单行函数
一、单行数值函数
1、round:四舍五入。
格式：round(n1,[n2]):把n1四舍五入到n2位
Select round(123.456) From emp
Select round(123.556) From emp
当n2省略时，四舍五入到整数位。
Select round(123.456,1) From emp
Select round(123.446,1) From emp
Select round(123.446,2) From emp
当n2为正数时，四舍五入到小数点后的第几位。
Select round(123.456,-1) From emp
Select round(125.456,-1) From emp
Select round(125.456,-2) From emp
当n2为负数时，表示从小数点向左数进行四舍五入。
dual:oracle中的一个虚表。主要用来返回单行单列的查询
Select round(123.456,1) From dual
2、trunc:截断
格式：trunc(n1,[n2])
Select trunc(123.456) From dual
Select trunc(123.556) From dual
Select trunc(123.556,2) From dual
Select trunc(123.556,-2) From dual
3、mod:取余
格式:Mod(n1,n2)
Select Mod(10,3)From dual
二、单行字符函数
1、upper：把所有字母转换成大写
Select upper('abcdafdafafdafda fdafdasf') From dual
2、lower：把所有字母转换成大写
Select lower('SDfdDfaaffdasfdda fdafdasf') From dual
3、initcap：把首字母转换成大写，其余转换成小写
Select initcap('SDfdDfaaffdasfdda fdafdasf') From dual
4、concat:字符串连接函数
格式：concat(s1,s2)
SELECT concat('abc','def') FROM dual
SELECT concat(concat('abc','def'),'ghi') FROM dual
5、length:取字符的长度
格式:length(s1)
SELECT length('avadafda') FROM dual
SELECT length(' avadafda ') FROM dual
SELECT ename,length(ename) FROM emp
6、substr:取子串
格式：substr(s1,n1,[n2]):取s1中从第n1位开始，长度为n2的子串。
SELECT substr('abcdefg',3) FROM dual
注意：java中第几位从0开始，而oracle中从1开始。
SELECT substr('abcdefg',3,3) FROM dual
//查询员工表中每个员工名字的前两位。
SELECT ename,substr(ename,1,2) FROM emp
//查询员工表中每个员工名字的后两位。
SELECT ename,substr(ename,length(ename)-1) FROM emp
//取员工姓名中第三个字母是A的员工信息
SELECT * FROM emp WHERE ename LIKE '__A%'
SELECT * FROM emp WHERE substr(ename,3,1) = 'A'
SELECT ename,substr(ename,-3) FROM emp
当n1为负值时，表示从后面第abs(n1)位开始数。
7、instr:取子串的位置
格式:instr(s1,s2, [n1] ,[n2]):取s2在s1中从第n1位开始，第n2次出现的位置
SELECT instr('avadfadfadfad' ,'f',3,2) FROM dual
当n1,n2省略时，默认为1
//取员工姓名中第三个字母是A的员工信息
SELECT * FROM emp WHERE instr(ename,'A')= 3 X
SELECT * FROM emp WHERE instr(ename,'A',3,1) = 3 V
当n1为负值时，表示从后面第abs(n1)位开始数。
8、trim:去掉指定字符串两端指定字符的。
语法1:TRIM(s1):去空格的
SELECT length(TRIM(' hel lo ')) FROM dual
中间的去不掉。
语法2:TRIM(s2 FROM s1) :去掉s1两端中的s2
SELECT TRIM('h' FROM 'helloh') FROM dual
9、replace(s1,s2,s3):把s1中的s2用s3替换。
SELECT REPLACE('hello','e','E') FROM dual
三、单行日期函数
1、sysdate:取服务器当前时间
SELECT SYSDATE FROM dual
2、add_months:在指定的日期上加减多少个月
格式：add_months(d1,n)
SELECT hiredate,add_months(hiredate,6) FROM emp
SELECT add_months('31-8月-10',1) FROM dual
3、months_between:表示两个日期之间间隔多少个月。
SELECT months_between(SYSDATE,hiredate) FROM emp
SELECT to_date(to_char(SYSDATE,'yyyy-mm-dd'),'yyyy-mm-dd')-hiredate FROM emp
4、last_day:取给定日期所在月份的最后一天的日期
格式：last_day(d1)
SELECT last_day(SYSDATE) FROM dual
SELECT last_day('01-2月-01') FROM dual
5、next_day:取给定日期下一个指定星期后的日期。
SELECT next_day(SYSDATE,'星期四') FROM dual
SELECT next_day('01-2月-01','星期四') FROM dual
SELECT * FROM nls_session_parameters//查看会话中参数。
6、extract:取给定日期中的年或者月或者日
格式：extract(YEAR|MONTH|DAY FROM d1)
SELECT extract(DAY FROM SYSDATE) FROM dual
SELECT extract(DAY FROM to_date('2001-01-01','yyyy-mm-dd')) FROM dual
7、round:对日期进行四舍五入。
格式:round(d1,['fmt'])
SELECT round(SYSDATE) FROM dual//当第二个参数不写时，默认四舍五入到日，看时间是否超过中午12：00.
SELECT round(SYSDATE,'MM') FROM dual
SELECT round(SYSDATE,'YY') FROM dual
8、trunc:对日期进行截断
格式:trunc(d1,['fmt'])
练习：
1、计算2000年1月1日到现在有多少月，多少周（四舍五入）。
SELECT round(months_between(SYSDATE,'01-1月-00')),round((SYSDATE - to_date('01-01-00','dd-mm-rr'))/7) FROM dual
2、将员工的参加工作日期按如下格式显示：月份/年份
SELECT to_char(hiredate,'MM"月"/YY"年"') FROM emp
四、转换函数
1、to_char:把日期或者数值型数据转换成字符型
格式：to_char(d1|n1,['fmt'])
当参数1为日期型数据时：fmt可以为
CC:表示世纪
RR:表示两位年份
YY:表示两位年份
YYYY:表示4位年份
MM:表示两位月份
DD:表示两位日期
HH12：表示12进制的小时
HH24:表示24进制的小时
MI：表示分钟
SS：表示秒
当参数1为数值型数据时:fmt可以为:
0：表示本身
9：表示任意一个数值
,:表示千位分隔符
.：表示小树点
$:表示美元符号
L:表示本地货币符号
SELECT to_char(SYSDATE) FROM dual
SELECT to_char(SYSDATE,'DD/MM') FROM dual
SELECT to_char(SYSDATE,'YYYY-MM-DD HH:MI:SS') FROM dual
SELECT to_char(1234567.789,'L999,999,999,999.999') FROM dual
SELECT * FROM nls_session_parameters
ALTER SESSION SET NLS_CURRENCY='RMB' //修改会话的本地货币符号
2、to_date:把字符转换成日期
格式：to_date(s1,'fmt')
fmt参见上面to_char函数说明
SELECT to_date('20010101','rrmmddmi') FROM dual
SELECT to_date('20010101','rr') FROM dual
注意：要转换的字符串一定要和格式码相匹配
把字符串“2000年/1月-1日”转换成日期
SELECT to_date('2000年/1月-1日','YYYY"年"/MM"月"-DD"日"') FROM dual
3、to_number:把字符转换成数字
格式：to_number(s1,'fmt')
fmt参见上面to_char函数说明
SELECT to_number('RMB123,456,576.89','L999,999,999.99') FROM dual
SELECT to_number('RMB123,456,5761.89','L999,999,99999.99') FROM dual
注意：当格式码中的格式比要转换的长或短时，都转换不成功。
五、通用函数
1、空值处理函数
nvl(参数1,参数2)：如果参数1不为null，则函数结果为参数1，否则结果为参数2
SELECT nvl('a','b') FROM dual
SELECT nvl(NULL,'b') FROM dual
//练习：求每个员工的年收入,年收入等于12*(工资+津贴)
SELECT 12*(sal+nvl(comm,0)) FROM emp
//查询每个员工的经理编号，对于没有经理的员工显示"无经理"
SELECT nvl(mgr,'无经理') FROM emp X
注意：nvl函数要求参数1和参数2必须是相同数据类型的。
SELECT nvl(to_char(mgr),'无经理') FROM emp
nvl2(参数1，参数2,参数3）：判断参数1是否为null，如果不是null,返回参数2，否则参数3
//练习：求每个员工的年收入,年收入等于12*(工资+津贴)
SELECT 12*(sal+nvl2(comm,comm,0)) FROM emp
//查询每个员工的经理编号，对于没有经理的员工显示"无经理"
SELECT nvl2(mgr,to_char(mgr),'无经理') FROM emp
注意：nvl2函数要求参数2和参数3必须是相同数据类型的。
nullif(参数1,参数2)：判断参数1和参数2是否相等，如果相等，返回null,否则返回参数1
SELECT nullif('b','b') FROM dual
COALESCE(参数1,参数2,参数3,.....):返回第一个不为null的参数
SELECT COALESCE(NULL,NULL,1,2) FROM dual
case表达式
语法结构
CASE 条件表达式
WHEN 值1 THEN 结果1
WHEN 值2 THEN 结果2
...
[ELSE 结果n]
END
//查询员工的姓名，津贴，300元显示低,500元显示还行，1400元显示高，其他的显示工作不认真，继续努力。
SELECT ename,comm, CASE comm
WHEN 300 THEN '低'
WHEN 500 THEN '还行'
WHEN 1400 THEN '高'
ELSE '工作不认真，继续努力。'
END
FROM emp
decode(条件表达式,值1,结果1,值2,结果2,.....值n,结果n，结果n+1) :
判断条件表达式1和哪个值相匹配，则返回对应的结果，如果没有匹配的返回结果n+1
//查询员工的姓名，津贴，300元显示低,500元显示还行，1400元显示高，其他的显示工作不认真，继续努力。
SELECT ename,comm,decode(comm,300,'低',500 ,'还行',1400,'高','工作不认真，继续努力。') FROM emp
//练习，计算每个员工的个人所得税。
计算方式：
工资在0-1000元以下的不交税
在1000-2000的交10%
2000-3000的交15%
3000以上的交20%
trunc(0-1) 0
1.-2 1
2.-3 2
SELECT ename,sal,sal*decode(trunc(sal/1000),0,0,1,0.1,2,0.15,0.2) FROM emp
