oracle的函數有：1、字符串函數，包括ASCII()、CONCAT()等；2、數字函數，包括ABS()、COS()等；3、日期函數，包括EXTRACT()、ROUND()等；4、轉換函數，包括TO_CHAR()、TO_DATE()等。
本教程操作環境：Windows7系統、Oracle 11g版、Dell G3電腦。
Oracle SQL語句中經常使用到Oracle自帶的函數，這些函數豐富了SQL的語言功能，為Oracle SQL提供了更多的操作性。Oracle函數可以接受零個或者多個輸入參數，并返回一個輸出結果。?Oracle?數據庫中主要使用兩種類型的函數：
1、單行函數：對每一個函數應用在表的記錄中時，只能輸入一行中的列值作為輸入參數(或常數)，并且返回一個結果。
例如1：MOD(X,Y)?是求余函數，返回的X除以Y的余數，其中X和Y可以是列值，也可以是常數。
例如2：TO_CHAR(X,’YYYYMMDD’)是時間類型轉字符串的函數，其中X可以是行中某一時間類型（date）的列，也可以是一個時間類型的常數。
常用的單行函數大致以下幾類：
-
字符串函數：對字符串進行操作，例如：TO_CHAR()、SUBSTR()、DECODE()等等。
-
數值函數：對數值進行計算或操作，返回一個數字。例如：ABS()、MOD()、ROUND()等等。
-
轉換函數：將一種數據類型轉換成另外一種類型：例如：TO_CHAR()、TO_NUMBER()、TO_DATE()等等。
-
日期函數：對時間和日期進行操作的函數。例如：TRUNC()、SYSDATE()、ADD_MONTHS()等等。
2、聚合函數：聚合函數同時可以對多行數據進行操作，并返回一個結果。比如?SUM(x)返回結果集中?x?列的總合。
一、字符串函數
字符函數接受字符參數，這些參數可以是表中的列，也可以是一個字符串表達式。
常用的字符函數：
|
函數 |
說明 |
|
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 |
二、數字函數
數字函數接受數字參數，參數可以來自表中的一列，也可以是一個數字表達式。
|
函數 |
說明 |
示例 |
|
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) |
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 |
說明：
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;
?
代碼解析：
在格式化字符串中，使用雙引號對非格式化字符進行引用
針對數字的格式化，格式化字符有：
|
參數 |
示例 |
說明 |
|
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格式轉換為一個數字
代碼演示：TO_NUM函數
SELECT?TO_NUMBER('-$12,345.67','$99,999.99')"num"?FROM?dual;
?
五、其它單行函數
1、NVL(X,VALUE)
如果X為空，返回value，否則返回X
例：對工資是2000元以下的員工，如果沒發獎金，每人獎金100元
代碼演示：NVL函數
SQL>?SELECT?ENAME,JOB,SAL,NVL(COMM,100)?FROM?EMP?WHERE?SAL<p><strong>2、NVL2(x,value1,value2)</strong></p><p>如果x非空，返回value1，否則返回value2</p><p>例：對EMP表中工資為2000元以下的員工，如果沒有獎金，則獎金為200元，如果有獎金，則在原來的獎金基礎上加100元</p><p>代碼演示：NVL2函數</p><pre class="brush:sql;toolbar:false">SQL>?SELECT?ENAME,JOB,SAL,NVL2(COMM,comm+100,200)?"comm" 2???FROM?EMP?WHERE?SAL<h2><strong>六、聚合函數</strong></h2><p>聚合函數同時對一組數據進行操作，返回一行結果，比如計算一組數據的總和，平均值</p><p>等。</p>
|
名稱 |
作用 |
語法 |
|
AVG |
平均值 |
AVG（表達式） |
|
SUM |
求和 |
SUM(表達式) |
|
MIN、MAX |
最小值、最大值 |
MIN(表達式)、MAX(表達式) |
|
COUNT |
數據統計 |
COUNT（表達式） |
例：求本月所有員工的基本工資總和
代碼演示：sum函數
SQL>?SELECT?SUM(sal)?FROM?emp; ------------------------------------------------------------------- SUM(SAL) 29025
例：求不同部門的平均工資
代碼演示：AVG函數下的分組查詢
SQL>?SELECT?DEPTNO,AVG(SAL)?FROM?EMP?GROUP?BY?DEPTNO;?? ------------------------------------------------------------------- DEPTNO????AVG(SAL) ---------????---------- 30????1566.66666 20?????2175 10????2916.66666
推薦教程：《Oracle教程》
