oracle的常用l函数,Oracle常用函数

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。