Oracle使用簡單函數

Oracle使用簡單函數

本章將介紹Oracle資料庫的內建函數。函數可以接受零個或多個參數，並返回一個輸出參數。在Oracle資料庫中可以使用兩種主要類型的參數：

• 單行函數（single row function）單行函數同時只能對一行進行操作，且對輸入的每一行返回一行輸出結果。單行函數的樣本是CONCAT(x,y)，其功能是將y添加到x上，並返回所得到的字串

• 彙總函式（aggregate function）彙總函式同時可以對多行進行操作，並返回一行輸出結果。彙總函式的一個例子是AVG(x),其功能是返回x的平均值，其中x可以是一列，或者說一個運算式

1. 使用單行函數。單行函數主要有5種：

   1. 字元函數。可以對由字元組成的字串進行操作

   2. 數字函數。可以對數字進行計算

   3. 轉換函式。可以將一種資料庫類型轉換成另一中資料庫類型

   4. 日期函數。可以對日期和時間進行處理

   5. Regex函數。在查詢資料時可以使用Regex

1.1 字元函數

字 符 函 數 列 表

函數	說明
ASCII（x）	返回字元x的ASCII碼
CHR(x)	返回ASCII碼為x的字元
CONCAT(x,y)	將y添加到x上，並將得到的字串作為結果返回
INITCAP(x)	將x中每個單詞的首字母都轉換成大寫，並返回處理結果
INSET(x,find_string[,start][,occurentce])	在x中尋找字串find_string，然後返回find_string所在的位置。可以提供一個可選的start位置來指定該函數從這個位置開始尋找。同樣，也可以指定一個可選的occurrence參數來說明應該返回find_string第幾次出現的位置
LENGTH(x)	返回x字串的長度
LOWER(x)

1.2 數字函數

    數字函數可以用於計算。數字函數可以接受輸入的數字參數，這些數字可以來自一個表的數字列，也可以是一個結果為數位運算式。數字函數會對這些輸入的數字進行計算返回一個數字結果。數字函數的一個例子是SQRT(x)，該函數返回x的平方根

函數	說明	例子
ABS(x)	返回x的絕對值	ABS(10) = 10 ABS(-10) = 10
ACOS(x)	返回x的反餘弦	ACOS(1) = 0 ACOS(-1) = 3.1415926
ASIN(x)	返回x的反正弦	這些有興趣的朋友可以自行驗證。
ATAN(x)	返回x的反正切
ATAN2(x)	返回x和y的反正切
BITAND(x,y)	返回對x和y進行位與（AND ）操作的結果
COS(x)	返回x的餘弦
COSH(x)	返回x的雙曲線餘弦函數
CEIL(X)	返回大於或等於x的最小整數
EXP(X)	返回e的x次冪，x約等於2.7.18
FLOOR(X)	返回小於或等於x的最大整數
LOG(X,Y)	返回以x為底y的對數
LN(x)	返回x的自然對數
MOD(x,y)	返回x除以y的餘數
POWER(x,y)	返回x的y次冪
ROUND(x[,y])	返回對x取整的結果
SIGN(x)	如果x是負數則返回-1，如果是整數，返回1，如果是0，返回0
SIN(X)	返回x的正弦函數
SINH(x)	返回x的雙曲線正弦函數
SQRT(x)	返回x的平方根
TAN(x)	返回x的正切函數

1.3 轉換函式

    有時可能需要將一個值從一種資料類型轉換為另一種資料類型。

ASCIISTR(x)	將x轉換為一個ASCII字串
BIN_TO_NUM(x)	將位元字x轉換為NUMBER類型
DECODE(x,search,result,default)	將x與search中的值進行比較，如果相等，返回result的值，否則返回default
TO_BINARY_DOUBLE(x)	將x轉換為一個BINARY_DOUBLE類型
TO_BINARY_FLOAT(x)	將x轉換為一個BINARY_FLOAT類型
TO_BLOB(x)	將x轉換為一個二進位大對象（BLOB）類型。
TO_CHAR(x[,format])	將x轉換為一個VARCHAR2字串。可以指定一個選擇性參數format來說明x的格式
TO_CLOB(x)	將x轉換為一個字元大對象（CLOB）類型。
TO_DATE(x[,format])	將x轉換為一個DATE類型。
TO_TIMESTAMP(x)	將字串x轉換為一個TIMESTAMP類型

1.4 Regex函數

    Regex中包含許多元字元。^可以匹配一個字串的開頭：[5-8]可以匹配介於5~8之間的數字；$可以匹配一個字串的結尾。

    orcle中常用的元字元

元字元	說明	例子
\	說明要匹配的字元是一個特殊字元、常量	\n匹配分行符號 \\匹配\ \(匹配( \)匹配)
^	匹配字串的開頭位置
$	匹配字串的末尾位置
*	匹配前面的字元0次或多次	ba*rk可以匹配brk,bark,baark
+	匹配前面字串1此或多次	ba+rk可以匹配bark、baark等
?	匹配前面的字元0次或1次
{n}	匹配一個字元恰好是n次，	hob{2}it可以匹配hobbit
{n,m}	匹配一個字元至少n次，最多m次
.	匹配出null之外的任意單個字元
(pattern)	匹配一個指定的子運算式
x|y	匹配x或者y，
{abc}	匹配abc中的任意一個字元
[a-z]	匹配指定範圍內的任意一個字元
[::]	指定一個字元類，可以匹配該類中的任何字元	[:alphanum:]可以匹配字元0-9、A-Z和a-z [:alpha:]可以匹配字元A-Z和a-z [:blank:]可以匹配空格和tab鍵 [:digit:]可以匹配數字0-9 [:graph:]可以匹配非Null 字元 [:lower:]可以匹配小寫字母
[==]	指定等價類別
\n	這是對前一次捕獲的一個反向引用，其中n是一個正整數
\d	數字字元
\D	非數字字元
\w	字母字元
\W	非字母字元
\s	空白字元
\S	非空白字元
\A	只匹配字串的開頭位置
\Z	只匹配字串的末尾位置
*?	匹配前面的模式元素0次或多次
+?	匹配前面的模式元素1次或多次
??	匹配前面的模式元素0次或
Regex函數
函數	說明
REGEXP_LIKE(x,pattern[,match_option])	從x中搜尋pattern參數中定義的Regex。可以使用match_option修改預設匹配選項
REGEXP_INSRT(x,pattern[,start[,occurence[,rerturn_option[,match_option]]]])	在x中尋找pattern，並返回pattern所在的位置。可以指定一下選擇性參數： start：開始尋找的位置。預設是1，指x的第一個字元 occurrence 說明應該返回當前第幾次出現pattern的位置 return_option 說明應該返回什麼整數 match_option 修改預設的匹配設定
REGEXP_REPLACE(x,pattern[,replace_string[,start[,occurrence[,match_option]]])	在x中尋找pattern，並將其替換為replace_string。其他選項請參照上一個方法

二、使用彙總函式

函數	說明
AVG(x)	返回x的平均值
COUNT(x)	返回一個包含x的查詢的行數
MAX(x)	返回x的最大值
MEDIA(x)	返回x的中間值
MIN(x)	返回x的最小值
STDDEV(x)	返回x的標準差
SUM(x)	返回x的和
VARIANCE(x)	返回x的方差

三、對行進行分組GROUP BY，使用HAVING子句過濾行組。Having放在group不要之後.group by可以不與having一起使用，但是having必須與group by一起使用。