Oracle之SQL函數

標籤：開始   輸入   trail   mat   平均值   sql函數   tin   補齊   使用者   

1.SQL函數
(1)字元函數：主要是操縱字串
(2)數字函數：主要是操縱數字類型的數值
(3)轉換函式：這些函數將資料從一種資料類型轉換為另一種資料類型
(4)日期函數：處理日期和時間
(5)Regex函數：這些函數使用Regex搜尋資料
2.字元函數
（1）CONCAT(m,n) 將m和n串連起來，並返回串連後的字串
（2）INITCAP(n) 將n中的第一個字母轉換成大寫
（3）INSTR(x,y,m,n) 在x中尋找字串y出現的位置。其中m,n是可選項，m代表從字串x的第m個位置開始尋找。n代表字串y出現次數
（4）LENGTH(n) 求字串n的長度
（5）LOWER(n)將字串n中各個字元轉換成小寫
（6）LPAD(x,n,y) 在字串x的左邊補齊空格，得到總長為n個字元的字串。其中y是可選項，這個參數用於指定左邊補齊的字串
（7）RPAD(x,n,y) 類似於LPAD，只是在字串x的右邊補齊
（8）TRIM( [ leading | trailing | both  [ trim_character ]  ] FORM x) 預設在x左邊和右邊截去指定字元，其中leading、trailing、both、trim_character都是可選項。
如果指定參數leading，則截去x左邊的指定字元。如果指定參數trailing，則截去x右邊的指定字元。如果指定參數both，則截去x左右兩邊的指定字元。參數trim_character指定要截去的字元
（9）LTRIM(x,y) 在x左邊截去指定的字元。指定字元可以通過參數y來表示。如果沒有指定要截去字元，則預設截去空格
（10）RTRIM(x,y) 類似於LTRIM，只是在字串x的右邊截去指定字元
（11）NVL(x,y) 如果x為NULL，則結果返回y；否則結果返回x
（12）NVL2(x,y,z) 如果x不為NULL，則結果返回y；否則結果返回z
（13）NANVL(x,y) 如果x的值是NAN（非數字），則結果返回y；否則結果返回x。Oracle 10g新增特性
（14）REPLACE(x,y,z)在x中將字元或是字串y，替換為z
（15）UPPER(x) 將字串n中各個字元轉換成大寫
（16）SUBSTR(x,m,n) 從字元x的m位置開始取得長度為n的一個子字串，其中n可以省略不寫，那麼取得子字串就是從m開始到字串的結尾

3.數字函數
（1）ABS(X) 求x的絕對值
 (2) FLOOR 向下取整 （=< x)
 (3) ceil 向上取整 （>=x)
 (4) ROUND(x,y) 對x進行取整，y是選擇性參數，表示對第幾位小數進行取整。如果y為正數，則從x的小數點右邊第y位開始取整；如果y為負數，則從x的小數點左邊第y位開始取整
 (5)TRUNC(x,y) 對x進行截斷，y是選擇性參數，表示對第幾位小數進行截斷。如果y為正數，則從x的小數點右邊第y位開始截斷；如果y為負數，則從x的小數點左邊第y位開始截斷
 (6)MOD(x,y) 求x除以y的餘數
4.日期函數
（1） ADD_MONTHS(x,y) 月+y
（2） last_day(x) 求出本月的最後一天
（3）MONTHS_BETWEEN(x,y) 返回x和y之間的差值 (y<x)
 (4)NEXT_DAY(x,day) 返回從x開始，下一個day的日期
 (5)Oracle 擷取當前日期及日期格式
　　擷取系統日期： SYSDATE()
　　格式化日期： TO_CHAR(SYSDATE(),‘YY/MM/DD HH24:MI:SS)或 TO_DATE(SYSDATE(),‘YY/MM/DD HH24:MI:SS)
　　格式化數字： TO_NUMBER

註： TO_CHAR 把日期或數字轉換為字串
　　TO_CHAR(number, ‘格式‘)
　　TO_CHAR(salary, ‘$99,999.99‘)
　　TO_CHAR(date, ‘格式‘)
　　TO_DATE 把字串轉換為資料庫中的日期類型
　　TO_DATE(char, ‘格式‘)
　　TO_NUMBER 將字串轉換為數字
　　TO_NUMBER(char, ‘格式‘)

