Oracle特色函数与复杂更新应用.ppt

Oracle特色函数及复杂更新应用,目标,今天主要介绍一下日常操作中可能会用上Oracle的特色函数:转换函数：to_char通用函数：nvl、nullif、nvl2、coalesce、decode日期函数：months_between、add_months、next_day、last_day通过Round、Trunc函数操作日期复杂的UpdateTable应用,显式数据类型转换,NUMBER,CHARACTER,TO_CHAR,TO_CHAR函数对日期的转换,TO_CHAR(date,format_model)格式:必须包含在单引号中而且大小写敏感。可以包含任意的有效的日期格式。可以使用fm去掉多余的空格或者前导零。与日期指用逗号隔开。,YYYY,日期格式的元素,YEAR,MM,MONTH,DY,DAY,2004,TWOTHOUSANDANDFOUR,02,MON,MONDAY,JULY,MON,JUL,DD,02,练习,如何用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),9,0,$,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)END,CASE表达式,在需要使用IF-THEN-ELSE逻辑时:,CASEexprWHENcomparison_expr1THENreturn_expr1WHENcomparison_expr2THENreturn_expr2WHENcomparison_exprnTHENreturn_exprnELSEelse_exprEND,DECODE(expr,comparison_expr1,return_expr1,comparison_expr2,return_expr2,comparison_exprn,return_exprn,else_expr),日期函数,两日期相差多少月,MONTHS_BETWEEN,ADD_MONTHS,NEXT_DAY,LAST_DAY,ROUND,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_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,mission_pct,e.manager_id,e.department_id);,合并语句举例,在对表COPY_EMP使用merge语句，根据指定的条件从表EMPLOYEES中插入或更新数据。,合并语句举例,MERGEINTOcopy_empcUSINGemployeeseON(c.employee_id=e