一、oracle函式概述
提供一系列用於執行特定操作的函式 SQL 函式帶有一個或多個引數並返回一個值 以下是SQL函式的分類：
二、單行函式
單行函式對於從表中查詢的每一行只返回一個值 可以出現在 SELECT 子句中和 WHERE 子句中 單行函式可以大致劃分為：
日期函式
數字函式
字元函式
轉換函式
其他函式
2.
1 日期函式：可以進行算術運
算
SYSDATE
|
返回系統日期
|
select sysdate from dual;
|
ADD_MONTHS(<d>,<i>)
|
返回日期d 加上i個月後的新日期(i正可負)。
|
Add_Months(sysdate,2)
Add_Months(sysdate,-2) |
LAST_DAY(<d>)
|
返回日期d所在的月的最後一天。
|
Select Last_Day(sysdate) from dual;
|
MONTHS_BETWEEN(<d1>,<d2>)
|
返回日期d1比d2大多少月數。
|
Months_Between(’19-1月-2008’, ’19-1月-2009’)
|
NEW_TIME(<d>,<tz1>,<tz2>)
|
將時區tz1的時間d，轉換為時區tz2裡的時間。
|
SELECT SYSDATE,NEW_TIME(SYSDATE,'CDT','PDT') FROM DUAL;
|
NEXT_DAY(<d>,<dow>)
|
返回日期d後的第一個dow。(dow：day of week)
|
Select Next_Day(sysdate,’星期一’) from dual;
|
EXTRACT
|
用於提取日期時間型別的特定部分
|
Select extract(month from sysdate) from dual;
|
ROUND
|
四捨五入
|
Round(sysdate,’Year’)精確到年
|
TRUNC
|
截斷日期
|
Trunc(sysdate,’Month’)截斷月份
|
2.
2 字元函式
LPAD(<c1>,<i>[,<c2>])
|
在字串c1的左邊新增字串c2直到c1字串的長度等於i。
|
SELECT LPAD('Hello!',8,'*') leftpad,RPAD('Hello!',8,'*') rightpad FROM DUAL;
|
RPAD(<c1>,<i>[,<c2>])
|
在字串c1的右邊新增字串c2直到c1字串的長度等於i。
|
LOWER(<c1>)
|
把字串c1轉換為小寫。
|
SELECT LOWER(ename) 小寫,UPPER(ename) 大寫, INITCAP(ename) 首字元 FROM EMP;
|
UPPER(<c1>)
|
把字串c1轉換為大寫。
|
INITCAP(<c1>)
|
把c1字串的每一個單詞的第一個字母轉換成大寫字母。
|
LENGTH(<c1>)
|
返回字串c1的長度。
|
SELECT LENGTH('How are you') FROM DUAL;
|
SUBSTR(<c1>,<i>[,<j>])
|
返回字串c1中從第i個位置開始的j個字元（向右）。如果省略j，則返回c1中從第i個位置開始的所有字元。如果j為負，則返回字串c1中從第i個位置開始的j個字元（向左）。
|
SELECT SUBSTR('Hello,World',1,5) FROM DUAL; 1不是下標
|
INSTR(<c1>,<c2>[,<i>[,<j>]])
|
在c1中從位置i開始查詢c2在c1中出第j次的位置，i可以為負(此時，從c1的尾部開始)。
|
SELECT INSTR('Mississippi','i',3,3) FROM DUAL; 返回結果11。
SELECT INSTR('Mississippi','i',-2,3) FROM DUAL; 返回結果2。 |
LTRIM(<c1>,<c2>)
|
從c1前面開始去掉出現在c2的中任何前導字符集。
|
SELECT LTRIM('Mississippi','Mis') FROM DUAL; 返回結果'ppi'。
SELECT RTRIM('Mississippi','ip') FROM DUAL; 返回結果'Mississ' |
RTRIM(<c1>,<c2>)
|
從c1後面開始去掉出現在c2的中任何前導字符集。
預設去掉空格
Trim()，去掉兩端空格 |
Concat (<c1>, <c2>)
|
用於連線c1，c2兩個表示式
|
Select concat ('Hello',' world') from dual;
|
Replace(<c1>,<c2>,[<c3>] )
|
用c3替換c1中所有c2字元，如果沒有c3，表示去掉c2字元
|
Select replace('jack and jue' ,'j','bl') from dual;
|
CHR和ASCII
|
字元與ASCII值之間的轉換
|
|
DECODE(<x>,<m1>,<r1>[,<m2>,<r2…>])
|
功能類似於一系列的if…then…else語句。
如果deptno為10，用學術部替代
如果為20，用市場部替代
如果為30，用就業部替代 |
select deptno,decode(deptno,10,'學術部',20,'市場部',30,'就業部') from emp;
|
2.3 數字
函
數
函式
|
輸入
|
輸出
|
Abs(n)
|
Select abs(-15) from dual;
|
15
|
Ceil(n)
|
Select ceil(44.778) from dual;
|
45
|
Cos(n)
|
Select cos(180) from dual;
|
-.5984601
|
Cosh(n)
|
Select cosh(0) from dual;
|
1
|
Floor(n)
|
Select floor(100.2) from dual;
|
100
|
Power(m,n)
|
Select power(4,2) from dual;
|
16
|
Mod(m,n)
|
Select mod(10,3) from dual;
|
1
|
Round(m,n)
|
Select round(100.256,2) from dual;
|
100.26
|
Trunc(m,n)
|
Select trunc(100.256,2) from dual;
|
100.25
|
Sqrt(n)
|
Select sqrt(4) from dual;
|
2
|
Sign(n)
|
Select sign(-30) from dual;
|
-1
|
2.4 轉換函式
TO_CHAR(<x>[,<fmt>[,<nlsparm>]])
|
將x轉換成字串。
|
Select to_char(sysdate,’yyyy-mm-dd’) from dual;
Select to_char(123456,’$999,999’) from dual; |
TO_NUMBER(<c>[,<fmt>[,<nlsparm>]])
|
將字串c轉換成數字。
|
Select to_number(‘123’) from dual;
|
TO_DATE(<c>[,<fmt>[,<nlsparm>]])
|
將字串c轉換成日期。
|
Select to_date(‘2009-7-8’,’yyyy-mm-dd’) from dual;
|
SELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS') FROM dual;
2.4 其他函式
NVL(x1,x2)
|
如果x1為空返回x2，否則返回x1。
|
SELECT NVL(comm,0) FROM EMP;
|
NVL2(x1,x2,x3)
|
如果x1為空返回x3，否則返回x2
|
select nvl2(comm,2000,0) from emp;
|
NULLIF(x1,x2)
|
如果x1=x2返回空，否則返回x1
|
select nullif(comm,300) from emp;
|
三、分組函式
分組函式基於一組行來
返回結果 為每一組行返回一個值
3. Group by子句，having篩選
□ GROUP BY子句
1）用於將資訊劃分為更小的組
2）每一組行返回針對該組的單個結果
□ HAVING子句
1）用於指定 GROUP BY 子句檢索行的條件
SELECT p_category, MAX(itemrate) FROM itemfile GROUP BY p_category;
SELECT p_category, MAX(itemrate) FROM itemfile
GROUP BY p_category
HAVING p_category NOT IN ('accessories');
求每個部門中每個崗位的平均薪水大於200的員工資訊
select deptno,empjob,avg(sal) from emp group by deptno,empjob having avg(sal) > 20000
四、分析函式
用於計算一個行在一組有序行中的排位排位從1開始。如計算聚集的累積排名、移動平均數和報表聚合值。
ROW_NUMBER
|
返回連續的排位，不論值是否相等
|
RANK
|
具有相等值的行排位相同，序號隨後跳躍
|
DENSE_RANK
|
具有相等值的行排位相同，序號是連續的
|
select ename 姓名,sal 薪水,
row_number() over(order by sal) row_number,
rank() over(order by sal) rank,
DENSE_RANK() over(order by sal) dense_rank from emp;
查詢公司所有員工的工資排名情況
select e.ename ,e.sal,d.dname, e.deptno,
dense_rank() over(order by e.sal desc)
from emp e ,dept d where e.deptno = d.deptno
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/69940641/viewspace-2929021/，如需轉載，請註明出處，否則將追究法律責任。
