PL/SQL單行函數和組函數詳解 函數是一種有零個或多個參數並且有一個返回值的程序
在SQL中Oracle內建了一系列函數
這些函數都可被稱為SQL或PL/SQL語句
函數主要分為兩大類
單行函數
組函數
本文將討論如何利用單行函數以及使用規則
SQL中的單行函數 SQL和PL/SQL中自帶很多類型的函數
有字符
數字
日期
轉換
和混合型等多種函數用於處理單行數據
因此這些都可被統稱為單行函數
這些函數均可用於SELECT
WHERE
ORDER BY等子句中
例如下面的例子中就包含了TO_CHAR
UPPER
SOUNDEX等單行函數
SELECT ename
TO_CHAR(hiredate
day
DD
Mon
YYYY
)FROM empWhere UPPER(ename) Like
AL%
ORDER BY SOUNDEX(ename)
單行函數也可以在其他語句中使用
如update的SET子句
INSERT的VALUES子句
DELET的WHERE子句
認證考試特別注意在SELECT語句中使用這些函數
所以我們的注意力也集中在SELECT語句中
NULL和單行函數 在如何理解NULL上開始是很困難的
就算是一個很有經驗的人依然對此感到困惑
NULL值表示一個未知數據或者一個空值
算術操作符的任何一個操作數為NULL值
結果均為提個NULL值
這個規則也適合很多函數
只有CONCAT
DECODE
DUMP
NVL
REPLACE在調用了NULL參數時能夠返回非NULL值
在這些中NVL函數時最重要的
因為他能直接處理NULL值
NVL有兩個參數
NVL(x
x
)
x
和x
都式表達式
當x
為null時返回X
否則返回x
下面我們看看emp數據表它包含了薪水
獎金兩項
需要計算總的補償
column name emp_id salary bonuskey type pk nulls/unique nn
u nnfk table datatype number number numberlength
不是簡單的將薪水和獎金加起來就可以了
如果某一行是null值那麼結果就將是null
比如下面的例子
update empset salary=(salary+bonus)*
這個語句中
雇員的工資和獎金都將更新為一個新的值
但是如果沒有獎金
即 salary + null
那麼就會得出錯誤的結論
這個時候就要使用nvl函數來排除null值的影響
所以正確的語句是
update empset salary=(salary+nvl(bonus
)*
單行字符串函數 單行字符串函數用於操作字符串數據
他們大多數有一個或多個參數
其中絕大多數返回字符串
ASCII()
c
是一字符串
返回c
第一個字母的ASCII碼
他的逆函數是CHR()
SELECT ASCII(
A
) BIG_A
ASCII(
z
) BIG_z FROM empBIG_A BIG_z
CHR(＜i＞)[NCHAR_CS]
i是一個數字
函數返回十進制表示的字符
select CHR(
)
CHR(
)
CHR(
) FROM empCHR
CHR
CHR
A z B
CONCAT(
)
c
c
均為字符串
函數將c
連接到c
的後面
如果c
為null
將返回c
如果c
為null
則返回c
如果c
c
都為null
則返回null
他和操作符||返回的結果相同
select concat(
slobo
Svoboda
) username from dualusernameslobo Syoboda
INITCAP()
c
為一字符串
函數將每個單詞的第一個字母大寫其它字母小寫返回
單詞由空格
控制字符
標點符號限制
select INITCAP(
veni
vedi
vici
) Ceasar from dualCeasarVeni
Vedi
Vici
INSTR(
[
＜i＞[
]])
c
c
均為字符串
i
j為整數
函數返回c
在c
中第j次出現的位置
搜索從c
的第i個字符開始
當沒有發現需要的字符時返回
如果i為負數
那麼搜索將從右到左進行
但是位置的計算還是從左到右
i和j的缺省值為
select INSTR(
Mississippi
i
) from dualINSTR(
MISSISSIPPI
I
)
select INSTR(
Mississippi
i
) from dualINSTR(
MISSISSIPPI
I
)
INSTRB(
[
i[
j])
與INSTR（）函數一樣
只是他返回的是字節
對於單字節INSTRB()等於INSTR()
LENGTH()
c
為字符串
返回c
的長度
如果c
為null
那麼將返回null值
select LENGTH(
Ipso Facto
) ergo from dualergo
LENGTHb()
與LENGTH()一樣
返回字節
lower()
返回c的小寫字符
經常出現在where子串中
select LOWER(colorname) from itemdetail WHERE LOWER(colorname) LIKE
%white%
COLORNAMEWinterwhite
LPAD(
＜i＞[
])
c
c
均為字符串
i為整數
在c
的左側用c
字符串補足致長度i
可多次重復
如果i小於c
的長度
那麼只返回i那麼長的c
字符
其他的將被截去
c
的缺省值為單空格
參見RPAD
select LPAD(answer
) padded
answer unpadded from question;PADDED UNPADDED Yes YesNO NOMaybe maybe
LTRIM(
)
把c
中最左邊的字符去掉
使其第一個字符不在c
中
如果沒有c
那麼c
就不會改變
select LTRIM(
Mississippi
Mis
) from dualLTRppi
RPAD(
＜i＞[
])
在c
的右側用c
字符串補足致長度i
可多次重復
如果i小於c
的長度
那麼只返回i那麼長的c
字符
其他的將被截去
c
的缺省值為單空格
其他與LPAD相似
RTRIM(
)
把c
中最右邊的字符去掉
使其第後一個字符不在c
中
如果沒有c
那麼c
就不會改變
REPLACE(
[
])
c
c
c
都是字符串
函數用c
代替出現在c
中的c
後返回
select REPLACE(
uptown
up
down
) from dualREPLACEdowntown
STBSTR(
＜i＞[
])
c
為一字符串
i
j為整數
從c
的第i位開始返回長度為j的子字符串
如果j為空
則直到串的尾部
select SUBSTR(
Message
) from dualSUBSMess
SUBSTRB(
＜i＞[
])
與SUBSTR大致相同
只是I
J是以字節計算
SOUNDEX()
返回與c
發音相似的詞
select SOUNDEX(
dawes
) Dawes SOUNDEX(
daws
) Daws
SOUNDEX(
dawson
) from dualDawes Daws DawsonD
D
D
TRANSLATE(
)
將c
中與c
相同的字符以c
代替
select TRANSLATE(
fumble
uf
ar
) test from dualTEXTramble
TRIM([[]] from c
)
將c
串中的第一個
最後一個
或者都刪除
select TRIM(
space padded
) trim from dual TRIMspace padded
UPPER()
返回c
的大寫
常出現where子串中
select name from dual where UPPER(name) LIKE
KI%
NAMEKING
單行數字函數
單行數字函數操作數字數據
執行數學和算術運算
所有函數都有數字參數並返回數字值
所有三角函數的操作數和值都是弧度而不是角度
oracle沒有提供內建的弧度和角度的轉換函數
ABS()
返回n的絕對值
ACOS()
反余玄函數
返回
到
之間的數
n表示弧度
select ACOS(
) pi
ACOS(
) ZERO FROM dualPI ZERO
ASIN()
反正玄函數
返回
到
n表示弧度
ATAN()
反正切函數
返回n的反正切值
n表示弧度
CEIL()
返回大於或等於n的最小整數
COS()
返回n的余玄值
n為弧度
COSH()
返回n的雙曲余玄值
n 為數字
select COSH(<1.4>) FROM dualCOSH(
)
EXP()
返回e的n次冪
e=
FLOOR()
返回小於等於N的最大整數
LN()
返回N的自然對數
N必須大於
LOG(
)
返回以n
為底n
的對數
MOD()
返回n
除以n
的余數
POWER(
)
返回n
的n
次方
ROUND(
)
返回捨入小數點右邊n
位的n
的值
n
的缺省值為
這回將小數點最接近的整數
如果n
為負數就捨入到小數點左邊相應的位上
n
必須是整數
select ROUND(
)
ROUND(
) FROM dualROUND(
) ROUND(
)
SIGN()
如果n為負數
返回
如果n為正數
返回
如果n=
返回
SIN（)
返回n的正玄值
n為弧度
SINH()
返回n的雙曲正玄值
n為弧度
SQRT()
返回n的平方根
n為弧度
TAN（)
返回n的正切值
n為弧度
TANH()
返回n的雙曲正切值
n為弧度
TRUNC(
)
返回截尾到n
位小數的n
的值
n
缺省設置為
當n
為缺省設置時會將n
截尾為整數
如果n
為負值
就截尾在小數點左邊相應的位上
單行日期函數
單行日期函數操作DATA數據類型
絕大多數都有DATA數據類型的參數
絕大多數返回的也是DATA數據類型的值
ADD_MONTHS(
＜i＞)
返回日期d加上i個月後的結果
i可以使任意整數
如果i是一個小數
那麼數據庫將隱式的他轉換成整數
將會截去小數點後面的部分
LAST_DAY()
函數返回包含日期d的月份的最後一天
MONTHS_BETWEEN(
)
返回d
和d
之間月的數目
如果d
和d
的日的日期都相同
或者都使該月的最後一天
那麼將返回一個整數
否則會返回的結果將包含一個分數
NEW_TIME(
)
d
是一個日期數據類型
當時區tz
中的日期和時間是d時
返回時區tz
中的日期和時間
tz
和tz
時字符串
NEXT_DAY(
)
返回日期d後由dow給出的條件的第一天
dow使用當前會話中給出的語言指定了一周中的某一天
返回的時間分量與d的時間分量相同
select NEXT_DAY(
Jan
Monday
)
st Monday
NEXT_DAY(
Nov
Tuesday
)+
nd Tuesday
) from dual;
st Monday
nd Tuesday
Jan
Nov
ROUND([
])
將日期d按照fmt指定的格式捨入
fmt為字符串
SYADATE
函數沒有參數
返回當前日期和時間
TRUNC([
])
返回由fmt指定的單位的日期d
單行轉換函數
單行轉換函數用於操作多數據類型
在數據類型之間進行轉換
CHARTORWID()
c 使一個字符串
函數將c轉換為RWID數據類型
SELECT test_id from test_case where rowid=CHARTORWID(
AAAA
SAACAAAALiAAA
)
CONVERT(
[
])
c尾字符串
dset
sset是兩個字符集
函數將字符串c由sset字符集轉換為dset字符集
sset的缺省設置為數據庫的字符集
HEXTORAW()
x為
進制的字符串
函數將
進制的x轉換為RAW數據類型
RAWTOHEX()
x是RAW數據類型字符串
函數將RAW數據類轉換為
進制的數據類型
ROWIDTOCHAR()
函數將ROWID數據類型轉換為CHAR數據類型
TO_CHAR([[
)
x是一個data或number數據類型
函數將x轉換成fmt指定格式的char數據類型
如果x為日期nlsparm=NLS_DATE_LANGUAGE 控制返回的月份和日份所使用的語言
如果x為數字nlsparm=NLS_NUMERIC_CHARACTERS 用來指定小數位和千分位的分隔符
以及貨幣符號
NLS_NUMERIC_CHARACTERS =
dg
NLS_CURRENCY=
string
TO_DATE([
[
）
c表示字符串
fmt表示一種特殊格式的字符串
返回按照fmt格式顯示的c
nlsparm表示使用的語言
函數將字符串c轉換成date數據類型
TO_MULTI_BYTE()
c表示一個字符串
函數將c的擔子截字符轉換成多字節字符
TO_NUMBER([
[
)
c表示字符串
fmt表示一個特殊格式的字符串
函數返回值按照fmt指定的格式顯示
nlsparm表示語言
函數將返回c代表的數字
TO_SINGLE_BYTE()
將字符串c中得多字節字符轉化成等價的單字節字符
該函數僅當數據庫字符集同時包含單字節和多字節字符時才使用
其它單行函數
BFILENAME(
)
dir是一個directory類型的對象
file為一文件名
函數返回一個空的BFILE位置值指示符
函數用於初始化BFILE變量或者是BFILE列
DECODE(
[
[])
x是一個表達式
m
是一個匹配表達式
x與m
比較
如果m
等於x
那麼返回r
否則
x與m
比較
依次類推m
m
m
直到有返回結果
DUMP(
[
[
[
]]])
x是一個表達式或字符
fmt表示
進制
進制
進制
或則單字符
函數返回包含了有關x的內部表示信息的VARCHAR
類型的值
如果指定了n
n
那麼從n
開始的長度為n
的字節將被返回
EMPTY_BLOB()
該函數沒有參數
函數返回 一個空的BLOB位置指示符
函數用於初始化一個BLOB變量或BLOB列
EMPTY_CLOB()
該函數沒有參數
函數返回 一個空的CLOB位置指示符
函數用於初始化一個CLOB變量或CLOB列
GREATEST()
exp_list是一列表達式
返回其中最大的表達式
每個表達式都被隱含的轉換第一個表達式的數據類型
如果第一個表達式是字符串數據類型中的任何一個
那麼返回的結果是varchar
數據類型
同時使用的比較是非填充空格類型的比較
LEAST()
exp_list是一列表達式
返回其中最小的表達式
每個表達式都被隱含的轉換第一個表達式的數據類型
如果第一個表達式是字符串數據類型中的任何一個
將返回的結果是varchar
數據類型
同時使用的比較是非填充空格類型的比較
UID
該函數沒有參數
返回唯一標示當前數據庫用戶的整數
USER
返回當前用戶的用戶名
USERENV()
基於opt返回包含當前會話信息
opt的可選值為
ISDBA 會話中SYSDBA腳色響應
返回TRUE
SESSIONID 返回審計會話標示符
ENTRYID 返回可用的審計項標示符
INSTANCE 在會話連接後
返回實例標示符
該值只用於運行Parallel 服務器並且有 多個實例的情況下使用
LANGUAGE 返回語言
地域
數據庫設置的字符集
LANG 返回語言名稱的ISO縮寫
TERMINAL 為當前會話使用的終端或計算機返回操作系統的標示符
VSIZE()
x是一個表達式
返回x內部表示的字節數
SQL中的組函數 組函數也叫集合函數
返回基於多個行的單一結果
行的准確數量無法確定
除非查詢被執行並且所有的結果都被包含在內
與單行函數不同的是
在解析時所有的行都是已知的
由於這種差別使組函數與單行函數有在要求和行為上有微小的差異
組（多行）函數 與單行函數相比
oracle提供了豐富的基於組的
多行的函數
這些函數可以在select或select的having子句中使用
當用於select子串時常常都和GROUP BY一起使用
AVG([{DISYINCT|ALL}])
返回數值的平均值
缺省設置為ALL
SELECT AVG(sal)
AVG(ALL sal)
AVG(DISTINCT sal) FROM scott
empAVG(SAL) AVG(ALL SAL) AVG(DISTINCT SAL)
COUNT({*|DISTINCT|ALL} )
返回查詢中行的數目
缺省設置是ALL
*表示返回所有的行
MAX([{DISTINCT|ALL}])
返回選擇列表項目的最大值
如果x是字符串數據類型
他返回一個VARCHAR
數據類型
如果X是一個DATA數據類型
返回一個日期
如果X是numeric數據類型
返回一個數字
注意distinct和all不起作用
應為最大值與這兩種設置是相同的
MIN([{DISTINCT|ALL}])
返回選擇列表項目的最小值
STDDEV([{DISTINCT|ALL}])
返回選者的列表項目的標准差
所謂標准差是方差的平方根
SUM([{DISTINCT|ALL}])
返回選擇列表項目的數值的總和
VARIANCE([{DISTINCT|ALL}])
返回選擇列表項目的統計方差
用GROUP BY給數據分組 正如題目暗示的那樣組函數就是操作那些已經分好組的數據
我們告訴數據庫用GROUP BY怎樣給數據分組或者分類
當我們在SELECT語句的SELECT子句中使用組函數時
我們必須把為分組或非常數列放置在GROUP BY子句中
如果沒有用group by進行專門處理
那麼缺省的分類是將整個結果設為一類
select stat
counter(*) zip_count from zip_codes GROUP BY state;ST ZIP_COUNT
AK
AL
AR
AZ
CA
在這個例子中
我們用state字段分類
如果我們要將結果按照zip_codes排序
可以用ORDER BY語句
ORDER BY子句可以使用列或組函數
select stat
counter(*) zip_count from zip_codes GROUP BY state ORDER BY COUNT(*) DESC;ST COUNT(*)
NY
PA
TX
CA
用HAVING子句限制分組數據 現在你已經知道了在查詢的SELECT語句和ORDER BY子句中使用主函數
組函數只能用於兩個子串中
組函數不能用於WHERE子串中
例如下面的查詢是錯誤的
錯誤SELECT sales_clerk
SUN(sale_amount) FROM gross_sales WHERE sales_dept=
OUTSIDE
AND SUM(sale_amount)>
GROUP BY sales_clerk
這個語句中數據庫不知道SUM()是什麼
當我們需要指示數據庫對行分組
然後限制分組後的行的輸出時
正確的方法是使用HAVING語句
SELECT sales_clerk
SUN(sale_amount) FROM gross_sales WHERE sales_dept=
OUTSIDE
GROUP BY sales_clerkHAVING SUM(sale_amount)>
;
嵌套函數 函數可以嵌套
一個函數的輸出可以是另一個函數的輸入
操作數有一個可繼承的執行過程
但函數的優先權只是基於位置
函數遵循由內到外
由左到右的原則
嵌套技術一般用於象DECODE這樣的能被用於邏輯判斷語句IF
THEN
ELSE的函數
嵌套函數可以包括在組函數中嵌套單行函數
或者組函數嵌套入單行函數或組函數中
比如下面的例子
SELECT deptno
GREATEST(COUNT(DISTINCT job)
COUNT(DISTINCT mgr) cnt
COUNT(DISTINCT job) jobs
COUNT(DISTINCT mgr) mgrsFROM empGROUP BY deptno;DEPTNO CNT JOBS MGRS
From:http://tw.wingwit.com/Article/program/Oracle/201311/18932.html
