Oracle SQL&PLSQL课件：第3章 单行函数.ppt

单击此处编辑母版标题样式,*,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,Oracle SQL&PL/SQL,第,3,章 单行函数,了解,SQL,函数的分类；,掌握常用单行函数的使用方法；,了解数据类型隐式转换的原则，掌握显式转换函数的使用方法。,本章要点,SQL,函数,函数,输入,参数 1,参数,2,参数,n,函数执行作用,输出,结果值,两种类型的,SQL,函数,Functions,单行函数,多行函数,单行函数,操作数据项,接收参数并返回一个结果,对每个返回的行起作用,每行返回一个结果,可以改变数据类型,能被嵌套,function_name,(,column,|,expression,arg1,arg2,.,),单函函数,转换,字符,数值,日期,通用,单行函数,字符函数,字符函数,LOWER,UPPER,INITCAP,CONCAT,SUBSTR,LENGTH,INSTR,LPAD,TRIM,大小写转换,字符处理,字符函数,字符函数,LOWER,UPPER,INITCAP,CONCAT,SUBSTR,LENGTH,INSTR,LPAD,TRIM,大小写转换,字符处理,函数,结果,大小写转换函数,转化字符串的大小写,LOWER(,SQL Course,),UPPER(,SQL Course,),INITCAP(,SQL Course,),sql,course,SQL COURSE,Sql,Course,使用大小写转换函数,显示,Blake,的雇员编号、姓名和部门编号。,SQL SELECT,empno,ename,deptno,2 FROM,emp,3 WHERE,ename,=,blake,;,no rows selected,EMPNO ENAME DEPTNO,-,7698 BLAKE 30,SQL SELECT,empno,ename,deptno,2 FROM,emp,3 WHERE,ename,=UPPER(,blake,);,CONCAT(,Good,String,),SUBSTR(,String,1,3),LENGTH(,String,),INSTR(,String,r,),LPAD(,sal,10,*,),TRIM(,S,FROM,SSMITH,),GoodString,Str,6,3,*5000,MITH,函数,结果,字符处理函数,字符处理函数,使用字符处理函数,SQL SELECT,ename,CONCAT(,ename,job),LENGTH(,ename,),2 INSTR(,ename,A),3 FROM,emp,4 WHERE,SUBSTR(job,1,5)=SALES;,ENAME CONCAT(ENAME,JOB)LENGTH(ENAME)INSTR(ENAME,A),-,MARTIN MARTINSALESMAN 6 2,ALLEN ALLENSALESMAN 5 1,TURNER TURNERSALESMAN 6 0,WARD WARDSALESMAN 4 2,数值函数,ROUND:,四舍五入到指定的小数位,ROUND(45.926,2)45.93,TRUNC:,截取到指定的小数位,TRUNC(45.926,2)45.92,MOD:,取余数,MOD(1600,300)100,使用,ROUND,函数,SQL SELECT ROUND(45.923,2),ROUND(45.923,0),2 ROUND(45.923,-1),3 FROM DUAL;,ROUND(45.923,2)ROUND(45.923,0)ROUND(45.923,-1),-,45.92 46 50,SQL SELECT TRUNC(45.923,2),TRUNC(45.923),2 TRUNC(45.923,-1),3 FROM DUAL;,TRUNC(45.923,2)TRUNC(45.923)TRUNC(45.923,-1),-,45.92 45 40,使用,TRUNC,函数,使用,MOD,函数,计算工作为,salsman,的雇员的薪水和佣金相除后的剩余。,SQL SELECT,ename,sal,comm,MOD(,sal,comm,),2 FROM,emp,3 WHEREjob=SALESMAN;,ENAME SAL COMM MOD(SAL,COMM),-,MARTIN 1250 1400 1250,ALLEN 1600 300 100,TURNER 1500 0 1500,WARD 1250 500 250,日期的处理,Oracle,是以一种内部的数值形式存储日期的，即：世纪、年、月、日、小时、分、秒。,默认的日期形式是：,DD-MON-RR.,SYSDATE,是一个可以返回日期和时间的函数,DUAL,是一个虚拟表用于查看,SYSDATE.,日期的运算,对一个日期型数据加上或减去一个数可以得到一个新的日期型数据。,两个日期型数据相减得到这两个日期的间隔数。,如要以小时相加则必须用小时数除以24得到的数据进行相加。,对日期使用算术运算符,SQL SELECT,ename,(SYSDATE-,hiredate,)/7 WEEKS,2 FROM,emp,3 WHERE,deptno,=10;,ENAME WEEKS,-,KING 830.93709,CLARK 853.93709,MILLER 821.36566,RR,日期格式,当前年份,1995,1995,2001,2001,指定的日期,27-OCT-95,27-OCT-17,27-OCT-17,27-OCT-95,RR,格式,1995,2017,2017,1995,YY,格式,1995,1917,2017,2095,如果当前年份的两位数是：,0,49,049,50,99,50,99,返回的日期是当前世纪的日期,返回的日期是下一个世纪的日期,返回的日期是上一个世纪的日期,返回的日期是当前世纪的日期,如果指定两位数年份是：,日期函数,两个日期之间间隔多少个月,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-SEP-95),19.6774194,11-,JUL-94,08-,SEP-95,30-,SEP-95,ROUND(25-JUL-95,MONTH)01-AUG-95,ROUND(25-JUL-95,YEAR)01-JAN-96,TRUNC(25-JUL-95,MONTH)01-JUL-95,TRUNC(25-JUL-95,YEAR)01-JAN-95,使用日期函数,转换函数,隐式数据类型转换,显式数据类型转换,数据类型转换,隐式转换,赋值语句中，,Oracle,服务器自动完成以下转换：,VARCHAR2 or CHAR,从,到,VARCHAR2 or CHAR,NUMBER,DATE,NUMBER,DATE,VARCHAR2,VARCHAR2,隐式转换,在表达式中，,Oracle,服务器自动完成以下转换：,VARCHAR2 or CHAR,从,到,VARCHAR2 or CHAR,NUMBER,DATE,显式转换,NUMBER,CHARACTER,TO_CHAR,TO_NUMBER,DATE,TO_CHAR,TO_DATE,显式转换,NUMBER,CHARACTER,TO_CHAR,TO_NUMBER,DATE,TO_CHAR,TO_DATE,显式转换,NUMBER,CHARACTER,TO_CHAR,TO_NUMBER,DATE,TO_CHAR,TO_DATE,TO_CHAR,用于日期型,日期格式模型：,必须用单引号引起来并且是大小写敏感的,可以包含任何有效的日期元素,可以用,fm,来消除前导空格或零,使用逗号与日期型数据分隔开,TO_CHAR(,date,fmt,),YYYY,日期格式模型的元素,YEAR,MM,MONTH,DY,DAY,完整的年份数字表示,年份的英文表示,用两位数字来表示月份,用,3,个英文字符所写来表示星期几,星期几完成的英文表示,月份的英文表示,日期格式模型的元素,用时间元素格式化日期的时间部分,通过使用双引号可以添加字符串,使用数字下标可以拼写数字号码,HH24:MI:SS AM,15:45:32,PM,DD of MONTH,12,of OCTOBER,ddspth,fourteenth,TO_CHAR,用于日期型,SQL SELECT,ename,2 TO_CHAR(,hiredate,fmDD,Month YYYY)HIREDATE,3 FROM,emp,;,ENAME HIREDATE,-,KING 17 November 1981,BLAKE 1 May 1981,CLARK 9 June 1981,JONES 2 April 1981,MARTIN 28 September 1981,ALLEN 20 February 1981,.,14 rows selected.,TO_CHAR,用于数值型,通过在,TO_CHAR,中使用以下形式可以把数值型数据转化成变长的字符串：,TO_CHAR(,number,fmt,),9,0,$,L,.,一位数字,显示前导零,显示美元符号,显示本地货币号,显示小数点,显示千位符,TO_CHAR,用于数值型,SQL SELECTTO_CHAR(,sal,$99,999)SALARY,2 FROM,emp,3 WHERE,ename,=SCOTT;,SALARY,-,$3,000,TO_NUMBER,和,TO_DATE,函数,使用,TO_NUMBER,将一个字符串转换成数值型数据。,TO_NUMBER(,char,fmt,),使用,TO_DATE,将一个字符串转换成日期型数据。,TO_DATE(,char,fmt,),NVL,函数,将,NULL,转换为一个实际的值,数据类型可以是,date,、,character,、,number,。,数据类型必须匹配,NVL(,comm,0),NVL(,hiredate,01-JAN-97),NVL(job,No Job Yet),SQL SELECT,ename,sal,comm,(,sal,*12)+NVL(,comm,0),2 FROM,emp,;,使用,NVL,函数,ENAME SAL COMM(SAL*12)+NVL(COMM,0),-,KING 5000 60000,BLAKE 2850 34200,CLARK 2450 29400,JONES 2975 35700,MARTIN 1250 1400 16400,ALLEN 1600 300 19500,.,14 rows selected.,DECODE,函数,使用,DECODE,函数更便于完成诸如,CASE,或,IF-THEN-ELSE,语句的条件查询,DECODE(,col,/expression,search1,result1,search2,result2,.,default,),使用,DECODE,函数,SQL SELECT job,sal,2 DECODE(job,ANALYST,SAL*1.1,3 CLERK,SAL*1.15,4 MANAGER,SAL*1.20,5 SAL),6 REVISED_SALARY,7 FROM,emp,;,JOB SAL REVISED_SALARY,-,PRESIDENT 5000 5000,MANAGER 2850 3420,MANAGER 2450 2940,.,14 rows selected.,使用,DECODE,函数,SQL SELECT,ename,sal,2 DECODE(TRUNC(,sal,/1000,0),3 0,0.00,4 1,0.09,5 2,0.20,6 3,0.30,7 4,0.40,8 5,0.42,9 6,0.44,10 0.45)TAX_RATE,11 FROM,emp,12 WHERE,deptno,=30;,显示部门编号为,30,的每个雇员相适应的税率。,函数的嵌套,单行函数可以嵌套于任何层。,嵌套的函数是从最里层向最外层的顺序计算的。,F3,(F2,(F1(,col,arg1),arg2),arg3),Step 1=Result 1,Step 2=Result 2,Step 3=Result 3,函数的嵌套,SQL SELECT,ename,2 NVL(TO_CHAR(mgr),No Manager),3 FROM,emp,4 WHEREmgr IS NULL;,ENAME NVL(TO_CHAR(MGR),NOMANAGER),-,KING No Manager,