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。