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

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, ..., exprn) 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) CASE WHEN exp1 = exp2 THEN NULL ELSE exp1 END 比较两个表达式，如果它们相等则返回空值，否则返回表达式一 注意：表达式一不能为空 COALESCE 函数 COALESCE(exp1, exp2, …, expN) COALESCE返回表达式列表中第一个不为空的表达式 COALESCE (expr1, expr2) CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END COALESCE (expr1, expr2, ..., exprn), for n=3 CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE COALESCE (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_