《2022年第9讲 Oracle单行函数.doc》由会员分享，可在线阅读，更多相关《2022年第9讲 Oracle单行函数.doc（9页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、第9讲 单行函数单行函数：1、 字符函数：接受字符输入同时返回字符或数值。2、 数值函数：接受数值输入并返回数值。3、 日期函数：对日期型数据进展操作。4、 转换函数：从一种数据类型转换为另一种数据类型。5、 通用函数：nvl函数、decode函数。1、常用字符函数1.1大小写转换函数：（1）Lower（column|str）：将字符串中大写字母转换为小写字母。（2）Upper（column|str）：将字符串中小写字母转换为大写字母。（3）Initcap（column|str）：将每个单词的第一个字母转换成大写，其余的字母转换成小写。例：显示工作是文员的雇员姓名和工作，要求姓名首字母大写，工
2、作小写，查询条件不区分大小写。select initcap(ename),lower(job)from empwhere job=upper(clerk);1.2 字符处理函数（1）Cancat（column1|str1, column2|str2）：将字符串连接在一起。（2）Substr（column|str,m,n）：提取子串，m指定子串的起始位置，n指定字串的长度。（3）Length（column|str）：求字符串长度。（4）Instr（char1,char2,n,m）：在字符串中查找子串位置,返回chr2在char1中的位置，n指定起始搜索位置（默认1），m指定子串第m次出现次数（默
3、认1）。（5）Trim：去除字符串头部和尾部的字符，默认去除空格符。（6）Lpad：以右对齐方式填充字符型数据，左边补充特定字符。（7）Rpad：以左对齐方式填充字符型数据，右边补充特定字符。例1：连接雇员姓名和工作，雇员姓名和工作首字母大写。select concat(initcap(ename),initcap(job) from emp例2：假如雇员职务的前5位字符为“SALES”，则显示雇员姓名、姓名长度、姓名中字符“A”的位置。select ename,length(ename),instr(ename,A) from emp where substr(job,1,5)=SALES;
4、练习：显示雇员姓名和姓名中是否有字符A的信息。满足如下要求。1、姓名首字符大写，其它字符小写。2、假如字符A是雇员姓名的首字符，则显示“字符A在首位”，假如字符A是姓名的最后一个字母，则显示“字符在末尾”，假如字符A不在姓名中，则显示“没找到A字符”，其它情况下，显示“字符A在中间”。select initcap(ename) 姓名, case instr(ename,A) when 0 then 没找到A字符 when 1 then 字符A在首位 when length(ename) then 字符A在末尾 else 字符A在中间 end 字符A位置from emp;姓名 字符A位置- -S
5、mith 没找到A字符Allen 字符A在首位Ward 字符A在中间Jones 没找到A字符Martin 字符A在中间Blake 字符A在中间Clark 字符A在中间Scott 没找到A字符King 没找到A字符Turner 没找到A字符Adams 字符A在首位James 字符A在中间Ford 没找到A字符Miller 没找到A字符2、 常用数值函数（1）Round（column|expression,n）函数：将列或表达式所表示的数值四舍五入到小数点后的n位。（2）Trunc（column|expression,n）函数：将列或表达式所表示的数值截取到小数点后的n位。（3）Mod（m,n）函
6、数：取m除以n后得到的余数。例1：按部门显示平均工资select deptno, round(avg(sal),0),trunc(avg(sal),0) from emp group by deptno;例2：显示工资除以金额后的余数。select ename,sal,comm,mod(sal,nvl(comm,0) from emp;3、常用日期函数（1）Month_between(date1,date2)：返回date1和date2之间的月份数。（2）Add_months(date,n)：date加n个月。（3）Next_day(date,char)：求出date后一周内某天char日期，
7、char能够是一个有效的表示星期几的数字或字符串。（4）Last_day(date)：求出date所在月的最后一天。（5）Round(date,fmt)：将date按fmt指定格式进展四舍五入，fmt是可选项，默认“DD”，将date四舍五入为最近的天。（6）Trunk(date,fmt)：将date按fmt指定格式进展截取，fmt是可选项，默认“DD”，将date截取为最近的天。/*将日期显示格式设置为美国标准格式*/alter session set nls_date_language=american;select sysdate from dual;例1：计算雇员进入公司的星期数sel
8、ect ename,trunc(sysdate-hiredate)/7) weeks from emp;例2：显示雇员编号、受雇日期、受雇月数、满半年的转正日期、受雇后第一个星期五、受雇当月的最后一天。select ename 雇员姓名, hiredate 受雇日期, trunc(months_between(sysdate,hiredate) 受雇月数, add_months(hiredate,6) 半年转正日期, next_day(hiredate,FRIDAY) 受雇后的第一个星期五,next_day(hiredate,6) 受雇后的第一个星期五, last_day(hiredate)
9、受雇当月的最后一天from emp;例3：比拟87年入职的雇员受雇日期按月四舍五入和截尾的结果。select empno,hiredate, round(hiredate,MONTH), trunc(hiredate,MONTH)from empwhere hiredate like %87%;EMPNO HIREDATE ROUND(HIREDATE,MONTH) TRUNC(HIREDATE,MONTH)- - - - 7788 1987-4-19 1987-5-1 1987-4-1 7876 1987-5-23 1987-6-1 1987-5-14、数据类型转换函数（1）To_char（
10、date|number,fmt）函数：将日期或数值数据按形式fmt转换为变长字符串。（2）To_number(char)：把一个数字组成的字符串转换成数值。常用数字格式：9：一位数字；0：显示前导0；$：显示美元符号；L：显示本地货币符号；.：显示小数点；,：显示千位符。（3）To_date(char,fmt)：把一个表示日期的字符串按照形式fmt转换成日期。例1：查看雇佣日期，显示格式为“dd/mm/yyyy”。select ename,to_char(hiredate,dd/mm/yyyy) hiredate from emp;ENAME HIREDATE- -SMITH 17/12/19
11、80ALLEN 20/02/1981WARD 22/02/1981JONES 02/04/1981MARTIN 28/09/1981BLAKE 01/05/1981CLARK 09/06/1981SCOTT 19/04/1987KING 17/11/1981TURNER 08/09/1981ADAMS 23/05/1987JAMES 03/12/1981FORD 03/12/1981MILLER 23/01/1982Dual 是 Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目的表的Select语句块中。例2：查看当前日期、时间，显示格式为“yyyy-mm-dd hh24:mi
12、:ss”。select to_char(sysdate,yyyy-mm-dd hh24:mi:ss) 系统时间 from dual;系统时间-2011-04-13 11:30:17例3：显示工资，显示格式为“L99,999.99”和“$99,999.99”。select ename 姓名, to_char(sal,L99,999.99) 人民币工资, to_char(round(sal/8,2),$99,999.99) 折合美元from emp;姓名 人民币工资 折合美元- - -SMITH ￥1,600.00 $200.00ALLEN ￥1,600.00 $200.00WARD ￥1,250
13、.00 $156.25JONES ￥2,975.00 $371.88MARTIN ￥1,250.00 $156.25BLAKE ￥2,850.00 $356.25CLARK ￥1,500.00 $187.50SCOTT ￥3,000.00 $375.00KING ￥5,000.00 $625.00TURNER ￥1,500.00 $187.50ADAMS ￥1,100.00 $137.50JAMES ￥950.00 $118.75FORD ￥3,000.00 $375.00MILLER ￥1,300.00 $162.50例4：把字符串“$1234.45”转换成数值。select to_numb
14、er($1234.45,$9999.99) 数值 from dual; 数值-1234.45例5：使用日期字符串“2月 22,1981”查询雇员姓名和入职日期select ename,hiredate from emp where hiredate=to_date(2月 22,1981,month dd,yyyy);ENAME HIREDATE- -WARD 1981-2-225、通用函数（1）Nvl(expr1,expr2)函数：将null值转换为一个实际值。假如expr1是null，则返回expr2，否则返回expr1。（2）Nvl2（expr1,expr2,expr3）函数：该函数用于处
15、理null。假如expr1不是null，则返回expr2，假如expr1是null，则返回expr3。例：显示所有雇员的姓名、工资、奖金和薪水总和。select ename,sal,comm,nvl2(comm,sal+comm,sal) salary from emp;（3）Decode函数：分支处理函数，类似与case语句或if-then-else语句。 语法：Decode(expr,search1,result1,search2,result2,default)假如search1匹配expr，则返回result1；假如search2匹配expr，则返回result2，依次类推；假如没有任
16、何匹配关系，则返回default。例1：显示从事不同工作的雇员按不同比例加薪后的工资水平。select job,sal, decode(job,ANALYST,sal*1.1, CLERK,sal*1.15, MANAGER,sal*1.2, sal) revised_salfrom emp;JOB SAL REVISED_SAL- - -CLERK 1600.00 1840SALESMAN 1600.00 1600SALESMAN 1250.00 1250MANAGER 2975.00 3570SALESMAN 1250.00 1250MANAGER 2850.00 3420MANAGER
17、1500.00 1800ANALYST 3000.00 3300PRESIDENT 5000.00 5000SALESMAN 1500.00 1500CLERK 1100.00 1265CLERK 950.00 1092.5ANALYST 3000.00 3300CLERK 1300.00 1495例2：按雇员的月薪确定相应的税率。select ename,sal,comm, decode(trunc(sal+nvl(comm,0)/1000,0), 0, 0.0, 1, 0.1, 2, 0.2, 3, 0.3, 4, 0.4, 0.5) tax_ratefrom emp;ENAME SAL
18、COMM TAX_RATE- - - -SMITH 1600.00 300.00 0.1ALLEN 1600.00 300.00 0.1WARD 1250.00 500.00 0.1JONES 2975.00 0.2MARTIN 1250.00 1400.00 0.2BLAKE 2850.00 0.2CLARK 1500.00 300.00 0.1SCOTT 3000.00 0.3KING 5000.00 0.5TURNER 1500.00 0.00 0.1ADAMS 1100.00 0.1JAMES 950.00 0FORD 3000.00 0.3MILLER 1300.00 0.1练习：根
19、据例2中确定的税率计算雇员月税金。显示雇员姓名、工资、薪水、税率和税金。select ename,sal,comm,t.tax_rate, (msal-1000)*t.tax_rate tax_sal from emp e, (select empno,(sal+nvl(comm,0) msal, decode(trunc(sal+nvl(comm,0)/1000,0), 0, 0.00, 1, 0.1, 2, 0.2, 3, 0.3, 4, 0.4, 0.5) tax_rate from emp)twhere e.empno=t.empno;ENAME SAL COMM TAX_RATE T
20、AX_SAL- - - - -SMITH 1600.00 300.00 0.1 90ALLEN 1600.00 300.00 0.1 90WARD 1250.00 500.00 0.1 75JONES 2975.00 0.2 395MARTIN 1250.00 1400.00 0.2 330BLAKE 2850.00 0.2 370CLARK 1500.00 300.00 0.1 80SCOTT 3000.00 0.3 600KING 5000.00 0.5 2000TURNER 1500.00 0.00 0.1 50ADAMS 1100.00 0.1 10JAMES 950.00 0 0FO
21、RD 3000.00 0.3 600MILLER 1300.00 0.1 30考虑：税率计算方法的改良？设税金起征点为1000元，1000元以内不交税，超出部分按以下规则征税：（1）1000元以上，同时不到2000元，税率0.1；（2）2000元以上，同时不到3000元，税率0.2；（3）3000元以上，同时不到4000元，税率0.3；（4）4000元以上，同时不到5000元，税率0.4；（5）5000元以上，税率0.5。如5000元收入,税金为：1000*(0.1+0.2+0.3+0.4)=1000元。显示员工的姓名、工资、奖金、税金。select ename,sal,comm, decod
22、e(p,0,0.0, 1,(msal-1000)*t.tax_rate, 2,1000*0.1+(msal-2000)*tax_rate, 3,1000*(0.1+0.2)+(msal-3000)*tax_rate, 4,1000*(0.1+0.2+0.3)+(msal-4000)*tax_rate, 1000*(0.1+0.2+0.3+0.4)+(msal-5000)*tax_rate ) tax_salfrom emp e, (select empno, (sal+nvl(comm,0) msal, trunc(sal+nvl(comm,0)/1000,0) p, decode(trunc
23、(sal+nvl(comm,0)/1000,0), 0, 0.00, 1, 0.1, 2, 0.2, 3, 0.3, 4, 0.4, 0.5) tax_rate from emp)twhere e.empno=t.empno;ENAME SAL COMM TAX_SAL- - - -SMITH 1600.00 300.00 90ALLEN 1600.00 300.00 90WARD 1250.00 500.00 75JONES 2975.00 295MARTIN 1250.00 1400.00 230BLAKE 2850.00 270CLARK 1500.00 300.00 80SCOTT 3000.00 300KING 5000.00 1000TURNER 1500.00 0.00 50ADAMS 1100.00 10JAMES 950.00 0FORD 3000.00 300MILLER 1300.00 30