Oracle特色函數(shù)與復雜更新應(yīng)用

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

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

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

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

5、N NULL ELSE exp1 ENDv比較兩個表達式，如果它們相等則返回空值，否則返回表達式一v注意：表達式一不能為空COALESCE 函數(shù)函數(shù)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)日期函數(shù)日期函數(shù)兩日期相差多少月兩日期相差多少月MONTHS_BETWEENADD_MONTHSNEXT_DAYLAST_DAYROUNDTRUNC 加月份以日期加月份以日期指定日期的下一天指定日期的下一天一個月中的最后一天一個月中的最后一天Round 日期日期Truncate 日期日期函數(shù)函數(shù)描述描述練習練習- -日期運算日期運算v得到2010-5-10至2010-9-13經(jīng)過了多少天v得到2010-5-10至2010-9-13經(jīng)過了多少個月v得到某個日期兩個月后的日期v得到某個日期這天后100天的日期v得到某個日期兩個月后的那個月的最后一

8、天高級更新操作高級更新操作v提出疑問 如何實現(xiàn)以下SQL Server中的Update操作 舉例高級更新操作高級更新操作v提出疑問 有以下表結(jié)構(gòu)，如何通過一個語句達到以下功能： 把A表的數(shù)據(jù)同步到B表，以ID字段為主鍵，如果B表中沒有A表的記錄，請插入合并語句合并語句v按照指定的條件執(zhí)行插入或刪除操作v如果滿足條件的行存在，執(zhí)行更新操作；否則執(zhí)行插入操作: 避免多次重復執(zhí)行插入和刪除操作 提高效率而且使用方便 在數(shù)據(jù)倉庫應(yīng)用中經(jīng)常使用合并語句的語法合并語句的語法可以使用merge語句，根據(jù)指定的條件進行插入或更新操作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語句，根據(jù)指定的條件從表 EMPLOYEES中插入或更新數(shù)據(jù)。合并語句舉例合并語句舉例MERGE INTO copy_emp c USING employees e ON (c.employee_id