　(6)返回系統日期,輸出 25-12月-09
　　　select sysdate from dual;
mi是分鐘，輸出 2009-12-25 14:23:31
select to_char(sysdate,‘yyyy-MM-dd HH24:mi:ss‘) from dual;
mm會顯示月份，輸出 2009-12-25 14:12:31
select to_char(sysdate,‘yyyy-MM-dd HH24:mm:ss‘) from dual;
輸出 09-12-25 14:23:31
select to_char(sysdate,‘yy-mm-dd hh24:mi:ss‘) from dual
輸出 2009-12-25 14:23:31
select to_date(‘2009-12-25 14:23:31‘,‘yyyy-mm-dd,hh24:mi:ss‘) from dual
而如果把上式寫作：
select to_date(‘2009-12-25 14:23:31‘,‘yyyy-mm-dd,hh:mi:ss‘) from dual
則會報錯，因為小時hh是12進位，14為非法輸入，不能匹配。
輸出 $10,000,00 ：
select to_char(1000000,‘$99,999,99‘) from dual;
輸出 RMB10,000,00 ：
select to_char(1000000,‘L99,999,99‘) from dual;
輸出 1000000.12 ：
select trunc(to_number(‘1000000.123‘),2) from dual;
select to_number(‘1000000.123‘) from dual;
轉換的格式：
表示 year 的：y 表示年的最後一位 、
yy 表示年的最後2位 、
yyy 表示年的最後3位 、
yyyy 用4位元表示年
表示month的： mm 用2位元字表示月 、
mon 用簡寫形式， 比如11月或者nov 、
month 用全稱， 比如11月或者november
表示day的：dd 表示當月第幾天 、
ddd 表示當年第幾天 、
dy 當周第幾天，簡寫， 比如星期五或者fri 、
day 當周第幾天，全稱， 比如星期五或者friday
表示hour的：hh 2位元表示小時 12進位 、
hh24 2位元表示小時 24小時
表示minute的：mi 2位元表示分鐘
表示second的：ss 2位元表示秒 60進位
表示季度的：q 一位元 表示季度 （1-4）
另外還有ww 用來表示當年第幾周 w用來表示當月第幾周。
24小時制下的時間範圍：00：00：00-23：59：59
12小時制下的時間範圍：1：00：00-12：59：59
數字格式: 9 代表一個數字
0 強制顯示0
$ 放置一個$符
L 放置一個浮動本地貨幣符
. 顯示小數點
, 顯示千位指示符
補充：
目前時間減去7分鐘的時間
select sysdate,sysdate - interval ‘7‘ MINUTE from dual;
目前時間減去7小時的時間
select sysdate - interval ‘7‘ hour from dual;
目前時間減去7天的時間
select sysdate - interval ‘7‘ day from dual;
目前時間減去7月的時間
select sysdate,sysdate - interval ‘7‘ month from dual;
目前時間減去7年的時間
select sysdate,sysdate - interval ‘7‘ year from dual;
時間間隔乘以一個數字
select sysdate,sysdate - 8*interval ‘7‘ hour from dual;
含義解釋：
Dual偽列
Dual 是 Oracle中的一個實際存在的表，任何使用者均可讀取，常用在沒有目標表的select語句塊中。
不同系統可能返回日期的格式不一樣。
返回當前串連的使用者：select user from dual;

6.轉換函式
（1） BIN_TO_NUM(x) 將x轉換為一個位元
（2） TO_CHAR(x[,format]) 將x轉換為一個VARCHAR2字串。format為選擇性參數，用來指定x的格式
（3）CHARTOROWID(x) 將x轉換為ROWID類型，ROWID長度為18位
（4）TO_DATE(x[,format])將x轉換為一個DATE。format為選擇性參數，用來指定x的格式
（5）TO_NUMBER(x[,format]) 將x轉換為一個NUMBER。format為選擇性參數，用來指定x的格式

(8)bin_to_num(x) 將x轉換為十進位數字x格式 1,1,1
(9)chartorowid(x)將x 轉換為rowid類型，rowid為18位
(10)rowidtochar(x)將rowid x轉換為一個varchar2類型字元
(11)rowidtonachar(x)將rowid x轉換為一個nvarchar2類型的字元
(12)to_char(x[,format])將x 轉換為一個varchar2字串。format為選擇性參數。用來指定x 的格式
註：數字會隱式轉換
(13)to_date(x[,format])將x轉換為一個date。format為選擇性參數，用來指定x的格式
(14)to_number(x[,format])將x轉換為一個number。format為選擇性參數，用來指定x的格式
7.數字函數
abs(x)求絕對值
asin(x)求反正弦函數
acos(x)求反餘弦函數
atin(x)求反正切函數
cos(x)求餘弦函數
sin(x)求正弦函數
*ceil(x)求大於或是等於x的最小整數
*floor(x)求小於或是等於x的最大整數
*round(x,y)對x進行取整，y是選擇性參數，表示對第幾位小數進行取整。如果y為正數，則從x 的小數點右邊第y為開始取整；如果y為負數，則從x的小數點左邊第y位開始取整，按四捨五入
*trunc(x,y)對x進行截斷，y是選擇性參數，表示對第幾位小數進行截斷。如果y為正數，則從x 的小數點右邊，反之反之
mod(x,y)求x除以y的餘數

8.執行個體應用

