Oracle常用函数

Lower	转换小写
upper	转换大写
substr	取子串
length	取长度
trim	去空格
to_date	将字符串转换成日期
to_char	将日期或数字转换成字符串
to_number	将字符串转换成数字
nvl	可以将null转换成一个具体值
case	分支语句
decode	同case
round	四舍五入

lower

● 查询员工，将员工姓名全部转换成小写

select lower(ename) from emp;

upper

● 查询job为manager的员工

select * from emp where job=upper('manager');

substr

● 查询姓名以M开头所有的员工

select * from emp where substr(ename, 1,1)='M';

length

● 取得员工姓名的长度

select length(ename) from emp;

trim

trim会去首尾空格，不会去除中间的空格。

● 取得工作岗位为MANAGER的所有员工

select * from emp where job=trim('MANAGER    ');

to_date

● 查询1981-02-20入职的员工(第一种方法，与数据库的格式匹配上)

select * from emp where HIREDATE='20-2月 -81';

● 查询1981-02-20入职的员工(第二种方法，将字符串转换成date类型)

select * from emp where hiredate=to_date('1981-02-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

to_date可以将字符串转换成日期，具体格式to_date(字符串，匹配格式)。

日期格式的说明

控制符	说明
YYYY	表示年
MM	表示月
DD	表示日
HH12,HH24	表示12小时制，表示24小时制
MI	表示分
SS	表示秒

to_char

● 查询1981-02-20以后入职的员工，将入职日期格式化成yyyy-mm-dd hh:mm:ss

select empno, ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss') from emp where hiredate>to_date('1981-02-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

● 查询员工薪水加入千分位

select empno, ename, to_char(sal, '$999,999') from emp;

● 查询员工薪水加入千分位和保留两位小数

select empno, ename, to_char(sal, '$999,999.00') from emp;

将数字转换成字符串，格式

控制符	说明
9	表示一位数字
0	位数不够可以补零
$	美元符
L	本地货币符号
.	显示小数
,	显示千分位

to_number

将字符串转换成数值

select * from emp where sal>to_number('1,500', '999,999');

nvl

● 取得员工的全部薪水，薪水+津贴

select empno, ename, sal, comm, sal+comm from emp;

以上结果不正确，主要原因是津贴(comm)字段为null，所以无法计算，所以正确的做法是将津贴先转换成0，再计算。可以使用Oracle提供的nvl，该函数的语法格式为：nvl(表达式1，表达式2)，表达式1：指的是字段名称;表达式2：指的是将该字段的null转换成的值。

● 采用nvl函数，取得员工的全部薪水，薪水+津贴

select empno, ename, sal, comm, sal+nvl(comm,0) from emp;

以上结果是正确的，在做表设计的时候，关于数值字段最好不允许为null，可以设置缺省值。

case … when … then …end

● 如果job为MANAGERG薪水上涨10%,如果job为SALESMAN工资上涨50%

select empno, ename, job, sal, (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 end) as newsal from emp;

decode

同case …when …then … end

● 如果job为MANAGERG薪水上涨10%,如果job为SALESMAN工资上涨50%

select empno, ename, job, sal, decode(job, 'MANAGER', SAL*1.1, 'SALESMAN', sal*1.5) as newsal from emp;

round

四舍五入

select round(1234567.4567, 2) from dual;

Dual是oracle提供的，主要为了方便使用，因为select的时候需要用from。