Oracle函數

                                         函數

Oracle的函數分單行函數與多行函數

    單行函數：隻影響一行資料

    多行函數：影響多行資料

字元函數

  大小寫轉換：

 LOWER

 UPPER

 INITCAP

  字元處理函數：

 CONCAT

 SUBSTR

 LENGTH

 INSTR

 LPAD | RPAD

 TRIM

 REPLACE

select empno,lower(ename) from emp;

select empno,upper(ename) from emp;

select empno,initcap(ename) from emp;

select empno||ename from emp;

select concat(empno,ename) from emp;

select concat(empno,'aa') from emp;

select ename,substr(ename,1,3) from emp(1代表從第幾個字母開始，3代表截取的長度)

select ename,length(ename) from emp;(傳回字元串的長度)

select ename,instr('string',r) from emp;(這個字元串中的r第一次出現的位置，位置從一開始，java中從0開始)

select empno,lpad(empno,10,'*') from emp;(定義字元長度10位，不足的地方補上*号，rpad是又面補)

select ename,trim(ename) from emp;(去掉字元串中的空格)

select ename,length(trim(ename)) from emp;(去掉字元串中的空格，得到字元串的真正長度ltrim隻去左邊的，rtrim隻去右邊的，注意中間的空格去不掉)

select replace(ename,'A','a') from emp;(把大寫A替換成小寫a)

數字函數

  ROUND

  TRUNC

  MOD

select sal,rround(sal,2) from emp;(四舍五入到指定位2代表保留幾位)

select sal,round(sal) from emp;

select sal,trunc(sal,2) from emp;(小數點後截位2代表截的位數)

select sal,mod(sal,100) from emp;(取餘數計算，100代表對多少取餘數)

轉換函數（字元串，數字，日期轉換）

  數字類型轉換

    隐含的數字類型轉換

    顯式的數字類型轉換（建議盡量使用）

 to_char

 to_number

 to_date

日期類型轉成字元格式

select sysdate from dual;

select to_char(sysdate,'yyyy-mm-dd-dd HH:Mi:ss') from dual;

select to_char(sysdate,'fmyyyy-MM-dd') from dual;

select to_char(sysdate,'yyyy-MM-dd')+1 from dual;

select to_char(sysdate,'year month dy day') from dual;

to_char(sysdate,'dy') from dual;(dy表示3個字母縮寫)

日期格式基礎：

時間格式作為日期的一部分

HH24:MI:SS:AM     15:45:32 PM

用雙引号括起來以加字元串

DD"of"MONTH       12 of OCTOBER

數值拼寫

ddspth            fourteenth

數值轉換

9  代表一個數字

0  強制為0

$  設定美元符号

L  使用目前字元集貨币符号

.   小數點

，千位分隔符

select to_char(sal,'999,999,999,999，999') from emp;

select to_char(sal,'000,000,000,000') from emp;

select to_char($sal,999,999,999) from emp;

seelct to_char(Lsal,999,999,999) from emp;

select sal,to_number(to_char(sal,'999999.999')) from emp;

select to_date('2008-01-01','yyyy-MM-dd')+10 from dual;

日期類函數

  MONTHS_BETWEEN 兩日期相差多少月

  ADD_MONTHS 加月份到日期

  NEXT_DAY 指定日期的下一天

  LAST_DAY 一個月中的最後一天

  ROUND Round日期

  TRUNC Truncate日期

select empno,hiredate,sysdat-hiredate from emp;()

select empno,hiredate,months_between(sysdate,hiredate)

  from emp;()

select add_months(sysdate,2) from dual;(加兩個月)

select add_months(sysdate,-2) from dual;(減兩個月)

select next_day(sysdate,2) from dual;(2代表)

select last_day(sysdate) from dual;()

select round(sysdate) from dual;(日期的四舍五入)

select trunc(sysdate) from dual;()

select trunc(sysdate,'01-1月-98') from dual;

select round(sysdate,'MONTH') from dual;

select trunc(sysdate,'year') from dual;

其他函數

将空值轉換為實際值

NVL

NVL2

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

select empno,ename,job,sal,comm,nvl2(comm,'sal+comm','sal')

  from employees where department_id in(50,80);

NULLIF

COALESCE 傳回一個非空的表達式

CASE

select empno,ename,sal,case

  when sal<1000 then '小于1000'

  when sal>=1000 and sal<2000 then '介于1000到2000之間'

  when sal>=2000 and sal,3000 then '介于2000到3000之間'

  else '大于3000'

end

  from emp;

DECODE 類似于case或if(相等的情況)

select empno,ename,sal,decode(sal,1000,'大于1000') from emp;

select empno,ename,deptno,decode(deptno,20,'技術部',30,'财務  部',10,'辦公室') from emp;

函數嵌套

select empno,rpad(lpad(empno,6,'GB'),9,'_01') from emp;

分組函數

AVG 平均值

COUNT 統計表裡的記錄

MAX 最大值

MIN 最小值

SUM 總和

select max(sal),min(sal),avg(sal),sum(sal) from emp;

select max(sal),min(sal),avg(sal),sum(sal) from emp

  where deptno=10;

select count(*) from emp;

seelct count(empno) from emp;

count排除空值，在進行sum運算時，将空值去掉

insert into test(select * from test);(把表的資料插入另個表)

select avg(sal) from emp;

select avg(nvl(sal,0)) from emp;

GROUP BY子句

select deptno,max(sal),min(sal),sum(sal),avg(sal)

  from emp group by deptno

    order by deptno desc;

select emptno,ename,job,deptno from emp

  group by job,empno,ename,deptno;

select deptno,avg(sal) from emp

  group by deptno

  having avg(sal)>2000;

  having avg(sal);

  group by deptno

  having avg(sal)>(select avg(sal) from emp);

select name

case subject

  when '數學' then score else 0 end 數學,

  when '國文' then score else 0 end 國文,

  when '英語' then score else 0 end 英語

from stu

  group by name,subject,score;

select name 姓名,

sum(case subject

  when '數學' then score else 0 end) 數學,

sum(case subject

  when '國文' then score else 0 end) 國文,

  when '英語' then score else 0 end) 英語

end)

  group by name;

HAVING子句： 限定組的結果

嵌套組函數

select max(avg(salary)) from employees

  group by departement_id;(組函數最多嵌套兩層)