(1)數字函數舉例：
floor：取小
select floor(5.8) from dual; //傳回值為5
ceil：取da
select ceil(5.8) from dual; //傳回值為6
round：
select round(5.89),round(5.89,1),round(5.89,-1) from dual; //傳回值為6,5.9,10
trunc：
select trunc(5.89),trunc(5.89,1),trunc(5.89,-1) from dual; //傳回值為5,5.8,0

(2)日期函數舉例

add_months:
select add_months(sysdate,1) from dual;
last_day:
select last_day(sysdate) from dual;
next_day:
select sysdate,next_day(sysdate,5) from dual;
months_between:
select months_between(sysdate,sysdate+1) from dual;
round:對年取整，月日變為1；對月，日變為1
select round(sysdate),round(sysdate,‘yyyy‘),round(sysdate,‘mm‘) from dual;
trunc:以y為單位把後面的截斷
trunc(sysdate),trunc(sysdate,‘yyyy‘),trunc(sysdate,‘mm‘) from dual;
無論是DATE還是timestamp都可以進行加減操作。
可以對當前日期加年、月、日、時、分、秒，操作不同的時間類型，有三種方法：

1 使用內建函數numtodsinterval增加小時，分鐘和秒
2 加一個簡單的數來增加天
3 使用內建函數add_months來增加年和月

例：
對當前日期增加一個小時：
SQL> select sysdate, sysdate+numtodsinterval(1,’hour’) from dual ;

SYSDATE SYSDATE+NUMTODSINTE
——————- ——————-
2010-10-14 21:38:19 2010-10-14 22:38:19
對當前日期增加50分種
SQL> select sysdate, sysdate+numtodsinterval(50,’minute’) from dual ;

SYSDATE SYSDATE+NUMTODSINTE
——————- ——————-
2010-10-14 21:39:12 2010-10-14 22:29:12
對當前日期增加45秒
SQL> select sysdate, sysdate+numtodsinterval(45,’second’) from dual ;

SYSDATE SYSDATE+NUMTODSINTE
——————- ——————-
2010-10-14 21:40:06 2010-10-14 21:40:51
對當前日期增加3天
SQL> select sysdate, sysdate+3 from dual ;

SYSDATE SYSDATE+3
——————- ——————-
2010-10-14 21:40:46 2010-10-17 21:40:46
對當前日期增加4個月
SQL> select sysdate, add_months(sysdate,4) from dual ;

SYSDATE ADD_MONTHS(SYSDATE,
——————- ——————-
2010-10-14 21:41:43 2011-02-14 21:41:43

當前日期增加2年
SQL> select sysdate, add_months(sysdate,12*2) from dual ;

SYSDATE ADD_MONTHS(SYSDATE,
——————- ——————-
2010-10-14 21:42:17 2012-10-14 21:42:17

timestamp的操作方法與上面類似；
求兩個日期之差：
例：求2007-5-23 21：23：34與目前時間之間的差值。
SQL> select sysdate-to_date(’20070523 21:23:34′,’yyyy-mm-dd hh24:mi:ss’) dt from
dual ;

DT

1240.01623
如果兩個日期直接相減，得到的結果是一個資料型，我們可能想要得到兩個日期相差值表現形式為：
**年**月**日 **:**:**
SQL> SELECT NUMTOYMINTERVAL(MONTHS_BETWEEN(DT1, DT2), ‘month’) mon,
2 numtodsinterval(dt1-(add_months(dt2,trunc(MONTHS_BETWEEN(DT1, DT2)))
),’day’) DAY
3 FROM (SELECT SYSDATE DT1,
4 TO_DATE(’20070523 21:23:34′, ‘yyyy-mm-dd hh24:mi:ss’) DT2
5 FROM DUAL)
6 ;

MON DAY
—————- ———————-
+000000003-04 +000000021 00:40:15.999999999

即：3年 4 個月 21 天 00:40:15.99999999
Oracle彙總函式：
count返回找到的記錄數
count(列名)該列有的記錄數
count(*)表中共有幾條記錄
min返回一個數字列或計算資料行的最小值
max返回一個數字列或計算資料行的最大值
sum返回一個數字列或計算資料行的總和
avg對一個數字列或計算資料行求平均值
*Group by 子句
警告：
（1）如果查詢中包含彙總函式，而所選擇的列並不在彙總函式中，那麼這些列就必須在group by子句中。
（2）不能在where子句中使用彙總函式來限制行。只能通過having子句來限制行。
Group by主要可以用於將行分組為具有相同列值的多個部分。
Group by如果只是對行進行單獨的分組使用，那麼使用意義不大。
在複雜的SQL查詢中Group by子句大多數時候出現在彙總函式一起
如果查詢中包含一個彙總函式，而所選擇的列並不在彙總函式中，
那麼這些列就必須在group by子句中；
Having子句可以對彙總函式進行限制。

Oracle之SQL函數