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

,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,#,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,#,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_NUMBER,DATE,TO_CHAR,TO_DATE,TO_CHAR函数对,日,日期的,转,转换,TO_CHAR(date,format,_,_model),格式,:,必须包,含,含在单,引,引号中,而,而且大,小,小写敏,感,感。,可以包,含,含任意,的,的有效,的,的日期,格,格式。,可以使,用,用fm去掉多,余,余的空,格,格或者,前,前导零,。,。,与日期,指,指用逗,号,号隔开,。,。,YYYY,日期格,式,式的元,素,素,YEAR,MM,MONTH,DY,DAY,2004,TWOTHOUSANDAND FOUR,02,MON,MONDAY,JULY,MON,JUL,DD,02,练习,如何用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),9,0,$,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),CASE WHENexp1,=,=exp2THENNULL ELSEexp1END,比较两,个,个表达,式,式，如,果,果它们,相,相等则,返,返回空,值,值，否,则,则返回,表,表达式,一,一,注意：,表,表达式,一,一不能,为,为空,COALESCE函数,COALESCE(exp1,exp2,expN),COALESCE返回表,达,达式列,表,表中第,一,一个不,为,为空的,表,表达式,COALESCE,(,(expr1,expr2),CASE WHENexpr1ISNOT NULLTHENexpr1ELSE expr2 END,COALESCE,(,(expr1,expr2,.,.,exprn),for n,=3,CASE WHENexpr1ISNOT NULLTHENexpr1ELSECOALESCE,(,(expr2,.,.,.,exprn,),)END,CASE表达式,在需要,使,使用IF-THEN-ELSE逻辑时:,CASEexprWHENcomparison_expr1THENreturn,_,_expr1,WHENcomparison_expr2THENreturn,_,_expr2,WHENcomparison_exprnTHENreturn,_,_exprn,ELSEelse_expr,END,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_alias,USING,(,(table|view|sub_query)alias,ON,(,(join condition),WHEN MATCHEDTHEN,UPDATESET,col1=col_val1,col2=col2_val,WHEN NOTMATCHED THEN,INSERT,(,(column,_,_list),VALUES,(,(column,_,_values);,MERGEINTO copy,_,_empc,USINGemployeese,ON,(,(c.employee_id,=,=e,.,.employee,_,_id,),),WHEN MATCHEDTHEN,UPDATESET,c.first_name,=,=e.first_name,c.last,_,_name,=,=e.last_name,.,c.department,_,_id,=,=e.department_id,WHEN NOTMATCHED THEN,INSERTVALUES(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 c,USINGemployeese,ON,(,(c.employee_id,=,=e,.,.employee,_,_id,),),WHEN MATCHEDTHEN,UPDATESET,.,WHEN NOTMATCHED THEN,INSERTVALUES.,.,.;,SELECT,*,*,FROM COPY,_,_EMP;,norows selected,SELECT,*,*,FROM COPY,_,_EMP;,20rows selected.,