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

1、Oracle 特色函数及复杂更新应用目标今天主要要介绍一一下日常常操作中中可能会会用上Oracle的特色函函数:转换函数数：to_char通用函数数：nvl、nullif、nvl2、coalesce、decode日期函数数：months_between、add_months、next_day、last_day通过Round、Trunc函数操作日期期复杂的UpdateTable应用显式数据据类型转转换NUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATETO_CHAR函数对日日期的转转换TO_CHAR(date,format_model)格式:必须包含

2、含在单引引号中而而且大小小写敏感感。可以包含含任意的的有效的的日期格格式。可以使用用fm去掉多余余的空格格或者前前导零。与日期指指用逗号号隔开。YYYY日期格式式的元素素YEARMMMONTHDYDAY2004TWOTHOUSANDAND FOUR02MONMONDAYJULYMONJULDD02练习如何用SQL语句返回回以下格格式（假设今今天是2010-9-13）2010年9月13日13/09/102010/9/132010年的第37周2010年的第256天2010年的第37周星期一一TO_CHAR函数对数数字的转转换下面是在在TO_CHAR函数中经经常使用用的几种种格式:TO_CHAR(n

3、umber,format_model)90$L.,数字零美元符本地货币币符号小数点千位符练习怎样把一一个随意意的数字字转换成成以下格格式（小小于一千千万）：235678.9851-235,678.99235678.9851-￥235,678.998 - 通用函数数这些函数数适用于于任何数数据类型型，同时时也适用用于空值值：NVL(expr1,expr2)NVL2 (expr1,expr2, expr3)NULLIF(expr1,expr2)COALESCE(expr1, expr2,., exprn)NVL函数将空值转转换成一一个已知知的值：可以使用用的数据据类型有有日期、字符、数字。函数的一

4、一般形式式:NVL(commission_pct,0)NVL(hire_date,01-JAN-97)NVL(job_id,NoJobYet)NVL2函数判断值是是否为空空，如果果值不为为空则返返回第一一个表达达式，否否则返回回第二个个表达式式：可以使用用的数据据类型有有日期、字符、数字。函数的一一般形式式:NVL2(value,exp1,exp2)NULLIF函函数NULLIF(exp1,exp2)CASE WHENexp1= exp2THENNULL ELSEexp1END比较两个个表达式式，如果果它们相相等则返返回空值值，否则则返回表表达式一一注意：表表达式一一不能为为空COALESCE

5、函数COALESCE(exp1,exp2,expN)COALESCE返回表达达式列表表中第一一个不为为空的表表达式COALESCE(expr1, expr2)CASE WHENexpr1ISNOT NULLTHENexpr1ELSE expr2 ENDCOALESCE(expr1, expr2,., exprn),for n=3CASE WHENexpr1ISNOT NULLTHENexpr1ELSECOALESCE(expr2, .,exprn) ENDCASE表达式在需要使使用IF-THEN-ELSE逻辑时:CASEexprWHENcomparison_expr1THENreturn_ex

6、pr1WHENcomparison_expr2THENreturn_expr2WHENcomparison_exprnTHENreturn_exprnELSEelse_exprENDDECODE(expr,comparison_expr1,return_expr1,comparison_expr2,return_expr2,comparison_exprn,return_exprn,else_expr)日期函数数两日期相相差多少少月MONTHS_BETWEENADD_MONTHSNEXT_DAYLAST_DAYROUNDTRUNC加月份以以日期指定日期期的下一一天一个月中中的最后后一天Roun

7、d日期Truncate日期函数描述练习-日期运算算得到2010-5-10至2010-9-13经经过了多多少天得到2010-5-10至2010-9-13经经过了多多少个月月得到某个个日期两两个月后后的日期期得到某个个日期这这天后100天天的日期期得到某个个日期两两个月后后的那个个月的最最后一天天高级更新新操作提出疑问问如何实现现以下SQLServer中的Update操作举例高级更新新操作提出疑问问有以下表表结构，如何通通过一个个语句达达到以下下功能：把A表的数据据同步到到B表，以ID字段为主主键，如如果B表中没有有A表的记录录，请插插入合并语句句按照指定定的条件件执行插插入或删删除操作作如果满足

8、足条件的的行存在在，执行行更新操操作；否否则执行行插入操操作:避免多次次重复执执行插入入和删除除操作提高效率率而且使使用方便便在数据仓仓库应用用中经常常使用合并语句句的语法法可以使用用merge语句，根根据指定定的条件件进行插插入或更新操操作MERGEINTOtable_nametable_aliasUSING(table|view|sub_query)aliasON(join condition)WHEN MATCHEDTHENUPDATESETcol1 =col_val1,col2 =col2_valWHEN NOTMATCHED THENINSERT(column_list)VALUES

9、(column_values);MERGEINTO copy_empcUSINGemployeeseON(c.employee_id= e.employee_id)WHEN MATCHEDTHENUPDATESETc.first_name=e.first_name,c.last_name=e.last_name,.c.department_id=e.department_idWHEN NOTMATCHED THENINSERTVALUES(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中插入或或更新数数据。合并语句句举例MERGEINTO copy_emp cUSINGemployeeseON(c.employee_