单行函数3解读课件.ppt

1、转换函数转换函数隐式数据类型转换隐式数据类型转换Oracle 自动完成下列转换：VARCHAR2VARCHAR2 or CHAR or CHAR源数据类型源数据类型目标数据类型目标数据类型VARCHAR2 or CHARVARCHAR2 or CHARNUMBERNUMBERDATEDATENUMBERNUMBERDATEDATEVARCHAR2VARCHAR2VARCHAR2VARCHAR2varchar2numberdate显式数据类型转换显式数据类型转换NUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATETO_CHAR函数对函数对日期日期的转

2、换的转换格式:必须包含在单引号中而且大小写敏感必须包含在单引号中而且大小写敏感。可以包含任意的有效的日期格式有效的日期格式。日期之间用逗号隔开日期之间用逗号隔开。TO_CHAR(date,format_model)SELECT TO_CHAR(sysdate,yyyy-mm-dd hh:mi:ss)FROM dual;YYYY日期格式的元素日期格式的元素YEARMMMONTHDYDAY2004TWO THOUSAND AND FOUR02MONMONDAYJULYMONJULDD02日期格式的元素日期格式的元素 时间格式 使用双引号向日期中添加字符使用双引号向日期中添加字符HH24:MI:SS

3、 AMHH24:MI:SS AM15:45:32 15:45:32 PMPMDD of MONTHDD of MONTH12 12 of OCTOBERof OCTOBERTO_CHAR 函数对函数对日期日期的转换的转换SELECT last_name,TO_CHAR(hire_date,DD Month YYYY)AS HIREDATEFROM employees;select employee_id,last_name,hire_datefrom employeeswhere to_char(hire_date,yyyy-mm-dd)=1987-09-17TO_DATE 函数对函数对字符字

4、符的转换的转换 使用 TO_DATE 函数将字符转换成数字:TO_DATE(char,format_model)使用 TO_DATE:TO_DATE(2012年年10月月29日日 08:10:21,yyyy“年年”mm”月月”dd“日日”hh:mi:ss)From dual练习：返回hire_date 为*/*/*的员工信息，使用显示日期表达TO_CHAR函数对函数对数字数字的转换的转换下面是在TO_CHAR 函数中经常使用的几种格式:TO_CHAR(number,format_model)9 90 0$L L.,数字数字零零美元符美元符本地货币符号本地货币符号小数点小数点千位符千位符SELE

5、CT TO_CHAR(salary,$99,999.00)SALARYFROM employeesWHERE last_name=Ernst;TO_CHAR函数对函数对数字数字的转换的转换TO_NUMBER 函数对函数对字符字符的转换的转换 使用 TO_NUMBER 函数将字符转换成日期:TO_NUMBER(char,format_model)使用 TO_NUMBER:TO_NUMBER(￥1,234,567,890.00,L999,999,999,999.99)from dual通用函数通用函数这些函数适用于任何数据类型，同时也适用于适用于任何数据类型，同时也适用于空值空值：NVL(expr

6、1,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)练习1：求公司员工的年薪（含commission_pct)练习2：输出last_name,department_id，当department_id为null时，显示没有部门。SELECT

7、last_name,salary,NVL(commission_pct,0),(salary*12)+(salary*12*NVL(commission_pct,0)AN_SALFROM employees;使用使用NVL函数函数1212SELECT last_name,salary,commission_pct,NVL2(commission_pct,SAL+COMM,SAL)incomeFROM employees WHERE department_id IN(50,80);使用使用 NVL2 函数函数1212NVL2(expr1,expr2,expr3):expr1不为不为NULL，返回

8、，返回expr2；为；为NULL，返回，返回expr3。exp1!=null?exp2:exp3练习：查询员工的奖金率，若为空，返回0.01,若不为空，返回实际奖金率+0.015SELECT first_name,LENGTH(first_name)expr1,last_name,LENGTH(last_name)expr2,NULLIF(LENGTH(first_name),LENGTH(last_name)resultFROM employees;使用使用 NULLIF 函数函数123123NULLIF(expr1,expr2):相等返回NULL，不等返回expr1 使用使用 COALES

9、CE 函数函数 COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE。SELECT last_name,COALESCE(commission_pct,salary,10)commFROM employeesORDER BY commission_pct;使用使用 COALESCE 函数函数条件表达式条件表达式 在 SQL 语句中使用IF-THEN-ELSE 逻辑 使用两种方法:CASE 表达式 DECODE 函数CASE 表达式表达式在需要使用 IF-THEN-ELSE 逻辑时:C

10、ASE expr WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_exprEND练习：查询部门号为 10,20,30 的员工信息,若部门号为 10,则打印其工资的 1.1 倍,20 号部门,则打印其工资的 1.2 倍,30 号部门打印其工资的 1.3 倍数SELECT last_name,job_id,salary,CASE job_id WHEN IT_PROG THEN 1.10*

11、salary WHEN ST_CLERK THEN 1.15*salary WHEN SA_REP THEN 1.20*salary ELSE salary END REVISED_SALARYFROM employees;CASE 表达式表达式下面是使用case表达式的一个例子：DECODE 函数函数在需要使用 IF-THEN-ELSE 逻辑时:DECODE(col|expression,search1,result1,search2,result2,.,default)DECODE 函数函数SELECT last_name,job_id,salary,DECODE(job_id,IT_PR

12、OG,1.10*salary,ST_CLERK,1.15*salary,SA_REP,1.20*salary,salary)REVISED_SALARYFROM employees;DECODE 函数函数SELECT last_name,salary,DECODE(TRUNC(salary/2000,0),0,0.00,1,0.09,2,0.20,3,0.30,4,0.40,5,0.42,6,0.44,0.45)TAX_RATEFROM employeesWHERE department_id=80;使用decode函数的一个例子：嵌套函数嵌套函数 单行函数可以嵌套。嵌套函数的执行顺序是由内到

13、外。F3F3(F2(F2(F1(col,arg1)(F1(col,arg1),arg2),arg2),arg3)arg3)步骤步骤1=1=结果结果1 1步骤步骤2=2=结果结果2 2步骤步骤3=3=结果结果3 3SELECT last_name,NVL(TO_CHAR(manager_id),No Manager)FROM employeesWHERE manager_id IS NULL;嵌套函数嵌套函数总总 结结通过本章学习，您应该学会:使用函数对数据进行计算使用函数修改数据使用函数控制一组数据的输出格式使用函数改变日期的显示格式使用函数改变数据类型使用 NVL 函数使用IF-THEN-ELSE 逻辑