-------------------------------------单行函数--------------------------------------------
1.字符函数
select lower('hellO WORld') 转小写,upper('hellO WORld') 转大写,initcap('hello world') 首字母大写 from dual;
substr(a,b) 从a中,第b位开始取,取右边所有的字符
select substr('Hello World',4) from dual;
substr(a,b,c) 从a中,第b位开始取,取c位
select substr('Hello World',4,4) from dual;
length 字符数 lengthb 字节数
select length('Hello World') 字符数, lengthb('Hello World') 字节数 from dual;
select length('北京') 字符数, lengthb('北京') 字节数 from dual;
如果是字符,则结果一样,如果是汉字,则字节数的值是字符数的2倍
instr 在母串中查找子串,找到返回下标(从1开始),否则返回0
select instr('Hello World','o') from dual;
lpad 左填充 rpad 右填充
select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右 from dual;
trim 去掉前后指定的字符
select trim('H' from 'Hello WorldH') from dual;
replace(a,b,c)找到a中的字符b,用c替换该字符b
select replace('Hello World','l','*') from dual;
2.数字函数
select ROUND(45.926, 2) 一,ROUND(45.926, 1) 二, ROUND(45.926, 0) 三, ROUND(45.926, -1) 四, ROUND(45.926, -2) 五
from dual;
一 二 三 四 五
---------- ---------- ---------- ---------- ----------
45.93 45.9 46 50 0
select TRUNC(45.926, 2) 一,TRUNC(45.926, 1) 二, TRUNC(45.926, 0) 三, TRUNC(45.926, -1) 四, TRUNC(45.926, -2) 五
from dual
一 二 三 四 五
---------- ---------- ---------- ---------- ----------
45.92 45.9 45 40 0
3.日期函数
sysdate返回的是系统的时间。
systimestamp也是当前系统的时间戳,包含毫秒数,其加减一个数字后,仍为日期格式。
current_timestamp 与时区设置有关,返回的秒是系统的,返回的日期和时间是根据时区转换过的。
current_date是对current_timestamp准确到秒的四舍五入。
to_char(a,b)将a的日期格式转换成b的格式字符串格式
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss*ff') from dual;
加减一个数字后,仍为日期格式,允许日期 - 日期 ,不允许日期 + 日期
select (sysdate-1) 昨天, sysdate 今天, (sysdate+1) 明天 from dual;
员工的工龄
select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,(sysdate-hiredate)/30 月,
(sysdate-hiredate)/365 年 from emp;
last_day(sysdate)返回当前这个月的最后一天
select last_day(sysdate) from dual;
MONTHS_BETWEEN 计算工龄
select (sysdate-hiredate)/30 一,MONTHS_BETWEEN(sysdate,hiredate) 二 from emp;
add_months(a,b)在a的时间上,加上b个月后返回的时间。
例:114月后
select add_months(sysdate,114) from dual;
从今天开始算(如果今天是星期一),求下一个星期一,则为一个礼拜后
select next_day(sysdate,'星期一') from dual;
从今天开始算(如果今天是星期一),下一个星期二,则返回的值为明天
select next_day(sysdate,'星期二') from dual;
对日期进行四舍五入
select round(sysdate,'month') 一,round(sysdate,'year') 二 from dual;
隐式转换的前提: 被转换对象是可以转换的
显式转换
2012-10-22 16:24:13 今天是星期一,把需要自己添加的字符串用“”括起来
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;
查询员工的薪水: 货币符号,两位小数,千位符
select sal, to_char(sal,'L9,999.99') from emp;
4.通用函数
nvl2(a,b,c) 当a=null时,返回c,否则返回b
select sal*12+nvl2(comm,comm,0) from emp;
nullif(a,b) 当a=b时,返回null,否则返回a
select nullif('abc','abc') from dual;
select nullif('abc','abdc') from dual;
coalesce从左至右找到第一个不为null的值
select comm,sal,COALESCE(comm,sal) from emp;
sql语句中使用if-then-else逻辑有2种方法(例:根据职位涨工资 总裁1000 经理800 其他400)
方法一:
case表达式:sql99的语法,类似Basic,较繁琐
select ename,job,sal 涨前薪水, case job when 'PRESIDENT' then sal+1000
when 'MANAGER' then sal+800
else sal+400
end 涨后薪水 from emp;
方法二:
decode函数:Oracle自己的语法,类似Java,使用简单,数据库无需移植则尽量使用此函数
select ename,job,sal 涨前薪水,decode(job,'PRESIDENT',sal+1000,
'MANAGER', sal+800,
sal+400) 涨后薪水 from emp;