Oracle 特色函数及复杂更新应用

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