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

Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,*,*,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,*,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,Oracle,特色函数及复杂更新应用,目标,今天主要介绍一下日常操作中可能会用上,Oracle,的特色函数,:,转换函数：,to_char,通用函数：,nvl,、,nullif,、,nvl2,、,coalesce,、,decode,日期函数：,months_between,、,add_months,、,next_day,、,last_day,通过,Round,、,Trunc,函数,操作日期,复杂的,Update Table,应用,显式数据类型转换,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,TWO THOUSAND AND 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,.,expr,n,),NVL,函数,将空值转换成一个已知的值：,可以使用的数据类型有日期、字符、数字。,函数的一般形式,:,NVL(commission_pct,0),NVL(hire_date,01-JAN-97),NVL(job_id,No Job Yet),NVL2,函数,判断值是,否,否为空，,如,如果值不,为,为空则返,回,回第一个,表,表达式，,否,否则返回,第,第二个表,达,达式：,可以使用,的,的数据类,型,型有日期,、,、字符、,数,数字。,函数的一,般,般形式,:,NVL2(value,exp1,exp2),NULLIF 函,数,数,NULLIF(exp1,exp2),CASEWHEN exp1=exp2 THEN NULLELSEexp1 END,比较两个,表,表达式，,如,如果它们,相,相等则返,回,回空值，,否,否则返回,表,表达式一,注意：表,达,达式一不,能,能为空,COALESCE,函数,COALESCE(exp1,exp2,expN),COALESCE,返回表达,式,式列表中,第,第一个不,为,为空的表,达,达式,COALESCE(expr1,expr2),CASEWHEN expr1IS NOT NULLTHENexpr1 ELSEexpr2 END,COALESCE(expr1,expr2,.,exprn),forn=3,CASEWHEN expr1IS NOT NULLTHENexpr1ELSECOALESCE(expr2,.,exprn)END,CASE,表达式,在需要使,用,用,IF-THEN-ELSE,逻辑时,:,CASE,expr,WHEN,comparison_expr1,THEN,return_expr1,WHEN,comparison_expr2,THEN,return_expr2,WHEN,comparison_exprn,THEN,return_exprn,ELSE,else_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,语,句,句,，,，,根,根,据,据,指,指,定,定,的,的,条,条,件,件,进,进,行,行,插,插,入,入,或,更,更,新,新,操,操,作,作,MERGEINTO,table_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.,演讲完,毕,毕，谢,谢,谢观看,！,！,