oracle常用函式介紹

Oracle SQL提供了用於執行特定操作的專用函式，這些函式大大增強了SQL語言的功能。函式可以接受零個或者多個輸入引數，並返回一個輸出結果。

Oracle資料庫中主要使用兩種型別的函式：

單行函式：對每一個函式應用在表的記錄中時，只能輸入一行結果，返回一個結果，比如：MOD(x,y)返回x除以y的餘數（x和y可以是兩個整數，也可以是表中的整數列）。常用的單行函式有：

• 字元函式：對字串操作。
• 數字函式：對數字進行計算，返回一個數字。
• 轉換函式：可以將一種資料型別轉換為另外一種資料型別。
• 日期函式：對日期和時間進行處理。
• 聚合函式：聚合函式同時可以對多行資料進行操作，並返回一個結果。比如SUM(x)返回結果集中x列的總合。

2.字元函式

字元函式接受字元引數，這些引數可以是表中的列，也可以是一個字串表示式。下表列出了常用的字元函式。

函式	說明
ASCII(x)	返回字元x的ASCII碼。
CONCAT(x,y)	連線字串x和y。
INSTR(x, str. Start, n)	在x中查詢str，可以指定從start開始，也可以指定從第n次開始。
LENGTH(x)	返回x的長度。
LOWER(x)	x轉換為小寫。
UPPER(x)	x轉換為大寫。
LTRIM(x,trim_str)	把x的左邊截去trim_str字串，預設截去空格。
RTRIM(x,trim_str)	把x的右邊截去trim_str字串，預設截去空格。
TRIM(trim_str FROM x)	把x的兩邊截去trim_str字串，預設截去空格。
REPLACE(x,old,new)	在x中查詢old，並替換為new。
SUBSTR(x, start ,length)	返回x的字串，從staart處開始，擷取length個字元，預設length，預設到結尾。

表1 字元函式

示例	示例結果
SELECT ASCII(‘a’) FROM DUAL	97
SELECT CONCAT(‘Hello’, ’ world’) FROM DUAL	Hello world
SELECT INSTR(‘Hello world’，‘or’) FROM DUAL	8
SELECT LENGTH(‘Hello’) FROM DUAL	5
SELECT LOWER(‘hElLO’) FROM DUAL;	hello
SELECT UPPER(‘hello’) FROM DUAL	HELLO
SELECT LTRIM('===HELLO===', '=') FROM DUAL	HELLO===
SELECT ‘==’
SELECT RTRIM('===HELLO===', '=') FROM DUAL	===HELLO
SELECT '='||TRIM(' HELLO ')||'=' FROM DUAL	=HELLO=
SELECT TRIM('=' FROM '===HELLO===') FROM DUAL	HELLO
SELECT REPLACE('ABCDE','CD','AAA') FROM DUAL	ABAAAE
SELECT SUBSTR('ABCDE',2) FROM DUAL	BCDE
SELECT SUBSTR('ABCDE',2,3) FROM DUAL	BCD

表2 字元函式示例

3.數字函式

數字函式接受數字引數，引數可以來自表中的一列，也可以是一個數字表示式。

函式	說明	示例
ABS(x)	x絕對值	ABS(-3)=3
ACOS(x)	x的反餘弦	ACOS(1)=0
COS(x)	餘弦	COS(1)=1.57079633
CEIL(x)	大於或等於x的最小整數	CEIL(5.4)=6
FLOOR(x)	小於或等於x的最大整數	FLOOR(5.8)=5
LOG(x,y)	x為底y的對數	LOG(2,4)=2
MOD(x,y)	x除以y的餘數	MOD(8,3)=2
POWER(x,y)	x的y次冪	POWER(2,3)=8
ROUND(x,y)	x在第y小數位四捨五入	ROUND(3.456,2)=3.46
SQRT(x)	x的平方根	SQRT(4)=2
TRUNC(x,y)	x在第y位截斷	TRUNC(3.456,2)=3.45

表3 數字函式

說明：

1.ROUND(X[,Y])，四捨五入。

在預設y時，預設y=0；比如：ROUND(3.56)=4。

y是正整數，就是四捨五入到小數點後y位。ROUND(5.654,2)=5.65。

y是負整數，四捨五入到小數點左邊|y|位。ROUND(351.654,-2)=400。

2.TRUNC(x[,y])，直接擷取，不四捨五入。

在預設y時，預設y=0；比如：TRUNC (3.56)=3。

y是正整數，就是四捨五入到小數點後y位。TRUNC (5.654,2)=5.65。

y是負整數，四捨五入到小數點左邊|y|位。TRUNC (351.654,-2)=300。

4.日期函式

日期函式對日期進行運算。常用的日期函式有：

1、 ADD_MONTHS(d,n)，在某一個日期d上，加上指定的月數n，返回計算後的新日期。d表示日期，n表示要加的月數。

例：SELECT SYSDATE,add_months(SYSDATE,5) FROM dual;

2、LAST_DAY(d)，返回指定日期當月的最後一天。
例：SELECT SYSDATE,last_day(SYSDATE) FROM dual;

3、ROUND(d[,fmt])，返回一個以fmt為格式的四捨五入日期值，d是日期，fmt是格式模型。預設fmt為DDD，即月中的某一天。

