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

Oracle特色函数及复杂更新应用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')格式:必须包含在单引号中而且大小写敏感。可以包含任意的有效的日期格式。可以使用

fm

去掉多余的空格或者前导零。与日期指用逗号隔开。YYYY日期格式的元素YEARMMMONTHDYDAY2004TWOTHOUSANDANDFOUR02MONMONDAYJULYMONJULDD02练习

如何用SQL语句返回以下格式（假设今天是2010-9-13）2010年9月13日13/09/102010/9/132010年的第37周2010年的第256天2010年的第37周星期一TO_CHAR

函数对数字的转换下面是在TO_CHAR函数中经常使用的几种格式:TO_CHAR(number,'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

函数将空值转换成一个已知的值：可以使用的数据类型有日期、字符、数字。函数的一般形式:NVL(commission_pct,0)NVL(hire_date,'01-JAN-97')NVL(job_id,'NoJobYet')NVL2

函数判断值是否为空，如果值不为空则返回第一个表达式，否则返回第二个表达式：可以使用的数据类型有日期、字符、数字。函数的一般形式:NVL2(value,exp1,exp2)NULLIF函数NULLIF(exp1,exp2)CASEWHENexp1=exp2THENNULLELSEexp1END比较两个表达式，如果它们相等则返回空值，否则返回表达式一注意：表达式一不能为空COALESCE

函数COALESCE(exp1,exp2,…,expN)COALESCE返回表达式列表中第一个不为空的表达式COALESCE(expr1,expr2)CASEWHENexpr1ISNOTNULLTHENexpr1ELSEexpr2ENDCOALESCE(expr1,expr2,...,exprn),forn>=3CASEWHENexpr1ISNOTNULLTHENexpr1ELSECOALESCE(expr2,...,exprn)ENDCASE

表达式在需要使用

IF-THEN-ELSE逻辑时:CASEexprWHENcomparison_expr1THENreturn_expr1

[WHENcomparison_expr2THENreturn_expr2WHENcomparison_exprnTHENreturn_exprnELSEelse_expr]ENDDECODE(expr,comparison_expr1,return_expr1,comparison_expr2,return_expr2,comparison_exprn,return_exprn,else_expr)日期函数两日期相差多少月MONTHS_BETWEENADD_MONTHSNEXT_DAY LAST_DAYROUND TRUNC 加月份以日期指定日期的下一天一个月中的最后一天Round日期Truncate日期函数描述练习-日期运算得到2010-5-10至2010-9-13经过了多少天得到2010-5-10至2010-9-13经过了多少个月得到某个日期两个月后的日期得到某个日期这天后100天的日期得到某个日期两个月后的那个月的最后一天高级更新操作提出疑问如何实现以下SQLServer中的Update操作举例高级更新操作提出疑问有以下表结构，如何通过一个语句达到以下功能：把A表的数据同步到B表，以ID字段为主键，如果B表中没有A表的记录，请插入合并语句按照指定的条件执行插入或删除操作如果满足条件的行存在，执行更新操作；否则执行插入操作:避免多次重复执行插入和删除操作提高效率而且使用方便在数据仓库应用中经常使用合并语句的语法可以使用merge语句，根据指定的条件进行插入或更新操作MERGEINTOtable_name

table_aliasUSING(table|view|sub_query)aliasON(joincondition)WHENMATCHEDTHENUPDATESET

col1=col_val1,col2=col2_valWHENNOTMATCHEDTHENINSERT(column_list)VALUES(column_values);MERGEINTOcopy_empcUSINGemployeeseON(c.employee_id=e.employee_id)WHENMATCHEDTHENUPDATESETc.first_name=e.first_name,c.last_name=e.last_name,...c.department_id=e.department_idWHENNOTMATCHEDTHENINSERTVALUES(e.employee_id,e.first_name,e.last_name,e.email,e.phone_number,e.hire_date,e.job_id,e.salary,emission_pct,e.manager_id,e.department_id);合并语句举例在对表COPY_EMP使用merge语句，根据指定的条件从表

EMPLOYEES中插入或更新数据。合并语句举例MERGEINTOcopy_empcUSINGemployeeseON(c.employee_id=e