1、Oracle Oracle 特色函數及復雜特色函數及復雜更新應用更新應用目標目標今天主要介紹一下日常操作中可能會用上Oracle的特色函數:轉換函數：to_char通用函數： nvl、nullif、nvl2、coalesce、decode日期函數：months_between、add_months、next_day、last_day通過Round、Trunc函數 操作日期復雜的Update Table應用顯式數據類型轉換顯式數據類型轉換NUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATETO_CHAR 函數對日期的轉換函數對日期的轉換TO_CHAR

2、(date, format_model)格式:v必須包含在單引號中而且大小寫敏感。v可以包含任意的有效的日期格式。v可以使用 fm 去掉多余的空格或者前導零。v與日期指用逗號隔開。YYYY日期格式的元素日期格式的元素YEARMMMONTHDYDAY2004TWO THOUSAND AND FOUR02MONMONDAYJULYMONJULDD02練習練習v 如何用SQL語句返回以下格式（假設今天是2010-9-13） 2010年9月13日 13/09/10 2010/9/13 2010年的第37周 2010年的第256天 2010年的第37周星期一TO_CHAR 函數對數字的轉換函數對數字的轉

3、換下面是在TO_CHAR 函數中經常使用的幾種格式:TO_CHAR(number, format_model)90$L.,數字數字零零美元符美元符本地貨幣符號本地貨幣符號小數點小數點千位符千位符練習練習v怎樣把一個隨意的數字轉換成以下格式（小于一千萬）： 235678.9851 - 235,678.99 235678.9851 - ￥235,678.99 8 - 通用函數通用函數這些函數適用于任何數據類型，同時也適用于空值：v NVL (expr1, expr2)v NVL2 (expr1, expr2, expr3)v NULLIF (expr1, expr2)v COALESCE (exp

4、r1, expr2, ., exprn)NVL 函數函數將空值轉換成一個已知的值：v可以使用的數據類型有日期、字符、數字。v函數的一般形式: NVL(commission_pct,0) NVL(hire_date,01-JAN-97) NVL(job_id,No Job Yet)NVL2 函數函數判斷值是否為空，如果值不為空則返回第一個表達式，否則返回第二個表達式：v可以使用的數據類型有日期、字符、數字。v函數的一般形式: NVL2(value, exp1, exp2)NULLIF NULLIF 函數函數vNULLIF(exp1, exp2)vCASE WHEN exp1 = exp2 THE

5、N NULL ELSE exp1 ENDv比較兩個表達式，如果它們相等則返回空值，否則返回表達式一v注意：表達式一不能為空COALESCE 函數函數vCOALESCE(exp1, exp2, , expN)vCOALESCE返回表達式列表中第一個不為空的表達式vCOALESCE (expr1, expr2)vCASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 ENDvCOALESCE (expr1, expr2, ., exprn), for n=3vCASE WHEN expr1 IS NOT NULL THEN expr1 ELSE COAL

6、ESCE (expr2, ., exprn) ENDCASE 表達式表達式在需要使用 IF-THEN-ELSE 邏輯時:CASE expr WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_exprENDDECODE(expr, comparison_expr1, return_expr1, comparison_expr2, return_expr2, comparison_exprn

7、, return_exprn, else_expr)日期函數日期函數兩日期相差多少月兩日期相差多少月MONTHS_BETWEENADD_MONTHSNEXT_DAYLAST_DAYROUNDTRUNC 加月份以日期加月份以日期指定日期的下一天指定日期的下一天一個月中的最后一天一個月中的最后一天Round 日期日期Truncate 日期日期函數函數描述描述練習練習- -日期運算日期運算v得到2010-5-10至2010-9-13經過了多少天v得到2010-5-10至2010-9-13經過了多少個月v得到某個日期兩個月后的日期v得到某個日期這天后100天的日期v得到某個日期兩個月后的那個月的最后一

8、天高級更新操作高級更新操作v提出疑問 如何實現以下SQL Server中的Update操作 舉例高級更新操作高級更新操作v提出疑問 有以下表結構，如何通過一個語句達到以下功能： 把A表的數據同步到B表，以ID字段為主鍵，如果B表中沒有A表的記錄，請插入合并語句合并語句v按照指定的條件執行插入或刪除操作v如果滿足條件的行存在，執行更新操作；否則執行插入操作: 避免多次重復執行插入和刪除操作 提高效率而且使用方便 在數據倉庫應用中經常使用合并語句的語法合并語句的語法可以使用merge語句，根據指定的條件進行插入或更新操作MERGE INTO table_name table_alias USING

9、 (table|view|sub_query) alias ON (join condition) WHEN MATCHED THEN UPDATE SET col1 = col_val1, col2 = col2_val WHEN NOT MATCHED THEN INSERT (column_list) VALUES (column_values);MERGE INTO copy_emp c USING employees e ON (c.employee_id = e.employee_id)WHEN MATCHED THEN UPDATE SET c.first_name = e.fi

10、rst_name, c.last_name = e.last_name, . c.department_id = e.department_idWHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name, e.last_name, e.email, e.phone_number, e.hire_date, e.job_id, e.salary, mission_pct, e.manager_id, e.department_id);合并語句舉例合并語句舉例在對表COPY_EMP使用merge語句，根據指定的條件從表 EMPLOYEES中插入或更新數據。合并語句舉例合并語句舉例MERGE INTO copy_emp c USING employees e ON (c.employee_id