第9讲 Oracle单行函数

第9讲单行函数单行函数：1、字符函数：接受字符输入并且返回字符或数值。2、数值函数：接受数值输入并返回数值。3、日期函数：对日期型数据进行操作。4、转换函数：从一种数据类型转换为另一种数据类型。5、通用函数：nvl函数、decode函数。1、常用字符函数1.1大小写转换函数：（1）Lower（column|str）：将字符串中大写字母转换为小写字母。（2）Upper（column|str）：将字符串中小写字母转换为大写字母。（3）Initcap（column|str）：将每个单词的第一个字母转换成大写，其余的字母转换成小写。例：显示工作是文员的雇员姓名和工作，要求姓名首字母大写，工作小写，查询条件不区分大小写。selectinitcap(ename),lower(job)fromempwherejob=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：连接雇员姓名和工作，雇员姓名和工作首字母大写。selectconcat(initcap(ename),initcap(job))fromemp例2：如果雇员职务的前5位字符为“SALES”，则显示雇员姓名、姓名长度、姓名中字符“A”的位置。selectename,length(ename),instr(ename,'A')fromempwheresubstr(job,1,5)='SALES';练习：显示雇员姓名和姓名中是否有字符A的信息。满足如下要求。1、姓名首字符大写，其它字符小写。2、如果字符A是雇员姓名的首字符，则显示“字符A在首位”，如果字符A是姓名的最后一个字母，则显示“字符在末尾”，如果字符A不在姓名中，则显示“没找到A字符”，其它情况下，显示“字符A在中间”。selectinitcap(ename)姓名,caseinstr(ename,'A')when0then'没找到A字符'when1then'字符A在首位'whenlength(ename)then'字符A在末尾'else'字符A在中间'end字符A位置fromemp;姓名字符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：按部门显示平均工资selectdeptno,round(avg(sal),0),trunc(avg(sal),0)fromempgroupbydeptno;例2：显示工资除以金额后的余数。selectename,sal,comm,mod(sal,nvl(comm,0))fromemp;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截取为最近的天。/*将日期显示格式设置为美国标准格式*/altersessionsetnls_date_language='american';selectsysdatefromdual;例1：计算雇员进入公司的星期数selectename,trunc((sysdate-hiredate)/7)weeksfromemp;例2：显示雇员编号、受雇日期、受雇月数、满半年的转正日期、受雇后第一个星期五、受雇当月的最后一天。selectename雇员姓名,hiredate受雇日期,trunc(months_between(sysdate,hiredate))受雇月数,add_months(hiredate,6)半年转正日期,next_day(hiredate,'FRIDAY')受雇后的第一个星期五,next_day(hiredate,6)受雇后的第一个星期五,last_day(hiredate)受雇当月的最后一天fromemp;例3：比较87年入职的雇员受雇日期按月四舍五入和截尾的结果。selectempno,hiredate,round(hiredate,'MONTH'),trunc(hiredate,'MONTH')fromempwherehiredatelike'%87%';EMPNOHIREDATEROUND(HIREDATE,'MONTH')TRUNC(HIREDATE,'MONTH')--------------------------------------------------------------77881987-4-191987-5-11987-4-178761987-5-231987-6-11987-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”。selectename,to_char(hiredate,'dd/mm/yyyy')hiredatefromemp;ENAMEHIREDATE--------------------SMITH17/12/1980ALLEN20/02/1981WARD22/02/1981JONES02/04/1981MARTIN28/09/1981BLAKE01/05/1981CLARK09/06/1981SCOTT19/04/1987KING17/11/1981TURNER08/09/1981ADAMS23/05/1987JAMES03/12/1981FORD03/12/1981MILLER23/01/1982Dual是Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的Select语句块中。例2：查看当前日期、时间，显示格式为“yyyy-mm-ddhh24:mi:ss”。selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss')系统时间fromdual;系统时间-------------------2011-04-1311:30:17例3：显示工资，显示格式为“L99,999.99”和“$99,999.99”。selectename姓名,to_char(sal,'L99,999.99')人民币工资,to_char(round(sal/8,2),'$99,999.99')折合美元fromemp;姓名人民币工资折合美元-----------------------------------------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”转换成数值。selectto_number('$1234.45','$9999.99')数值fromdual;数值----------1234.45例5：使用日期字符串“2月22,1981”查询雇员姓名和入职日期selectename,hiredatefromempwherehiredate=to_date('2月22,1981','monthdd,yyyy');ENAMEHIREDATE-------------------------WARD1981-2-225、通用函数（1）Nvl(expr1,expr2)函数：将null值转换为一个实际值。如果expr1是null，则返回expr2，否则返回expr1。（2）Nvl2（expr1,expr2,expr3）函数：该函数用于处理null。如果expr1不是null，则返回expr2，如果expr1是null，则返回expr3。例：显示所有雇员的姓名、工资、奖金和薪水总和。selectename,sal,comm,nvl2(comm,sal+comm,sal)salaryfromemp;（3）Decode函数：分支处理函数，类似与case语句或if-then-else语句。语法：Decode(expr,search1,result1[,search2,result2,……][,default])如果search1匹配expr，则返回result1；如果search2匹配expr，则返回result2，依次类推；如果没有任何匹配关系，则返回default。例1：显示从事不同工作的雇员按不同比例加薪后的工资水平。selectjob,sal,decode(job,'ANALYST',sal*1.1,'CLERK',sal*1.15,'MANAGER',sal*1.2,sal)revised_salfromemp;JOBSALREVISED_SAL-----------------------------CLERK1600.001840SALESMAN1600.001600SALESMAN1250.001250MANAGER2975.003570SALESMAN1250.001250MANAGER2850.003420MANAGER1500.001800ANALYST3000.003300PRESIDENT5000.005000SALESMAN1500.001500CLERK1100.001265CLERK950.001092.5ANALYST3000.003300CLERK1300.0