《ACLE第四章单行函数.ppt》由会员分享，可在线阅读，更多相关《ACLE第四章单行函数.ppt（46页珍藏版）》请在三一办公上搜索。
1、,单行函数,本章要点,字符函数数字函数日期函数转换函数通用函数,SQL 函数,函数,函数（FunctionFunction）执行作用,SQL 函数的两种类型,函数,单行函数,多行函数,单行函数,单行函数：操纵数据项接受多个参数并返回一个值作用于每一个返回行每行返回一个结果可以修改数据类型可以嵌套接受多个参数，参数可以是一个列或者一个表达式,function_name(arg1,arg2,.),单行函数,转换,字符,数字,日期,通用,单行函数,字符函数,字符函数,LOWERUPPERINITCAP,CONCATSUBSTRLENGTHINSTRLPAD|RPADTRIMREPLACE,大小写处理
2、函数,字符处理函数,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 rows selected,SELECT employee_id,last_name,depart
3、ment_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,字符处理函数,字符串处理函数：,SELECT employee_id,CONCAT(first_name,la
4、st_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;
5、,使用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函数,计算所有是销售代表的雇员的工资被5000除后的余数,日期函数,常用的日期运算如下：日期类型列或表达式可以加减数字，功能是在该日期上加减对应的天数。如：08-JA
6、N-08+15结果是 23-JAN-08。日期类型列或表达式之间可以进行减操作，功能是计算两个日期之间间隔了多少天。如：23-JAN-08-08-JAN-08结果15天。如果需要加减相应小时或分钟，可以使用n/24来实现。,月的3字母缩写 JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC,用日期做算术运算,SELECT last_name,(SYSDATE-hire_date)/7 AS WEEKSFROM employeesWHERE department_id=90;,常用日期函数,SYSDATE：返回系统日期MONTHS_BETWEEN：返回
7、两个日期间隔的月数ADD_MONTHS：在指定日期基础上加上相应的月数NEXT_DAY：返回某一日期的下一个指定日期LAST_DAY：返回指定日期当月最后一天的日期ROUND(date,fmt)对日期进行指定格式的四舍五入操作。按照YEAR、MONTH、DAY等进行四舍五入。TRUNC(date,fmt)对日期进行指定格式的截断操作。按照YEAR、MONTH、DAY等进行截断。EXTRACT：返回从日期类型中取出指定年、月、日,MONTHS_BETWEEN(01-SEP-95,11-JAN-94),使用日期函数,ADD_MONTHS(11-JAN-94,6),NEXT_DAY(01-SEP-9
8、5,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:,日期函数,MONTHS_BETWEEN函数演示公司员工服务的月数SELECT last_name,salary,MONTHS_BETWEEN(SYSDATE
9、,hire_date)monthsFROM employeesORDER BY months;,ADD_MONTHS函数演示99年公司员工转,SELECT last_name,salary,hire_date,ADD_MONTHS(hire_date,3)new_dateFROM employeesWHERE hire_date01-1月-1999;,NEXT_DAY函数演示下周一的日期。,SELECT NEXT_DAY(02-2月-06,星期一)NEXT_DAY FROM DUAL;,LAST_DAY函数演示06年2月2日所在月,SELECT LAST_DAY(02-2月-2006)LAST
10、 DAY FROM DUAL;,ROUND函数演示98年入职员工入职日期按月四舍五入。,SELECT employee_id,hire_date,ROUND(hire_date,MONTH)FROM employeesWHERE SUBSTR(hire_date,-2,2)=98;,TRUNC函数演示98年入职员工入职日期按月截断。,SELECT employee_id,hire_date,TRUNC(hire_date,MONTH)FROM employeesWHERE SUBSTR(hire_date,-2,2)=98;,EXTRACT函数,EXTRACT函数语法EXTRACT(YEAR
11、MONTHDAY FROM日期类型表达式),部门编号是90的部门中所有员工入职月份。,SELECT last_name,hire_date,EXTRACT(MONTH FROM HIRE_DATE)MONTHFROM employeesWHERE department_id=90;,数据类型显性转换,通常是在字符类型，日期类型，数字类型之间进行显性转换。主要有3个显性函数：TO_CHARTO_NUMBERTO_DATE,TO_CHAR 函数,TO_CHAR(date|number,fmt)把日期类型/数字类型的表达式或列转换为变长类型字符类型。fmt指的是需要显示的格式：需要写在单引号中，并且
12、是大小写敏感可包含任何有效的日期格式fm表示去除生成结果的前导零或空格,常用日期格式,YYYY：4位数字表示年份；YY：2位数字表示年份，但是无世纪转换(与RR区别在后面章节介绍)；RR：2位数字表示年份，有世纪转换（与YY区别在后面章节介绍）；YEAR：年份的英文拼写；MM：两位数字表示月份；MONTH：月份英文拼写；DY：星期的英文前三位字母；DAY：星期的英文拼写；D：数字表示一星期的第几天，星期天是一周的第一天。DD：数字表示一个月中的第几天；DDD：数字表示一年中的第几天。,常用时间格式,AM 或PM：上下午表示；HH 或HH12或HH24：数字表示小时。HH12代表12小时计时，H
13、H24代表24小时计时；MI：数字表示分钟；SS：数字表示秒；,一些特殊格式,TH：显示数字表示的英文序数词，如：DDTH显示天数的序数词。SP：显示数字表示的拼写。SPTH：显示数字表示的序数词的拼写。,TO_CHAR(SYSDATE,DDSPTH)THIRTIETH,“字符串”:如在格式中显示字符串，需要两端加双引号。,TO_CHAR(SYSDATE,DD“of”MONTH)18 of 10月,日期到字符型转换,TO_CHAR函数进行日期到字符型复杂格式转换演示。,alter session set nls_language=simplified chinese;,SELECT emplo
14、yee_id,last_name,TO_CHAR(hire_date,Day,the Ddspthof YYYY HH24:MI:SS)hire_dateFROM employeesWHERE department_id=90;,数字到字符型转换,具体格式如下：9：一位数字；0：一位数字或前导零；$：显示为美元符号；L：显示按照区域设置的本地货币符号；.：小数点;,：千位分割符；,TO_CHAR函数进行数字到字符型格式转换,SELECT last_name,TO_CHAR(salary,$99,999.00)salaryFROM employeesWHERE last_name=King;,T
15、O_NUMBER和TO_DATE函数,TO_NUMBER(char,fmt)把字符类型列或表达式转换为数字类型。使用格式和TO_CHAR中转换成字符类型中的格式相同在转换时让Oracle知道字符串中每部分的功能TO_DATE(char,fmt)把字符类型列或表达式转换为日期类型。格式和TO_CHAR中转换成字符类型中的格式相同。,通用函数,这些函数可用于任意数据类型，并且适用于空值 NVL(expr1,expr2)NVL2(expr1,expr2,expr3)NULLIF(expr1,expr2)COALESCE(expr1,expr2,.,exprn),SELECT last_name,sa
16、lary,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,SELECT first_name,LENGTH(first_name)expr1,last_name,
17、LENGTH(last_name)expr2,NULLIF(LENGTH(first_name),LENGTH(last_name)resultFROM employees;,使用NULLIF函数,1,2,3,1,2,3,SELECT last_name,COALESCE(commission_pct,salary,10)commFROM employeesORDER BY commission_pct;,使用COALESCE函数,条件表达式,在SQL 语句中提供IF-THEN-ELSE 逻辑的使用两种用法：CASE表达式DECODE函数,CASE表达式,使得IF-THEN-ELSE 条件判断
18、容易实现,CASE 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 EN
19、D REVISED_SALARYFROM employees;,使用CASE表达式,使得IF-THEN-ELSE 条件判断容易实现：,DECODE函数,使得CASE或者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)REVIS
20、ED_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;,对每一个在部门80的雇员显示可用的税率,本章小结,单行函数介绍字符函数的使用数字函数的使用日期函数的使用转换函数的使用其他函数的使用,练习,1.计算2000年1月1日到现在有多少月，多少周（四舍五入）。2.查询员工last_
21、name的第三个字母是a的员工的信息(使用2个函数)。3.使用trim函数将字符串hello、Hello、bllb、hello 分别处理得到下列字符串ello、Hello、ll、hello。4.将员工工资按如下格式显示：123,234.00 RMB5.查询员工的last_name及其经理（manager_id），要求对于没有经理的显示“No Manager”字符串。6.将员工的参加工作日期按如下格式显示：月份/年份。7.在employees表中查询出员工的工资，并计算应交税款：如果工资小于1000,税率为0，如果工资大于等于1000并小于2000，税率为10，如果工资大于等于2000并小于3000，税率为15，如果工资大于等于3000，税率为20。,
