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/102010/9/132010年的第37周2010年的第256天2010年的第37周星期一TO_CHAR函数对数字的转换下面是在TO_CHAR函数中经常使用的几种格式:TO_CHAR(number,'format_model')90$L.,数字零美元符本地货币符号小数点千位符练习怎样把一个随意的数字转换成以下格式（小于一千万）：235678.9851-235,678.99235678.9851-￥235,678.998-Ⅷ通用函数这些函数适用于任何数据类型，同时也适用于空值：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)CASEWHENexpr1ISNOTNULLTHENexpr1ELSEexpr2ENDCOALESCE(expr1,expr2,...,exprn),forn=3CASEWHENexpr1ISNOTNULLTHENexpr1ELSECOALESCE(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_DAYLAST_DAYROUNDTRUNC加月份以日期指定日期的下一天一个月中的最后一天Round日期Truncate日期函数描述练习-日期运算得到2010-5-10至2010-9-13经过了多少天得到2010-5-10至2010-9-13经过了多少个月得到某个日期两个月后的日期得到某个日期这天后100天的日期得到某个日期两个月后的那个月的最后一天高级更新操作提出疑问如何实现以下SQLServer中的Update操作举例高级更新操作提出疑问有以下表结构，如何通过一个语句达到以下功能：把A表的数据同步到B表，以ID字段为主键，如果B表中没有A表的记录，请插入合并语句按照指定的条件执行插入或删除操作如果满足条件的行存在，执行更新操作；否则执行插入操作:避免多次重复执行插入和删除操作提高效率而且使用方便在数据仓库应用中经常使用合并语句的语法可以使用merge语句，根据指定的条件进行插入或更新操作MERGEINTOtable_nametable_aliasUSING(table|view|sub_query)aliasON(joincondition)WHENMATCHEDTHENUPDATESETcol1=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,e.commission_pct,e.manager_id,e.department_id);合并语句举例在对表COPY_EMP使用merge语句，根据指定的条件从表EMPLOYEES中插入或更新数据。合并语句举例MERGEINTOcopy_empcUSINGemployeeseON(c.employee_id=e.employee_id)WHENMATCHEDTHENUPDATESET...WHENNOTMATCHEDTHENINSERTVALUES...;SELECT*FROMCOPY_EMP;norowsselectedSELECT*FROMCOPY_EMP;20rowsselected.