oracle sql 提供了用于執行特定操作的專用函數。這些函數大大增強了 sql 語言的功能。
sql函數的分類：
對每一個函數應用在表的記錄中時，隻能輸入一行結果，傳回一個結果，可以出現在 select 子句中和 where 子句中
比如：mod(x,y)傳回 x 除以 y 的餘數（x 和 y 可以是兩個整數，也可以是表中的整
數列）。常用的單行函數有：
Ø 字元函數：對字元串操作。
Ø 數字函數：對數字進行計算，傳回一個數字。
Ø 轉換函數：可以将一種資料類型轉換為另外一種資料類型。
Ø 日期函數：對日期和時間進行處理。
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，預設到結尾
一些其它的字元函數
1、chr ：根據ascii碼傳回對應的字元
2、lpad和rpad ：填充
3、trim ：去除左右兩邊的空格符
4、length ：字元串長度
decode :逐個值替換 （類似與case end/switch結構）
上面各函數的例子：
示例
示例結果
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;
select ltrim('=hello=','=') from dual;
hello=
select rtrim('=hello=','=') from dual;
=hello
select trim('='from'=hello=') from dual;
select replace('abcde','cd','aaa')from dual;
abaaae
select substr('abcde',2,3) from dual;
bcd
函數
輸入
輸出
initcap(char)
select initcap(‘hello’) from dual;
lower(char)
select lower(‘fun’) from dual;
fun
upper(char)
select upper(‘sun’) from dual;
sun
ltrim(char,set)
select ltrim( ‘xyzadams’,’xyz’) from dual;
adams
rtrim(char,set)
select rtrim(‘xyzadams’,’ams’) from dual;
xyzad
translate(char, from, to)
select translate('jack','jac' ,'ban') from dual;
bank
replace(char, searchstring,rep string)
select replace(‘jack and juejac’ ,’jac’,’bl’) from dual;
blk and jue bl
instr (s1, s2 [,i ,j])
select instr (‘worldwide’,’d’) from dual;
substr (s, m, n)
select substr(‘abcdefg’,3,2) from dual;
cd
concat (expr1, expr2)
select concat (‘hello’,’ world’) from dual;
hello world
二、數字函數
數字函數接受數字參數，參數可以來自表中的一列，也可以是一個數字表達式。
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。
abs(n)
select abs(-15) from dual;
15
ceil(n)
select ceil(44.778) from dual;
45
floor(n)
select floor(100.2) from dual;
100
cos(n)
select cos(180) from dual;
-.5984601
power(m,n)
select power(4,3) from dual;
64
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(16) from dual;
4
sign(n)
select sign(-30) from dual;
-1
三、日期函數
日期函數對日期進行運算。常用的日期函數有：
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函數
2、nvl2(x,value1,value2)
如果x非空，傳回value1，否則傳回value2
例：對emp表中工資為2000元以下的員工，如果沒有獎金，則獎金為200元，如果有獎金，則在原來的獎金基礎上加100元
代碼示範：nvl2函數
3、nullif（表達式1，表達式2）
如果表達式1和表達式2相等則傳回空值，如果表達式1和表達式2不相等則傳回表達式1的結果
聚合函數同時對一組資料進行操作，傳回一行結果，比如計算一組資料的總和，平均值
等。
名稱
作用
文法
avg
平均值
avg（表達式）
sum
求和
sum(表達式)
min、max
最小值、最大值
min(表達式)、max(表達式)
count
資料統計
count（表達式）
例：求本月所有員工的基本工資總和
代碼示範：sum函數
例：求不同部門的平均工資
代碼示範：avg函數下的分組查詢
分析函數根據一組行來計算聚合值，用于計算完成聚集的累計排名、移動平均數等，分析函數為每組記錄傳回多個行。
常用的分析函數有
1、row_number：傳回連續的序号，不論值是否相等
2、rank：具有相等值的行排位相同，序号随後跳躍
3、dense_rank：具有相等值的行排位相同，序号是連續的
以上三個分析函數用于計算一個行在一組有序行中的排位，序号從1開始。
分析函數和聚合函數的不同之處
普通的聚合函數用group by分組，每個分組傳回一個統計值，而分析函數采用partition by分組，并且每組每行都可以傳回一個統計值。
分析函數的形式
分析函數帶有一個開窗函數over()，包含三個分析子句:分組(partition by), 排序(order by), 視窗(rows) ，他們的使用形式如下：over(partition by xxx order by yyy rows between zzz)。
分析函數例子(在scott使用者下模拟)
示例目的：顯示各部門員工的工資，并附帶顯示該部分的最高工資。
rank()值相同時排名相同，其後排名跳躍不連續