• 如果fmt為“YEAR”則舍入到某年的1月1日，即前半年捨去，後半年作為下一年。
• 如果fmt為“MONTH”則舍入到某月的1日，即前月捨去，後半月作為下一月。
• 預設為“DDD”，即月中的某一天，最靠近的天，前半天捨去，後半天作為第二天。
• 如果fmt為“DAY”則舍入到最近的周的週日，即上半周捨去，下半周作為下一週週日。

例：

SELECT SYSDATE,ROUND(SYSDATE),ROUND(SYSDATE,'day'),
ROUND(SYSDATE,'month'),ROUND(SYSDATE,'year') FROM dual;

與ROUND對應的函式時TRUNC(d[,fmt])對日期的操作，TRUNC與ROUND非常相似，只是不對日期進行舍入，直接擷取到對應格式的第一天。

4、EXTRACT(fmt FROM d)，提取日期中的特定部分。

fmt為：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中YEAR、MONTH、DAY可以為DATE型別匹配，也可以與TIMESTAMP型別匹配；但是HOUR、MINUTE、SECOND必須與TIMESTAMP型別匹配。

HOUR匹配的結果中沒有加上時區，因此在中國執行的結果小8小時。
例：

SELECT SYSDATE "date",

       EXTRACT(YEAR FROM SYSDATE)"year",

       EXTRACT(MONTH FROM SYSDATE)"month",

       EXTRACT(DAY FROM SYSDATE)"day",

       EXTRACT(HOUR FROM SYSTIMESTAMP)"hour",

       EXTRACT(MINUTE FROM SYSTIMESTAMP)"minute",

       EXTRACT(SECOND FROM SYSTIMESTAMP)"second"

FROM dual;

5.轉換函式

轉換函式將值從一種資料型別轉換為另外一種資料型別。常用的轉換函式有：
1、TO_CHAR(d|n[,fmt])
把日期和數字轉換為制定格式的字串。fmt是格式化字串。

程式碼演示：TO_CHAR對日期的處理

SQL> SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS') "date"  FROM DUAL;

程式碼解析：

① 在格式化字串中，使用雙引號對非格式化字元進行引用。

針對數字的格式化，格式化字元有：

引數	示例	說明
9	999	指定位置處顯示數字
.	9.9	指定位置返回小數點
,	99,99	指定位置返回一個逗號
$	999	數字開頭返回一個美元符號
EEEE	9.99EEEE	科學計數法表示
L	L999	數字前加一個本地貨幣符號
PR	999PR	如果數字式負數則用尖括號進行表示

程式碼演示：TO_CHAR對數字的處理

SELECT TO_CHAR(-123123.45,'L9.9EEEEPR')"date" FROM dual;

2、TO_DATE(X,[,fmt])

把一個字串以fmt格式轉換為一個日期型別。

3、TO_NUMBER(X,[,fmt])

把一個字串以fmt格式轉換為一個數字。fmt格式字元參考表3。

程式碼演示：TO_NUM函式

SELECT TO_NUMBER('-$12,345.67','$99,999.99')"num" FROM dual;

6.其他單行函式

1、NVL(X,VALUE)

如果x為空，返回value，否則返回x。

案例7：對工資是2000元以下的員工，如果沒有發獎金，每人獎金100元。

程式碼演示：NVL函式

SQL> SELECT ENAME,JOB,SAL,NVL(COMM,100) FROM EMP WHERE SAL<2000;

-------------------------------------------------------------------

ENAME    JOB    SAL    NVL(COMM,100) 

SMITH    CLERK    800    100 

ALLEN    SALESMAN    1600    300 

WARD    SALESMAN    1250    500 

MARTIN    SALESMAN    1250    1400 

TURNER    SALESMAN    1500    50 

ADAMS    CLERK    1100    100 

JAMES    CLERK    950    100 

-------------------------------------------------------------------

7 rows selected

2、NVL2(x,value1,value2)

如果x非空，返回value1，否則返回value2。

案例8：對EMP表中工資為2000元以下的員工，如果沒有獎金，則獎金為200元，如果有獎金，則在原來的獎金基礎上加100元。

程式碼演示：NVL2函式

SQL> SELECT ENAME,JOB,SAL,NVL2(COMM,comm+100,200) "comm"

2   FROM EMP WHERE SAL<2000;

-------------------------------------------------------------------

ENAME    JOB    SAL    comm 

SMITH    CLERK    800    200 

ALLEN    SALESMAN    1600    400 

WARD    SALESMAN    1250    600 

MARTIN    SALESMAN    1250    1500 

TURNER    SALESMAN    1500    150 

ADAMS    CLERK    1100    200 

JAMES    CLERK    950    200 

MILLER    CLERK    1300    200

-------------------------------------------------------------------------------------------------------

8 rows selected

7.聚合函式

聚合函式同時對一組資料進行操作，返回一行結果，比如計算一組資料的總和，平均值等。

名稱	作用	語法
AVG	平均值	AVG（表示式）
SUM	求和	SUM(表示式)
MIN、MAX	最小值、最大值	MIN(表示式)、MAX(表示式)
COUNT	資料統計	COUNT（表示式）

表 5 聚合函式

案例9：求本月所有員工的基本工資總和。

程式碼演示：SUM函式

SQL> select sum(sal) from emp;

SUM(SAL)
----------------
29025

案例10：求不同部門的平均工資。

程式碼演示：AVG函式下的分組查詢

SQL> SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO;

DEPTNO   AVG(SAL)

---------      ----------

    30    1566.66666

    20    2175

    10    2916.66666