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_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,mission_pct,e.manager_id,e.department_id);合并语句句举例在对表COPY_EMP使用merge语句，根根据指定定的条件从从表EMPLOYEES中插入或或更新数数据。合并语句句举例MERGEINTOcopy_empcUSINGemployeeseON(c.employee_id=e.employe