《oracle教学课件》尚硅谷-宋红康-03-单行函数.ppt

第3节单 行 函 数,讲师：宋红康 新浪微博：尚硅谷-宋红康,目 标,通过本章学习，您将可以:SQL中不同类型的函数在 SELECT 语句中使用字符，数字，日期和转换函数使用条件表达式,SQL 函数,函 数,函数执行,y=f(x1,x2),两种 SQL 函数,函 数,单行函数,多行函数,单行函数,单行函数:操作数据对象接受参数返回一个结果只对一行进行变换每行返回一个结果可以转换数据类型可以嵌套参数可以是一列或一个值,function_name(arg1,arg2,.),单行函数,转 换,字 符,数 值,日 期,通 用,单行函数,字符函数,字符函数,LOWERUPPERINITCAP,CONCATSUBSTRLENGTHINSTRLPAD|RPADTRIMREPLACE,大小写控制函数,字符控制函数,函数,结果,大小写控制函数,这类函数改变字符的大小写。,LOWER(SQL Course)UPPER(SQL Course)INITCAP(SQL Course),sql courseSQL COURSESql Course,大小写控制函数,显示员工 Higgins的信息:,SELECT employee_id,last_name,department_idFROM employeesWHERE last_name=higgins;no rows selected,SELECT employee_id,last_name,department_idFROM employeesWHERE LOWER(last_name)=higgins;,CONCAT(Hello,World)SUBSTR(HelloWorld,1,5)LENGTH(HelloWorld)INSTR(HelloWorld,W)LPAD(salary,10,*)RPAD(salary,10,*)TRIM(H FROM HelloWorld)REPLACE(abcd,b,m),HelloWorldHello106*2400024000*elloWorldamcd,函数,结果,字符控制函数,这类函数控制字符:,SELECT employee_id,CONCAT(first_name,last_name)NAME,job_id,LENGTH(last_name),INSTR(last_name,a)Contains a?FROM employeesWHERE SUBSTR(job_id,4)=REP;,字符控制函数,1,2,3,1,2,3,数字函数,ROUND:四舍五入ROUND(45.926,2)45.93TRUNC:截断TRUNC(45.926,2)45.92MOD:求余MOD(1600,300)100,SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)FROM DUAL;,ROUND 函数,DUAL 是一个伪表，可以用来测试函数和表达式,1,2,3,3,1,2,SELECT TRUNC(45.923,2),TRUNC(45.923),TRUNC(45.923,-2)FROM DUAL;,TRUNC 函数,3,1,2,1,2,3,SELECT last_name,salary,MOD(salary,5000)FROM employeesWHERE job_id=SA_REP;,MOD 函数,日 期,Oracle 中的日期型数据实际含有两个值:日期和时间。,SELECT last_name,hire_dateFROM employeesWHERE last_name like G%;,日 期,函数SYSDATE 返回:日期时间,日期的数学运算,在日期上加上或减去一个数字结果仍为日期。两个日期相减返回日期之间相差的天数。日期不允许做加法运算，无意义可以用数字除24来向日期中加上或减去天数。,日期的数学运算,SELECT last_name,(SYSDATE-hire_date)/7 AS WEEKSFROM employeesWHERE department_id=90;,日期函数,两个日期相差的月数,MONTHS_BETWEEN,ADD_MONTHS,NEXT_DAY,LAST_DAY,ROUND,TRUNC,向指定日期中加上若干月数,指定日期的下一个星期*对应的日期,本月的最后一天,日期四舍五入,日期截断,函数,描述,MONTHS_BETWEEN(01-SEP-95,11-JAN-94),日期函数,ADD_MONTHS(11-JAN-94,6),NEXT_DAY(01-SEP-95,FRIDAY),LAST_DAY(01-FEB-95),19.6774194,11-JUL-94,08-SEP-95,28-FEB-95,ROUND(SYSDATE,MONTH)01-AUG-95,ROUND(SYSDATE,YEAR)01-JAN-96,TRUNC(SYSDATE,MONTH)01-JUL-95,TRUNC(SYSDATE,YEAR)01-JAN-95,日期函数,Assume SYSDATE=25-JUL-95:,yyyy 年mm 月dd 日day 星期 hh 小时 mi 分钟 ss 秒,转换函数,隐 性,显 性,数据类型转换,隐式数据类型转换,Oracle 自动完成下列转换：,VARCHAR2 or CHAR,源数据类型,目标数据类型,VARCHAR2 or CHAR,NUMBER,DATE,NUMBER,DATE,VARCHAR2,VARCHAR2,varchar2,number,date,显式数据类型转换,NUMBER,CHARACTER,TO_CHAR,TO_CHAR函数对日期的转换,格式:必须包含在单引号中而且大小写敏感。可以包含任意的有效的日期格式。日期之间用逗号隔开。,TO_CHAR(date,format_model),SELECT TO_CHAR(sysdate,yyyy-mm-dd hh:mi:ss)FROM dual;,YYYY,日期格式的元素,YEAR,MM,MONTH,DY,DAY,2004,TWO THOUSAND AND FOUR,02,MON,MONDAY,JULY,MON,JUL,DD,02,日期格式的元素,时间格式使用双引号向日期中添加字符,TO_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-17,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_CHAR函数对数字的转换,下面是在TO_CHAR 函数中经常使用的几种格式:,TO_CHAR(number,format_model),9,0,$,L,.,数字,零,美元符,本地货币符号,小数点,千位符,SELECT 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(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),练习1：求公司员工的年薪（含commission_pct)练习2：输出last_name,department_id，当department_id为null时，显示没有部门。,SELECT last_name,salary,NVL(commission_pct,0),(salary*12)+(salary*12*NVL(commission_pct,0)AN_SALFROM employees;,使用NVL函数,1,2,1,2,SELECT last_name,salary,commission_pct,NVL2(commission_pct,SAL+COMM,SAL)incomeFROM 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 first_name,LENGTH(first_name)expr1,last_name,LENGTH(last_name)expr2,NULLIF(LENGTH(first_name),LENGTH(last_name)resultFROM employees;,使用 NULLIF 函数,1,2,3,1,2,3,NULLIF(expr1,expr2):相等返回NULL，不等返回expr1,使用 COALESCE 函数,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 逻辑时:,CASE 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*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_PROG,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函数的一个例子：,嵌套函数,单行函数可以嵌套。嵌套函数的执行顺序是由内到外。,F3(F2(F1(col,arg1),arg2),arg3),步骤1=结果1,步骤2=结果2,步骤3=结果3,SELECT last_name,NVL(TO_CHAR(manager_id),No Manager)FROM employeesWHERE manager_id IS NULL;,嵌套函数,总 结,通过本章学习，您应该学会:使用函数对数据进行计算使用函数修改数据使用函数控制一组数据的输出格式使用函数改变日期的显示格式使用函数改变数据类型使用 NVL 函数使用IF-THEN-ELSE 逻辑,