歸納整理Oracle中常用函數

這篇文章為大家帶來了關於Oracle的相關知識，其中詳細講解了Oracle中常用函數，透過範例程式碼介紹，希望對大家的學習或工作具有一定的參考借鑒價值，希望對大家有幫助。

推薦教學：《Oracle影片教學》

oracle資料庫中主要使用兩種類型的函數：

1.  單行函數：操作一行數據，傳回一個結果

• #常用的單行函數有：
• 字串函數：對字串運算。
• 數字函數：對數字進行計算，傳回一個數字。
• 日期函數：對日期和時間進行處理。
• 轉換函數：可以將一種資料型別轉換為另外一種資料型別。

2.  聚合函數(多行函數、分組函數、群組函數)：操作多行數據，並傳回一個結果。例如 SUM

一、字串函數

字元函數接受字元參數，這些參數可以是表中的列，也可以是字串表達式。

常用的字元函數：

函數	#說明
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的字串，從start處開始，截取length個字符，缺省length，預設到結尾

上面各函數的例子：

範例	範例結果
#SELECT ASCII('a') FROM dual;	#97
SELECT CONCAT('Hello','world') FROM dual;	Helloworld
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 RTRIM('=Hello=','=') FROM dual;	=Hello
SELECT TRIM('='FROM'= Hello=') FROM dual;	Hello
SELECT REPLACE('ABCDE','CD','AAA')FROM dual;	ABAAAE
SELECT SUBSTR('ABCDE',2,3) FROM dual;	BCD

二、數字函數

數字函數接受數字參數，參數可以來自表格中的一列，也可以是數字表達式。

##X除以Y的餘數MOD(8，3)=2POWER(X,Y)X的Y次方POWER( 2，3)=8ROUND(X[,Y])X在Y位四捨五入ROUND(3.456，2) =3.46SQRT(X)X的平方根SQRT(4)=2TRUNC(X[,Y])X在第Y位元截斷TRUNC(3.456，2)=3.45

說明：

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。

三、日期函數

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

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;

四、轉換函數

轉換函數將值從一種資料型別轉換為另一種資料型別。常見的轉換函數有：

1、TO_CHAR(d|n[,fmt])

把日期和數字轉換成製定格式的字串。 Fmt是格式化字串

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

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

 

程式碼解析：

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

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

函數	說明	範例
ABS(X)	X的絕對值	ABS(-3)=3
#ACOS(X)	X的反餘弦	ACOS(1)=0
COS(X)	餘弦	COS(1)=0.54030230586814
#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)

9.#,$##EEEE9.99EEEE

參數
#範例	說明
999	指定位置處顯示數字
9.9	指定位置傳回小數點
99,99	指定位置回傳一個逗號
$999	數字開頭傳回一個美元符號

###科學計數法表示#############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格式转换为一个数字

代码演示：TO_NUM函数

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

五、其它单行函数

1、NVL(X,VALUE)

如果X为空，返回value，否则返回X

例：对工资是2000元以下的员工，如果没发奖金，每人奖金100元

代码演示：NVL函数

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

例：对EMP表中工资为2000元以下的员工，如果没有奖金，则奖金为200元，如果有奖金，则在原来的奖金基础上加100元

代码演示：NVL2函数

SELECT ENAME,JOB,SAL,NVL2(COMM,comm+100,200) "comm" 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

六、聚合函数

聚合函数同时对一组数据进行操作，返回一行结果，比如计算一组数据的总和，平均值等。

名称	作用	语法
AVG	平均值	AVG（表达式）
SUM	求和	SUM(表达式)
MIN、MAX	最小值、最大值	MIN(表达式)、MAX(表达式)
COUNT	数据统计	COUNT（表达式）

例：求本月所有员工的基本工资总和

代码演示：sum函数

SELECT SUM(sal) FROM emp;

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

SUM(SAL)

29025

例：求不同部门的平均工资

代码演示：AVG函数下的分组查询

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

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

DEPTNO    AVG(SAL)

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

30    1566.66666

20     2175

10    2916.66666

推荐教程：《Oracle视频教程》

以上是歸納整理Oracle中常用函數的詳細內容。更多資訊請關注PHP中文網其他相關文章！