《单行函数》PPT课件.ppt

《《单行函数》PPT课件.ppt》由会员分享，可在线阅读，更多相关《《单行函数》PPT课件.ppt（46页珍藏版）》请在三一办公上搜索。

1、,单行函数,目标,掌握SQL中不同类型的函数。掌握在 SELECT 语句中使用字符，数字和日期函数。描述转换型函数的用途。,单行函数,单行函数:操作数据对象接受函数返回一个结果只对一行进行变换每行返回一个结果可以转换数据类型可以嵌套参数可以是一列或一个值,function_name(arg1,arg2,.),单行函数,Conversion,Character,Number,Date,General,Single-row functions,字符函数,Characterfunctions,LOWERUPPERINITCAP,CONCATSUBSTRLENGTHINSTRLPAD|RPADTRIM

2、REPLACE,Case-manipulation functions,Character-manipulationfunctions,Function,Result,大小写控制函数,这类函数改变字符的大小写。,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 r

3、ows 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),HelloWorldHello106*2400024000*elloWorld,Function,Result,字符控制函数,

4、这类函数控制字符:,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(4

5、5.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 内部使用数字存储日期:世纪,年,月,日,小时,分钟,秒。默认的日期格式是

6、 DD-MON-RR.,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,N

7、EXT_DAY,LAST_DAY,ROUND,TRUNC,加月份以日期,指定日期的下一天,一个月中的最后一天,Round 日期,Truncate 日期,函数,描述,MONTHS_BETWEEN(01-SEP-95,11-JAN-94),日期函数,ADD_MONTHS(11-JAN-94,6),NEXT_DAY(01-SEP-95,FRIDAY)SELECT NEXT_DAY(TO_DATE(2006-08-15,YYYY-MM-DD),星期二)FROM EMP,LAST_DAY(01-FEB-95),19.6774194,08-11-JUL-94SEP-95,28-FEB-95,ROUND(S

8、YSDATE,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:,TO_CHAR 函数对日期的转换,格式:必须包含在单引号中而且大小写敏感。可以包含任意的有效的日期格式。可以使用 fm 去掉多余的空格或者前导零。与日期只用逗号隔开。,TO_CHAR(date,format_model),YYYY,日期格式的元素,YEAR,MM,MONTH,DY,DAY,以数字表示全年,拼写出的年

9、,两位数的月,三个字母缩写的一个星期中的天,星期的全拼,全月名,MON,三个字母的月缩写,DD,数字日,日期格式的元素,时间格式作为日期的一部分.用双引号括起来以加字符串.,TO_CHAR 函数对日期的转换,SELECT last_name,TO_CHAR(hire_date,fmDD Month YYYY)AS HIREDATEFROM employees;,TO_CHAR 函数对数字的转换,下面是在TO_CHAR 函数中经常使用的几种格式:如:select to_char(sal,$99G999D9999)from emp,TO_CHAR(number,format_model),9,0,

10、$,L,.,代表一个数字,强制显示0,放置一个$符,放置一个浮动本地货币符,显示小数点D,显示千位指示符G(通常和D搭配),SELECT TO_CHAR(salary,$99,999.00)SALARYFROM employeesWHERE last_name=Ernst;,TO_CHAR函数对数字的转换,一般将.放在,后,TO_NUMBER 和 TO_DATE 函数,使用 TO_NUMBER 函数将字符转换成数字:,TO_NUMBER(char,format_model),TO_DATE(char,format_model),如:select to_number(100.00,999,999

11、.999999)from emp.前面的数字个数必须大于等于char中.前面的位数,如:SELECT TO_DATE(2006-08-15 星期二,yyyy-mm-dd day)FROM DUAL,RR 日期格式,Oracle9i 中的缺省日期格式DD-MON-RROracle 为了解决千年问题，而引入了RR 日期型格式1、如果当前年份的最后两位数（即不包括世纪）为049，并且指定的年份的最后两位数也为049，则返回的日期在本世纪2、如果当前年份的最后两位数（即不包括世纪）为049，并且指定的年份的最后两位数为5099，则返回的日期在上一世纪3、如果当前年份的最后两位数（即不包括世纪）为509

12、9，并且指定的年份的最后两位数为049，则返回的日期在下一世纪4、如果当前年份的最后两位数（即不包括世纪）为5099，并且指定的年份的最后两位数也为5099，则返回的日期在本世纪 举例:select to_char(to_date(09-feb-98,DD-MON-RR),yyyy-mm-dd)from dual返回结果为:1998-02-09,RR 日期格式,使用RR日期格式查找雇佣日期在1990年之前的员工，在1999或2009年，使用下面的命令会产生相同的结果:,SELECT last_name,TO_CHAR(hire_date,DD-Mon-YYYY)FROM employeesWH

13、ERE hire_date TO_DATE(01-Jan-90,DD-Mon-RR);,通用函数,这些函数适用于任何数据类型，同时也适用于空值：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),SELECT last_name,salary,N

14、VL(commission_pct,0),(salary*12)+(12*NVL(commission_pct,0)AN_SALFROM employees;,使用NVL函数,1,2,1,2,如果表达式1的值为null,返回表达式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,如果表达式1的值不为null,返回表达式2,否则返回表达式3,SELECT

15、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,当表达式1等于表达式2时,返回null,否则返回表达式1的值,使用 COALESCE 函数,如果表达式1的值不为空,返回表达式1的值,否则返回表达式2的值,如果表达式2的值为空,返回表达式3的值COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。

16、即返回参数列表中第一个非空表达式。如:SELECT sal,comm,coalesce(comm,sal,100)FROM EMP 通常最后一个参数会是一个常量,SELECT last_name,COALESCE(commission_pct,salary,10)commFROM employeesORDER BY commission_pct;,使用 COALESCE 函数,条件表达式,在 SQL 语句中使用IF-THEN-ELSE 逻辑。使用两种方法:CASE 表达式DECODE 函数,CASE 表达式,在需要使用 IF-THEN-ELSE 逻辑时:expr不能为null和比较运算符,CA

17、SE expr WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_exprEND,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 REVIS

18、ED_SALARYFROM employees;,CASE 表达式,下面是使用case表达式的一个例子：,DECODE 函数,在需要使用 IF-THEN-ELSE 逻辑时:expression不能为NULL和比较运算符,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)REV

19、ISED_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),Step 1=Result 1,Step 2=Result 2,Step 3=Result 3,SELECT last_name,NVL(TO_CHAR(manager_id),No Manager)FROM employeesWHERE manager_id IS NULL;,嵌套函数,总结,通过本章学习，您应该学会:使用函数对数据进行计算使用函数修改数据使用函数控制一组数据的输出格式使用函数改变日期的显示格式使用函数改变数据类型使用 NVL 函数使用IF-THEN-ELSE 逻辑,谢 谢！,