ORACLE 公 司 的 專 業 認 證 是 當 今 市 場 上 公 認 價 值 最 高, 最 具 鑑 別 力 的 認 證 目 前 全 球 有 超 過 50 萬 以 上 人 已 透 過 Oracle Certification Program 取 得 專 業 認 證, 成 為 職 場 新 鮮 人 的 首 選 認 證, 它 具 有 以 下 三 大 優 勢 : 一 更 多 的 工 作 機 會, 二 更 高 的 薪 資 成 長 ORACLE OCP 榮 登 最 高 年 薪 認 證 榜 首, 三 更 高 的 身 價 保 證 ORACLE OCP 認 證 專 家 炙 手 可 熱 一 定 要 獲 得 ORACLE 認 證 有 3 個 理 由 : 1. 透 過 培 養 全 球 熱 門 的 技 術 才 能, 取 得 競 爭 優 勢 2. 透 過 不 斷 學 習, 創 造 機 會 並 提 升 您 的 可 信 度 3. 80% 的 ORACLE 證 照 持 有 人 表 示, 他 們 因 為 取 得 認 證 而 得 到 升 職 加 薪 或 其 他 有 助 於 職 涯 發 展 的 機 會, 請 加 入 他 們 的 行 列 在 ORACLE 認 證 項 目 中,OCE(Oracle Database Certified SQL Expert) 是 資 料 庫 上 的 專 業 認 證, 是 對 系 統 導 入 人 員 的 知 識 技 術 以 及 操 作 技 能 之 最 高 級 別 認 證, 也 是 成 為 顧 問 的 通 行 證 要 取 得 OCE 證 照, 需 通 過 SQL Expert Exam, 該 項 考 試 所 需 具 備 之 知 識 技 能 內 容 包 括 : 關 連 式 及 物 件 式 資 料 庫 之 基 本 觀 念, 運 用 SQL 及 SQL*Plus 指 令 以 檢 索 新 增 更 改 刪 除 資 料 庫 資 料 及 製 作 報 表, 以 及 產 生 及 維 護 資 料 庫 物 件 與 熟 悉 資 料 庫 各 種 相 關 語 法 及 功 能 等 美 商 甲 骨 文 股 份 有 限 公 司 為 推 廣 資 訊 產 業 認 證, 台 灣 分 公 司 從 2005 年 起 推 出 Oracle Academy 計 畫, 並 對 國 內 立 案 之 各 大 專 院 校 提 出 互 助 優 惠 方 案, 協 助 校 方 提 供 學 生 就 業 準 備, 取 得 證 照 的 健 全 機 制, 俾 使 甲 骨 文 人 才 培 育 及 認 證 機 制 得 以 推 廣 以 供 應 市 場 所 需, 並 提 供 在 校 學 生 實 惠 的 管 道 取 得 有 價 值 的
證 照 本 人 與 辜 教 授 在 OAI 專 案 上 合 作 多 年, 對 於 他 多 年 來 致 力 於 推 動 學 生 考 取 ORACLE 資 料 庫 認 證, 嘉 惠 學 子 深 感 佩 服, 他 的 著 作 ORACLE 資 料 庫 學 習 實 務 內 容 精 湛, 並 融 入 OCE 考 試 所 需 之 知 識 與 技 能 重 點, 對 於 有 意 學 習 ORACLE 資 料 庫 並 參 加 ORACLE DATABAE OCE 認 證 考 試 之 初 學 者 是 一 大 福 音, 故 樂 為 之 序 美 商 甲 骨 文 股 份 有 限 公 司 台 灣 分 公 司 教 育 訓 練 部 資 深 經 理 陳 盈 珍 謹 識 2015.1.20
在 網 際 網 路 暨 資 訊 化 世 界 中, 資 料 庫 是 儲 存 與 處 理 宛 如 天 上 繁 星 般 眾 多 電 子 化 資 料 的 必 備 工 具, 而 資 料 庫 管 理 系 統 (Database Management System, 縮 寫 :DBMS) 是 一 套 用 來 建 立 資 料 庫 控 制 資 料 庫 分 類 資 料 存 取 資 料 以 及 管 理 資 料 庫 而 設 計 的 電 腦 軟 體 系 統 當 前 具 有 代 表 性 的 資 料 管 理 系 統 例 如 : ORACEL Microsoft SQL Server MySQL 等 等, 其 中 ORACLE 資 料 庫 在 眾 多 資 料 庫 品 牌 中 是 一 個 廣 受 企 業 及 機 關 單 位 愛 用 的 產 品, 其 效 能 與 操 控 性 堪 稱 是 資 料 庫 界 中 的 雙 B 根 據 坊 間 人 力 銀 行 的 調 查 指 出,ORACLE 人 才 月 薪 行 情 平 均 約 在 48K 上 下, 這 在 22K 起 薪 盛 行 的 年 代, 學 習 ORACLE 資 料 庫 技 術 成 為 自 己 的 核 心 能 力, 作 為 日 後 求 職 能 力 的 碁 石, 相 對 的 可 說 是 一 項 高 價 值 比 (C/V 值 ) 的 學 習 投 資 透 過 SQL 來 操 控 ORACLE 資 料 庫 系 統 是 學 習 ORACLE 資 料 庫 技 術 的 基 礎 緣 此, 本 書 針 對 大 專 校 院 資 訊 管 理 系, 暨 其 他 有 開 設 資 料 庫 管 理 系 統 實 務 性 課 程 的 校 院 系 大 二 或 大 三 學 生, 規 劃 編 撰 適 合 一 個 學 期 學 習 使 用 之 資 料 庫 管 理 系 統 實 務 性 學 習 教 材 全 書 以 做 中 學 精 神 貫 穿 各 章 節, 配 合 必 要 之 關 聯 式 資 料 庫 理 論, 採 用 ORACLE DATABSE 資 料 庫 管 理 系 統 為 實 作 對 象, 以 實 務 性 操 作 當 為 學 習 主 軸 書 內 每 個 章 節 均 提 供 大 量 實 務 性 例 題, 對 於 學 習 者 有 極 大 之 幫 助 此 外, 書 中 亦 融 入 ORACLE DATABASE 專 科 高 階 OCE 證 照 考 試 ( 考 試 科 目 1Z047) 之 內 容 重 點 做 為 學 習 標 的, 對 於 有 意 參 加 ORACLE DATABASE OCE 證 照 考 試 者 亦 極 有 助 益 本 書 係 筆 者 利 用 課 餘 閒 暇 編 撰, 雖 曾 戮 力 以 赴 追 求 完 善, 然 而 疏 漏 繆 誤 或 在 所 難 免, 歡 迎 讀 者 與 先 進 不 吝 指 正 辜 輝 趂 謹 誌 2015 年 1 月 於 板 橋
使 用 轉 換 函 數 及 SQL 條 件 表 達 式 Using the conversion functions and SQL condition expressions SQL SQL SQL 1. TO_CHAR( ) 2. TO_NUMBER( ) 3. TO_DATE( ) 4. NVL NVL2 NULLIF COALESCE
5-2 Chapter 5 使 用 轉 換 函 數 及 SQL 條 件 表 達 式 SELECT IF..THEN..ELSE CASE DECODE 學 習 目 標 1. 2. 3. TO_CHAR( ) TO_NUMBER( ) TO_DATE( ) 4. NVL( ) NVL2( ) NULLIF( ) COALESCE( ) 5. SELECT IF..THEN..ELSE CASE DECODE 5.1 5.3 5.4 5.1 資 料 類 型 的 隱 性 轉 換 SQL 的 資 料 類 型 轉 換 有 隱 性 轉 換 (Implicit Conversion) 和 顯 性 轉 換 (Explicit Conversion) 兩 種 方 式, 如 圖 5.1 隱 性 轉 換 指 不 必 使 用 指 定 的 轉 換 函 數, 在 執 行 SQL 語 句 時, 資 料 庫 管 理 系 統 會 自 動 將 運 算 資 料 轉 換 成 相 同 的 型 別 後 再 予 以 運 算 反 之, 顯 性 轉 換 指 須 人 為 下 達 轉 換 函 數 指 令 來 處 理 資 料 類 型 轉 換
5.1 資 料 類 型 的 隱 性 轉 換 5-3 圖 5.1 SQL 語 句 的 資 料 類 型 轉 換 類 別 ORACLE 資 料 庫 會 自 動 轉 換 的 資 料 類 型 如 表 5.1 在 表 5.1 中, 運 算 式 中 的 運 算 元 (Operands) 資 料 類 型 會 依 運 算 符 的 類 型 屬 性, 例 如 算 術 運 算 符 或 字 符 運 算 符, 來 轉 換 成 合 適 的 資 料 類 型 再 予 以 運 算 表 5.1 ORACLE 資 料 庫 可 自 動 轉 換 的 資 料 類 型 從 VARCHAR2 或 CHAR VARCHAR2 或 CHAR NUMBER DATE 轉 換 至 NUMBER DATE VARCHAR2 或 CHAR VARCHAR2 或 CHAR 例 如 : SELECT 5+'10' FROM DUAL; 執 行 結 果 為 數 值 15 說 明 : 因 運 算 式 的 運 算 符 為 加 (+), 因 此 資 料 庫 管 理 系 統 會 自 動 把 '10' 這 個 字 串 轉 換 成 數 值 10 後 再 和 另 一 個 運 算 元 5 相 加 而 SELECT 5 '10' FROM DUAL; 執 行 結 果 為 字 串 '510'
5-4 Chapter 5 使 用 轉 換 函 數 及 SQL 條 件 表 達 式 說 明 : 因 運 算 式 的 運 算 符 為 字 串 之 串 接 ( ), 因 此 資 料 庫 管 理 系 統 會 自 動 把 5 這 個 數 值 轉 換 成 字 串 '5' 後 再 和 另 一 個 運 算 元 '10' 串 接 請 注 意, 在 表 5.1 中 當 系 統 要 將 VARCHAR2 或 CHAR 自 動 轉 換 成 DATE 格 式 時, 日 期 字 串 必 須 為 'DD MON YY' 之 預 設 格 式, 否 則 便 不 會 執 行 隱 性 轉 換 其 中 DD 為 2 位 數 日 期 值,MON 為 月 分 之 3 個 英 文 字 母 縮 寫, 大 小 寫 均 可,YY 為 YY 格 式 之 西 元 年 分 後 兩 位 數 字 例 題 5.1.1 在 OE 帳 戶 ORDERS 資 料 表, 執 行 下 列 命 令 欲 查 詢 2001 年 6 月 30 日 以 後 下 訂 的 訂 單 號 碼 和 客 戶 號 碼, 請 問 錯 誤 何 在? 應 如 何 修 改? SELECT order_id, customer_id FROM orders WHERE order_date > 'June 30 2001'; 解 析 : 1. 在 WHERE 子 句 中, 系 統 無 法 對 預 設 格 式 DD-MON- YY 以 外 之 日 期 字 串 自 動 轉 換 因 此 上 述 命 令 執 行 時 會 出 現 如 下 錯 誤 訊 息 : 2. 此 題 由 於 日 期 字 串 非 預 設 格 式, 因 此 需 將 字 串 改 為 預 設 格 式 '30 JUN 01' 修 改 後 之 完 整 命 令 如 下 : SELECT order_id, customer_id FROM orders WHERE order_date > '30 Jun 01';
5.2 資 料 類 型 的 顯 性 轉 換 5-5 執 行 結 果 ( 部 分 ): 5.2 資 料 類 型 的 顯 性 轉 換 一 SQL 內 建 的 資 料 類 型 轉 換 函 數 SQL 內 建 的 資 料 類 型 轉 換 函 數 有 TO_NUMBER( ) TO_CHAR( ) 和 TO_DATE( ) 等 三 個 單 列 函 數, 其 互 動 關 係 如 圖 5.2 茲 簡 介 如 下 : 圖 5.2 SQL 資 料 類 型 轉 換 函 數 的 互 動 關 係 1. TO_NUMBER ( char, [fmt], [nlsparams] ) 功 能 : 將 一 個 數 字 轉 換 成 由 fmt 格 式 指 定 的 數 值 說 明 :nlsparams 參 數 用 來 指 定 日 期 顯 示 的 語 系, 未 指 定 則 用 連 結 期 (Session) 預 設 的 語 系 顯 示 2. TO_CHAR ( number date, [fmt], [nlsparams] ) 功 能 : 將 一 個 數 值 或 日 期 資 料, 依 指 定 格 式 fmt 轉 換 成 VARCHAR2 字 串 後 顯 示, 以 便 於 閱 讀
5-6 Chapter 5 使 用 轉 換 函 數 及 SQL 條 件 表 達 式 說 明 :fmt 參 數 為 轉 換 後 之 顯 示 格 式 模 式 字 符 nlsparams 參 數 用 來 指 定 日 期 顯 示 的 語 系, 未 指 定 則 用 連 結 期 (Session) 預 設 的 語 系 顯 示 3. TO_DATE (char, [fmt], [nlsparams]) 功 能 : 將 一 個 字 串 轉 換 成 fmt 格 式 指 定 之 日 期 說 明 : 如 果 未 指 定 fmt 則 預 設 格 式 為 DD-MON-YY nlsparams 參 數 作 用 同 上 二 數 值 與 數 字 互 為 轉 換 之 應 用 如 前 述, 在 SQL 中 使 用 TO_NUMBER( ) 函 數 可 將 數 字 轉 換 成 數 值 反 之, 最 常 的 情 況 是 我 們 會 用 TO_CHAR( ) 函 數 將 查 詢 的 欄 位 數 值 轉 換 成 指 定 格 式 之 數 字 顯 示, 以 利 於 閱 讀 輸 出 結 果 數 值 與 數 字 互 轉 對 應 命 令 如 下, 而 常 用 的 數 值 對 數 字 轉 換 格 式 元 素 如 表 5.2 所 示 數 字 轉 換 成 數 值 :TO_NUMBER(char,['fromate_model']) 數 值 轉 換 成 數 字 :TO_CHAR(number,['formate_model']) 表 5.2 數 值 對 數 字 常 用 轉 換 格 式 元 素 彙 整 表 元 素 結 果 9 一 個 9 代 表 顯 示 一 個 位 數 0 強 制 加 上 一 個 0 $ 加 上 一 個 浮 動 $ 號 L 使 用 當 地 錢 幣 符 號. 在 指 定 位 置 加 上 小 數 點, 在 指 定 位 置 加 上 撇 號
5.2 資 料 類 型 的 顯 性 轉 換 5-7 例 題 5.2.1 在 HR 帳 戶 的 employees 資 料 表 中, 請 用 由 右 而 左 三 位 數 一 撇, 並 在 薪 水 前 面 加 上 '$' 號 之 格 式, 查 詢 員 工 的 薪 水 解 析 : 1. 本 例 題 關 鍵 在 於 將 數 值 轉 換 成 指 定 格 式 之 數 字 2. 由 於 HR 帳 戶 範 本 資 料 中, 員 工 薪 水 最 高 為 5 位 數, 因 此 轉 換 字 符 應 為 '$99,999' 3. SQL 語 句 : SELECT employee_id, last_name, TO_CHAR(salary,'$99,999') FROM employees; 4. 執 行 結 果 ( 部 分 ): 例 題 5.2.2 在 OE 帳 戶 product_information 資 料 表 中, 假 設 list_price 值 分 別 為 1123.90 與 11235.90, 請 問 下 述 SQL 命 令 的 執 行 結 果 分 別 為 何? SELECT TO_CHAR(list_price,'$9,999') FROM product_information;
5-8 Chapter 5 使 用 轉 換 函 數 及 SQL 條 件 表 達 式 解 析 : 1. TO_CHAR(list_price,'$9,999') 可 將 數 值 依 指 定 格 式 轉 為 字 符 以 便 顯 示 及 閱 讀 2. 格 式 中 一 個 9 代 表 一 位 數, 如 格 式 位 數 如 無 小 數, 則 小 數 以 四 捨 五 入 進 位 因 此 第 一 筆 資 料 TO_CHAR(1123.90,'$9,999') 之 輸 出 為 : $1,234 3. 如 格 式 位 數 小 於 資 料 位 數 長 度, 則 原 有 之 位 數 會 以 # 顯 示 第 二 筆 資 料 TO_CAHR(list_price,'$9,999') 因 格 式 位 數 只 有 $9,999 表 以 四 位 整 數, 前 面 加 上 $ 及 三 位 數 一 撇, 小 數 四 捨 五 入 格 式 顯 示, 而 待 轉 之 數 值 為 11235.90, 整 數 有 5 位, 前 面 加 上 $ 符 號, 及 三 位 數 一 撇 之 撇 號, 總 共 有 7 個 位 數 (Digital), 長 度 超 出 格 式 模 式, 故 輸 出 結 果 為 7 個 #: ####### 三 日 期 與 字 串 互 為 轉 換 之 應 用 將 查 詢 得 到 的 日 期 欄 位 內 容 轉 換 為 字 串 主 要 是 為 了 增 加 輸 出 資 料 之 可 讀 性 反 之, 將 運 算 式 中 代 表 日 期 的 字 串 轉 換 成 日 期, 則 是 為 了 要 進 行 日 期 之 比 較 運 算 或 算 術 運 算 相 關 命 令 格 式 如 下 : 日 期 轉 換 為 字 串 :TO_CHAR(date,'format_model') 字 串 轉 換 為 日 期 :TO_DATE(char[,'format_model']) 上 述 命 令 中, 轉 換 格 式 模 式 format_model 的 格 式 元 素 如 表 5.3, 其 使 用 規 則 如 下 : 1. 格 式 字 符 必 須 用 單 引 號 括 起 來 2. 大 小 寫 有 別 例 如 :TO_CHAR(hire_date,'DD-MON-YY') 在 顯 示 時, 月 分 之 英 文 縮 寫 以 大 寫 呈 現, 而 TO_CHAR(hire_date,'DDmon-YY') 在 顯 示 時, 月 分 之 英 文 縮 寫 以 小 寫 呈 現
5.2 資 料 類 型 的 顯 性 轉 換 5-9 3. 可 包 含 表 5.3 中 任 何 有 效 的 日 期 元 素, 元 素 間 須 以 分 隔 號 隔 開, 例 如 : 空 格 逗 號 ',' 連 字 號 '-' 反 斜 號 '/' 等 4. 輸 出 的 日 期 月 分 等 會 自 動 以 空 格 填 補, 而 如 果 要 去 除 系 統 填 補 的 空 格, 可 在 格 式 字 符 前 面 加 上 fm 以 移 除 填 補 空 格 或 日 期 中 的 前 導 0 例 如 :TO_CHAR(hire_date,'fmDD-Mon-YYYY') 在 顯 示 日 期 01 至 09 時, 只 顯 示 1 至 9, 其 前 導 0 不 被 顯 示 表 5.3 日 期 格 式 字 符 彙 整 表 元 素 YYYY YEAR MM MONTH MON 結 果 以 4 位 阿 拉 伯 數 字 顯 示 年 份 以 英 文 顯 示 年 份 以 2 位 阿 拉 伯 數 字 顯 示 月 份 顯 示 月 份 英 文 名 稱 以 3 個 英 文 字 簡 寫 顯 示 月 份 DDD 或 DD 或 D 年 月 或 星 期 內 的 日 期 DY DAY 以 3 個 英 文 字 縮 寫 顯 示 該 日 期 為 星 期 幾 例 如 FRI SAT WED 等 以 英 文 顯 示 該 日 期 為 星 期 幾 例 如 SATURDAY FRIDAY 等 DD 以 2 位 阿 拉 伯 數 字 顯 示 日 期 SCC 或 CC 以 2 位 數 顯 示 世 紀 Q 顯 示 年 度 之 季 別 RM 以 羅 馬 數 字 顯 示 月 份, 例 如 8 以 VIII 表 示 WW 或 W 年 或 月 內 的 週 IW 以 ISO 標 準 顯 示 日 期 所 在 之 週 為 年 度 之 第 幾 週 (1-53) HH 或 HH12 或 HH24 HH: 顯 示 日 期 資 料 中 的 時 刻 ;HH12: 以 12 小 時 制 (1-12) 顯 示 時 刻 ;HH24 以 24 小 時 制 (0-23) 顯 示 時 刻
5-10 Chapter 5 使 用 轉 換 函 數 及 SQL 條 件 表 達 式 表 5.3 日 期 格 式 字 符 彙 整 表 ( 續 ) MI 顯 示 時 刻 中 的 分 鐘 SS 顯 示 時 刻 中 的 秒 SSSSS 顯 示 過 午 夜 後 之 秒 數 (0-86399) AM 或 A.M. 或 PM 或 P.M. 在 日 期 時 刻 中 顯 示 子 午 線 指 標 AM 或 PM 例 題 : SELECT TO_CHAR(SYSDATE,'DD-Mon-YY HH24:MI:SS A.M.') FROM dual; 執 行 結 果 :31-Jul-14 12:18:50 P.M. 註 : 粗 體 字 為 常 用 之 元 素 例 題 5.2.3 在 OE 帳 戶 orders 資 料 表 中, 請 完 成 下 述 WHERE 子 句, 以 查 詢 在 2006 年 7 月 10 日 以 前 下 訂 之 訂 單 編 號 和 客 戶 編 號 以 及 訂 單 日 期 SELECT order_id, customer_id, TO_CHAR( order_date, 'DD/MM/YYYY') FROM orders WHERE order_date< TO_DATE('JUL 10 2006', ); 解 析 : 1. 因 TO_DATE 函 數 要 轉 換 的 日 期 字 串 為 'JUL 10 2006', 所 以 配 合 之 轉 換 格 式 模 式 為 ' 月 日 年 ', 即 'MON DD YYYY' 2. 完 整 之 SQL 命 令 如 下 : SELECT order_id, customer_id,to_char(order_date,'dd/mm/yyyy') FROM orders WHERE order_date < TO_DATE('JUL 10 2006','MON DD YYYY');
5.3 空 值 轉 換 函 數 5-11 3. 執 行 結 果 : 5.3 空 值 轉 換 函 數 如 3.2 節 所 述, 如 果 資 料 表 欄 位 為 空 值 (NULL Value), 表 示 該 欄 位 的 值 可 能 為 下 列 三 種 情 況 之 一 :( 一 ) 目 前 不 知 它 的 值 (Unknown) 或, ( 二 ) 未 指 派 (Unassigned), 或 ( 三 ) 沒 有 值 (Unapplicable) 而 不 管 實 際 上 是 哪 種 狀 況, 在 SQL 運 算 式 中, 空 值 運 算 元 相 當 於 算 術 運 算 之 無 限 大, 因 此 必 須 加 以 轉 換 後 才 可 以 處 理 處 理 空 值 的 單 列 轉 換 函 數 有 NVL( ) NVL2( ) NULLIF COALESCE 等, 分 別 介 紹 如 下 一 NVL 函 數 SQL 語 句 格 式 : NVL( expr1, expr2) 作 用 : 將 expr1 空 值 轉 換 成 為 一 個 指 定 的 值 expr2 說 明 : 1. expr1 為 可 能 為 空 值 之 原 始 值 或 欄 位, 可 為 任 意 資 料 類 型 2. expr2 為 空 值 轉 換 後 之 目 標 值
5-12 Chapter 5 使 用 轉 換 函 數 及 SQL 條 件 表 達 式 例 題 5.3.1 NVL 使 用 示 範 1. NVL(commission_pct,0) 作 用 : 如 果 commission_pct 為 空 值, 則 傳 回 0 2. NVL(hire_date,'01-Jan-2014') 作 用 : 如 果 hire_date 為 空 值, 則 傳 回 '01-Jan-2014' 例 題 5.3.2 在 HR 帳 戶 employees 資 料 表 中, 查 詢 並 顯 示 員 工 的 姓 氐 薪 水 紅 利 百 分 比, 並 計 算 其 年 薪 解 析 : 1. 年 薪 = 月 薪 *(1+ 紅 利 百 分 比 )*12 2. 因 只 有 績 優 員 工 享 有 紅 利, 無 紅 利 員 工 之 紅 利 百 分 比 欄 位 (commission_pct) 為 空 值, 此 將 導 致 年 薪 公 式 無 法 計 算 因 此 需 用 NVL(commission_pct,0) 將 無 紅 利 員 工 的 紅 利 值 由 空 值 轉 為 0 3. SQL 語 句 : SELECT last_name, salary, NVL(commission_pct,0), salary*(1+nvl (commission_pct,0 ) ) *12 年 薪 FROM employees; 4. 執 行 結 果 ( 部 分 ):
5.3 空 值 轉 換 函 數 5-13 二 NVL2 函 數 SQL 語 句 格 式 : NVL2( expr1, expr2, expr3) 作 用 : 1. 如 果 expr1 空 值, 則 返 回 expr2 2. 如 果 expr1= 空 值, 則 返 回 expr3 說 明 : 1. expr1 為 可 能 為 空 值 之 原 始 值 或 欄 位, 可 為 任 意 資 料 類 型 2. expr2 是 expr1 不 是 空 值 之 轉 換 目 標 值 3. expr3 是 expr1 為 空 值 之 轉 換 目 標 值 例 題 5.3.3 在 HR 帳 戶 employees 資 料 表 中, 查 詢 部 門 50 與 部 門 80 員 工 薪 水 及 紅 利 百 分 比, 並 計 算 其 年 薪 ( 進 階 ) 解 析 : 1. 年 薪 = 月 薪 *(1+ 紅 利 百 分 比 )*12 2. 因 只 有 部 分 員 工 有 紅 利, 因 此 紅 利 百 分 比 可 用 NVL2(commission_pct, commission_pct,0) 求 得 3. 要 查 詢 部 門 50 及 部 門 80 之 條 件 式, 可 用 WHERE department_id IN (50,80) 達 成 4. SQL 語 句 : SELECT last_name, salary, commission_pct, salary*(1+nvl2( commission_pct, commission_pct,0))*12 " 年 收 入 " FROM employees WHERE department_id IN (50,80);
5-14 Chapter 5 使 用 轉 換 函 數 及 SQL 條 件 表 達 式 5. 執 行 結 果 : 三 NULLIF 函 數 SQL 語 句 格 式 : NULLIF( expr1, expr2) 作 用 : 1. 如 果 expr1 = expr2, 返 回 空 值 2. 如 果 expr1 expr2, 返 回 expr1 說 明 : expr1 expr2 為 欲 比 較 之 原 始 值 或 欄 位, 可 為 任 意 資 料 類 型 例 題 5.3.4 在 HR 帳 戶 employees 資 料 表 中, 查 詢 並 比 對 員 工 的 姓 氏 與 名 字 長 度, 相 同 者, 在 比 對 結 果 中 顯 示 NULL 不 等 者, 顯 示 名 字 字 數 ( 進 階 ) 解 析 : 1. 姓 氏 與 名 字 字 數 之 比 對 可 用 NULLIF( ) 函 數 處 理 2. SQL 語 句 : SELECT first_name, LENGTH( first_name) " 名 字 字 數 ", last_name, LENGTH( last_name) " 姓 氏 字 數 ", NULLIF (LENGTH(first_name), LENGTH(last_name)) 比 對 結 果 FROM employees;
5.3 空 值 轉 換 函 數 5-15 3. 執 行 結 果 : 四 COALESCE 函 數 SQL 語 句 格 式 : COALESCE (expr1, expr2, expr3,., expr n) 作 用 : 返 回 引 數 列 中 第 一 個 非 空 值 引 數 說 明 : expr1 至 expr n 為 欲 比 較 之 原 始 值 或 欄 位, 可 為 任 意 資 料 類 型 例 題 5.3.5 在 HR 帳 戶 employees 資 料 表 中, 查 詢 無 上 司 且 無 紅 利 的 員 工, 請 在 此 人 資 料 中 顯 示 這 個 人 是 老 闆 ( 進 階 ) 解 析 : 1. 無 上 司 者, 其 manager_id 為 空 值 2. 無 紅 利 者, 其 commission_pct 為 空 值 3. 使 用 coalesce 函 數 來 依 序 返 回 不 是 空 值 之 commission_pct 及 manager_id 如 二 者 均 為 空 值, 則 返 回 這 個 人 是 老 闆
5-16 Chapter 5 使 用 轉 換 函 數 及 SQL 條 件 表 達 式 4. SQL 語 句 : SELECT last_name,employee_id, COALESCE (TO_CHAR(commission_pct), TO_CHAR( manager_id), ' 這 個 人 是 老 闆 ') FROM employees; 5. 執 行 結 果 ( 部 分 ): 例 題 5.3.6 配 對 下 列 函 數 及 其 功 能 1) NVL a) 評 估 第 一 個 引 數 為 NOT NULL 或 NULL 值 2) COALESCE b) 比 較 兩 數 式, 如 果 相 同, 返 回 NULL; 否 則 僅 返 回 第 一 個 數 式 3) NULLIF c) 返 回 引 數 列 或 數 式 中 第 一 個 非 空 值 (Non-Null values) 引 數 4) NVL2 d) 轉 換 NULL 為 確 定 值 答 :1D, 2C, 3B, 4A 5.4 SELECT 語 句 的 若.. 則.. 否 則.. 流 程 控 制 SELECT 語 句 可 以 用 CASE 表 達 式 或 DECODE 函 數 來 達 成 和 第 三 代 程 式 語 言 相 似 的 若.. 則.. 否 則.. (IF..THEN..ELSE) 的 流 程 控 制 結 構 功 能, 其 SQL 語 法 如 下 :
5.4 SELECT 語 句 的 若.. 則.. 否 則.. 流 程 控 制 5-17 一 CASE..WHEN..END 表 述 式 SQL 語 句 格 式 : SELECT 欄 位 [,...], CASE 表 述 式 WHEN 比 較 條 件 1 THEN 返 回 值 1 [ WHEN 比 較 條 件 2 THEN 返 回 值 2 WHEN 比 較 條 件 N THEN 返 回 值 N ELSE 預 設 返 回 值 ] FROM 表 格 [WHERE.. 條 件 ] [ORDER BY 欄 位 ] END 作 用 : 在 SELECT 語 句 中, 完 成 若.. 則.. 否 則 之 條 件 動 作 說 明 : 1. CASE 前 須 加 逗 號 2. CASE 後 面 的 expr 可 為 欄 位 或 表 述 式 3. 各 個 比 較 條 件 直 接 寫 比 較 值, 不 可 再 寫 比 較 欄 位 名 稱 4. 各 個 返 回 值 可 為 欄 位 或 表 述 式 5. END 後 面 可 以 加 上 字 串 ( 用 雙 引 號 括 住 ) 當 為 CASE..WHEN.. THEN..ELSE 之 別 名 例 題 5.4.1 在 HR 帳 戶 的 employees 資 料 表 中, 近 年 來 由 於 公 司 獲 利 很 好, 決 定 依 下 列 條 件 加 薪 請 寫 一 SQL 語 句 查 詢 員 工 編 號 姓 氏 職 務 原 薪 水 及 調 整 後 之 薪 水 1. 業 務 代 表 SAL_REP 調 薪 20% 2. 程 式 設 計 師 IT_PROG 調 薪 15%
5-18 Chapter 5 使 用 轉 換 函 數 及 SQL 條 件 表 達 式 3. 職 員 ST_CLERK 調 薪 10% 4. 其 他 職 務 調 薪 5% 解 析 : 1. 在 employees 資 料 表 中, 職 務 存 於 JOB_ID 欄, 薪 水 存 於 salary 欄 2. 本 例 題 關 鍵 在 如 何 依 不 同 條 件 達 成 對 應 之 運 算 3. 使 用 CASE.. WHEN..ELSE 之 寫 法 如 下 : CASE job_id WHEN 'SAL_REP' THEN salary*1.2 WHEN 'IT_PROG' THEN salary*1.15 WHEN 'ST_CLERK' THEN salary*1.1 ELSE salary*1.05 END 4. SQL 語 句 : SELECT employee_id, last_name, job_id, salary " 原 薪 水 ", CASE job_id WHEN 'SAL_REP' THEN salary*1.2 WHEN 'IT_PROG' THEN salary*1.15 WHEN 'ST_CLERK' THEN salary*1.1 ELSE salary*1.05 END " 調 整 後 薪 水 " FROM employees; 說 明 : 1. Salary 欄 位 之 別 名 為 " 原 薪 水 " 2. CASE 語 句 的 預 設 返 回 值 為 salary*1.05 3. CASE 語 句 運 算 結 果 之 別 名 為 " 調 整 後 薪 水 "
5.4 SELECT 語 句 的 若.. 則.. 否 則.. 流 程 控 制 5-19 執 行 結 果 ( 部 分 ): 二 DECODE 函 數 SQL 語 句 格 式 : SELECT 欄 位 [, 欄 位,...], DECODE ( expr 比 較 條 件 1, 返 回 值 1 [, 比 較 條 件 2, 返 回 值 2, 比 較 條 件 N, 返 回 值 N] [, 預 設 返 回 值 ]) FROM 表 格 [WHERE.. 條 件 ] [ORDER BY 欄 位 ] 作 用 : 在 SELECT 語 句 中, 完 成 若.. 則.. 否 則 之 條 件 動 作 說 明 : 1. DECODE 函 數 內 expr 可 為 欄 位 或 表 述 式 2. 各 個 比 較 條 件 直 接 寫 比 較 值, 不 可 再 寫 比 較 欄 位 名 稱 3. 各 個 返 回 值 可 為 欄 位 或 表 述 式
5-20 Chapter 5 使 用 轉 換 函 數 及 SQL 條 件 表 達 式 4. DECODE 函 數 的 最 後 面 可 以 加 上 字 串 ( 用 雙 引 號 括 著 ) 當 為 此 及 函 數 執 行 結 果 之 別 名 例 題 5.4.2 用 DECODE 函 數 重 寫 問 題 5.4.1 解 析 : 1. 本 例 題 關 鍵 在 如 何 依 不 同 條 件 達 成 對 應 之 運 算 2. 使 用 DECODE 函 數 之 寫 法 如 下 : DECODE( job_id, 'SAL_REP', salary*1.2, 'IT_PROG', salary*1.15, 'ST_CLERK',salary*1.1, salary*1.05) SQL 語 句 : SELECT employee_id, last_name, job_id, salary " 原 薪 水 ", DECODE( job_id, 'SAL_REP', salary*1.2, 'IT_PROG', salary*1.15, 'ST_CLERK',salary*1.1, salary*1.05) " 調 整 後 薪 水 " FROM employees; 說 明 : 1. Salary 欄 位 之 別 名 為 " 原 薪 水 " 2. DECODE 函 數 運 算 結 果 之 別 名 為 " 調 整 後 薪 水 " 執 行 結 果 : 同 例 題 5.4.1
5.4 SELECT 語 句 的 若.. 則.. 否 則.. 流 程 控 制 5-21 總 結 1. 2. 3. 4. 5. 6. CASE DECODE( ) SELECT...... 5.1 5.3 5.4
5-22 Chapter 5 使 用 轉 換 函 數 及 SQL 條 件 表 達 式 請 選 擇 下 列 各 題 正 確 答 案 並 解 釋 原 因, 且 在 ORACLE 資 料 庫 上 實 作 : 1. 檢 視 OE 帳 戶 的 ORDERS 資 料 表 請 評 估 如 下 SQL 語 句 之 執 行, 下 列 敘 述 何 者 正 確? SELECT order_id, customer_id FROM orders WHERE order_date > 'June 30 2001'; A. 不 能 執 行, 因 WHERE 子 句 內 的 日 期 'June 30 2001' 未 使 用 雙 引 號 括 著 B. 可 以 執 行, 且 將 輸 出 ORDEER_DATE 大 於 'June 30 2001' 所 有 資 料 列 之 ORDER_ID 及 CUSTOMER_ID C. 不 能 執 行, 因 WHERE 子 句 內 的 日 期 'June 30 2001' 無 法 隱 性 轉 換, 須 用 TO_DATE 轉 換 函 數 才 能 執 行 D. 不 能 執 行, 因 WHERE 子 句 內 的 日 期 'June 30 2001' 無 法 隱 性 轉 換, 須 用 TO_CHAR 轉 換 函 數 才 能 執 行 2. 檢 視 OE 帳 戶 的 PRODUCT_INFORMATION 資 料 表 請 評 估 如 下 SQL 語 句 之 執 行, 下 列 敘 述 何 者 正 確? SELECT TO_CHAR(list_price,'$9,999') FROM product_information; A. LIST_PRICE 欄 位 值 為 1123.90, 結 果 顯 示 為 $1,124 B. LIST_PRICE 欄 位 值 為 1123.90, 結 果 顯 示 為 $1,123 C. LIST_PRICE 欄 位 值 為 11235.90, 結 果 顯 示 為 $1,123 D. LIST_PRICE 欄 位 值 為 11235.90, 結 果 顯 示 為 #######
學 習 評 量 5-23 3. 檢 視 HR 帳 戶 的 EMPLOYEES 資 料 表 請 問 下 列 哪 個 SQL 語 句 可 計 算 每 位 員 工 之 總 收 入? 員 工 總 收 入 等 於 年 薪 加 上 年 薪 乘 紅 利 百 分 比, 且 僅 有 少 數 人 獲 得 紅 利 A. SELECT first_name, salary, salary*12+salary*commission_pct "Total" FROM EMPLOYEES; B. SELECT first_name, salary, salary*12+nvl((salary*commission_pct), 0) "Total" FROM EMPLOYEES; C. SELECT first_name, salary, salary*12 + NVL(salary, 0)*commission_pct "Total" FROM EMPLOYEES; D. SELECT first_name, salary, salary*12+(salary*nvl2(commission_pct, salary, salary+commission_pct)) "Total" FROM EMPLOYEES; 4. 配 對 下 列 函 數 及 功 能 函 數 NVL 功 能 描 述 評 估 NULL 及 NOT NULL NULLIF 用 來 從 一 列 述 式 中 返 回 第 一 個 NOT NULL 的 值 COALESCE 用 來 比 較 兩 個 述 式, 相 等 返 回 NULL, 不 等 返 回 第 一 個 述 式 NVL2 用 來 轉 換 NULL 值 為 指 定 值 A. 1-A, 2-C, 3-B, 4-D B. 1-D, 2-C, 3-B, 4-A C. 1-B, 2-C, 3-D, 4-A D. 1-D, 2-B, 3-C, 4-A