单行函数3解读ppt课件

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,转换函数,隐 性,显 性,数据类型转换,转换函数 隐 性 显,隐式数据类型转换,Oracle,自动完成下列转换：,VARCHAR2,or CHAR,源数据类型,目标数据类型,VARCHAR2 or CHAR,NUMBER,DATE,NUMBER,DATE,VARCHAR2,VARCHAR2,varchar2,number,date,隐式数据类型转换Oracle 自动完成下列转换：VARCHA,显式数据类型转换,NUMBER,CHARACTER,TO_CHAR,TO_NUMBER,DATE,TO_CHAR,TO_DATE,显式数据类型转换NUMBERCHARACTERTO_CHAR,TO_CHAR,函数对,日期,的转换,格式,:,必须包含在单引号中而且大小写敏感,。,可以包含任意的,有效的日期格式,。,日期之间用逗号隔开,。,TO_CHAR(date,format_model),SELECT TO_CHAR(,sysdate,yyyy-mm-dd hh:mi:ss,)FROM dual;,TO_CHAR函数对日期的转换格式:TO_CHAR(date,YYYY,日期格式的元素,YEAR,MM,MONTH,DY,DAY,2004,TWO THOUSAND AND FOUR,02,MON,MONDAY,JULY,MON,JUL,DD,02,YYYY日期格式的元素YEARMMMONTHDYDAY200,日期格式的元素,时间格式,使用双引号向日期中添加字符,HH24:MI:SS AM,15:45:32,PM,DD of MONTH,12,of OCTOBER,日期格式的元素时间格式HH24:MI:SS AM15:45,TO_CHAR,函数对,日期,的转换,SELECT last_name,TO_CHAR(hire_date,DD Month YYYY),AS HIREDATE,FROM employees;,select employee_id,last_name,hire_date,from employees,where to_char(hire_date,yyyy-mm-dd)=1987-09-17,TO_CHAR 函数对日期的转换SELECT last_na,TO_DATE,函数对,字符,的转换,使用,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_DATE 函数对字符的转换 使用 TO_DATE 函数,TO_CHAR,函数对,数字,的转换,下面是在,TO_CHAR,函数中经常使用的几种格式,:,TO_CHAR(number,format_model),9,0,$,L,.,数字,零,美元符,本地货币符号,小数点,千位符,TO_CHAR函数对数字的转换下面是在TO_CHAR 函数中,SELECT TO_CHAR(salary,$99,999.00)SALARY,FROM employees,WHERE last_name=Ernst;,TO_CHAR,函数对,数字,的转换,SELECT TO_CHAR(salary,$99,99,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,TO_NUMBER 函数对字符的转换 使用 TO_NUMBE,通用函数,这些函数,适用于任何数据类型，同时也适用于空值,：,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),练习,1,：求公司员工的年薪（含,commission_pct),练习,2,：输出,last_name,department_id,，当,department_id,为,null,时，显示没有部门。,NVL 函数将空值转换成一个已知的值：练习1：求公司员工的年,SELECT last_name,salary,NVL(commission_pct,0),(salary*12)+(salary*12*NVL(commission_pct,0)AN_SAL,FROM employees;,使用,NVL,函数,1,2,1,2,SELECT last_name,salary,NVL(,SELECT last_name,salary,commission_pct,NVL2(commission_pct,SAL+COMM,SAL)income,FROM employees WHERE department_id IN(50,80);,使用,NVL2,函数,1,2,1,2,NVL2(expr1,expr2,expr3):expr1,不为,NULL,，返回,expr2,；为,NULL,，返回,expr3,。,exp1!=null?exp2:exp3,练习：查询员工的奖金率，若为空，返回,0.01,若不为空，返回实际奖金率,+0.015,SELECT last_name,salary,com,SELECT first_name,LENGTH(first_name)expr1,last_name,LENGTH(last_name)expr2,NULLIF(LENGTH(first_name),LENGTH(last_name)result,FROM employees;,使用,NULLIF,函数,1,2,3,1,2,3,NULLIF(expr1,expr2):,相等返回,NULL,，不等返回,expr1,SELECT first_name,LENGTH(firs,使用,COALESCE,函数,COALESCE,与,NVL,相比的优点在于,COALESCE,可以同时处理交替的多个值。,如果第一个表达式为空,则返回下一个表达式，对其他的参数进行,COALESCE,。,使用 COALESCE 函数COALESCE 与 NVL 相,SELECT last_name,COALESCE(commission_pct,salary,10)comm,FROM employees,ORDER BY commission_pct;,使用,COALESCE,函数,SELECT last_name,使用 COALESCE,条件表达式,在,SQL,语句中使用,IF-THEN-ELSE,逻辑,使用两种方法,:,CASE,表达式,DECODE,函数,条件表达式在 SQL 语句中使用IF-THEN-ELSE 逻,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,练习：查询部门号为,10,20,30,的员工信息,若部门号为,10,则打印其工资的,1.1,倍,20,号部门,则打印其工资的,1.2,倍,30,号部门打印其工资的,1.3,倍数,CASE 表达式在需要使用 IF-THEN-ELSE 逻辑时,SELECT last_name,job_id,salary,CASE,job_id,WHEN,IT_PROG,THEN,1.10*salary,WHEN,ST_CLERK,THEN,1.15*salary,WHEN,SA_REP,THEN,1.20*salary,ELSE,salary,END,REVISED_SALARY,FROM employees;,CASE,表达式,下面是使用,case,表达式的一个例子：,SELECT last_name,job_id,sala,DECODE,函数,在需要使用,IF-THEN-ELSE,逻辑时,:,DECODE(col|expression,search1,result1,search2,result2,.,default),DECODE 函数在需要使用 IF-THEN-ELSE 逻辑,DECODE,函数,SELECT last_name,job_id,salary,DECODE(job_id,IT_PROG,1.10*salary,ST_CLERK,1.15*salary,SA_REP,1.20*salary,salary),REVISED_SALARY,FROM employees;,DECODE 函数SELECT last_name,job,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_RATE,FROM employees,WHERE department_id=80;,使用,decode,函数的一个例子：,DECODE 函数SELECT last_name,sal,嵌套函数,单行函数可以嵌套。,嵌套函数的执行顺序是由内到外。,F3,(F2,(F1(col,arg1),arg2),arg3),步骤,1=,结果,1,步骤,2=,结果,2,步骤,3=,结果,3,嵌套函数单行函数可以嵌套。F3(F2(F1(col,arg1,SELECT last_name,NVL(TO_CHAR(manager_id),No Manager),FROM employees,WHERE manager_id IS NULL;,嵌套函数,SELECT last_name,嵌套函数,总 结,通过本章学习，您应该学会,:,使用函数对数据进行计算,使用函数修改数据,使用函数控制一组数据的输出格式,使用函数改变日期的显示格式,使用函数改变数据类型,使用,NVL,函数,使用,IF-THEN-ELSE,逻辑,总 结通过本章学习，您应该学会:,