第9讲Oracle单行函数.doc

第9讲 单行函数单行函数：1、 字符函数：接受字符输入并且返回字符或数值。2、 数值函数：接受数值输入并返回数值。3、 日期函数：对日期型数据进行操作。4、 转换函数：从一种数据类型转换为另一种数据类型。5、 通用函数：nvl函数、decode函数。1、常用字符函数1.1大小写转换函数：（1）Lower（column|str）：将字符串中大写字母转换为小写字母。（2）Upper（column|str）：将字符串中小写字母转换为大写字母。（3）Initcap（column|str）：将每个单词的第一个字母转换成大写，其余的字母转换成小写。例：显示工作是文员的雇员姓名和工作，要求姓名首字母大写，工作小写，查询条件不区分大小写。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次出现次数（默认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;练习：显示雇员姓名和姓名中是否有字符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位置- -Smith 没找到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）函数：取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日期，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：计算雇员进入公司的星期数select 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) 受雇当月的最后一天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（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/1980ALLEN 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: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.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_number($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）函数：该函数用于处理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，依次类推；如果没有任何匹配关系，则返回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 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 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练习：依据例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 TAX_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 0FORD 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, decode(p,0,0.0, 1,(ms