概述
ORACLE函數系列：
Oracle常見函數大全
Oracle-分析函數之連續求和sum(…) over(…)
Oracle-分析函數之排序值rank()和dense_rank()
Oracle-分析函數之排序後順序号row_number()
Oracle-分析函數之取上下行資料lag()和lead()
數值型函數
傳回絕對值 abs(x)
ABS(X)
【功能】傳回x的絕對值
【參數】x，數字型表達式
【傳回】數字
SQL> select abs(100) , abs(-100) from dual ;
ABS(100) ABS(-100)
---------- ----------
100 100 傳回正負值 sign(x)
sign(x)
【功能】傳回x的正負值
【參數】x，數字型表達式
【傳回】數字，若為正值傳回1，負值傳回-1，0傳回0
SQL> select sign(100), sign(-100),sign(0) from dual ;
SIGN(100) SIGN(-100) SIGN(0)
---------- ---------- ----------
1 -1 0 傳回較大的最小整數 ceil(x)
ceil(x)
【功能】傳回大于等于x的最小整數值
【參數】x，數字型表達式
【傳回】數字
SQL> select ceil(3.1) , ceil(3.1+5.2), ceil(0) from dual ;
CEIL(3.1) CEIL(3.1+5.2) CEIL(0)
---------- ------------- ----------
4 9 0 傳回較小的最大整數 floor(x)
floor(x)
【功能】傳回小于等于x的最大整數值
SQL> select floor(3.1) , floor(3.1+5.2), floor(0) from dual ;
FLOOR(3.1) FLOOR(3.1+5.2) FLOOR(0)
---------- -------------- ----------
3 8 0 傳回x的y次幂 power(x,y)
power(x,y)
【功能】傳回x的y次幂
【參數】x，y 數字型表達式
SQL> select power(2.5 ,2), power(1.5,0),power(20,-1) from dual ;
POWER(2.5,2) POWER(1.5,0) POWER(20,-1)
------------ ------------ ------------
6.25 1 0.05 【相近】exp(y)
傳回e的y次幂。（e為數學常量）
【關系】z=power(x,y),則y=1/log(z,x) (條件z,x>0)
傳回常量e的y次幂 exp(y)
exp(y)
【功能】傳回e的y次幂（e為數學常量）
【參數】y，數字型表達式
SQL> select exp(3) ,exp(0),exp(-3) from dual ;
EXP(3) EXP(0) EXP(-3)
---------- ---------- ----------
20.0855369 1 0.04978706 【相近】power(x,y)
傳回e的y次幂。
【相反】ln(y)
傳回e為底的自然對數。
傳回以x為底的y的對數 log(x,y)
【功能】傳回以x為底的y的對數
【參數】x，y,數字型表達式，
【條件】x,y都必須大于0
SQL> select power(4,2) , log(16,2),1/log(16,4) from dual ;
POWER(4,2) LOG(16,2) 1/LOG(16,4)
---------- ---------- -----------
16 0.25 2
SQL> select power(6.5,3),log(274.625,3),1/log(power(6.5,3),6.5) from dual;
POWER(6.5,3) LOG(274.625,3) 1/LOG(POWER(6.5,3),6.5)
------------ -------------- -----------------------
274.625 0.195642520743 3 【相近】ln(y)
傳回e為底的y的對數。（e為數學常量）
傳回以e為底的y的對數（e為數學常量）
ln(y)
【功能】傳回以e為底的y的對數（e為數學常量）
【參數】y，數字型表達式 (條件y>0)
SQL> select exp(3),exp(-3),ln(20.0855369),ln(0.049787068) from dual;
EXP(3) EXP(-3) LN(20.0855369) LN(0.049787068)
---------- ---------- -------------- ---------------
20.0855369 0.04978706 2.999999998845 -3.000000007388
【相近】log(x,y)
傳回以x為底的y的對數
【相反】exp(y)
傳回e的y次幂
傳回x除以y的餘數 mod(x,y)
【功能】傳回x除以y的餘數
【參數】x,y，數字型表達式
SQL> select mod(23,8),mod(24,8) from dual;
MOD(23,8) MOD(24,8)
---------- ----------
7 0
傳回四舍五入後的值 round(x[,y])
round(x[,y])
【功能】傳回四舍五入後的值
【參數】x,y，數字型表達式,
如果y不為整數則截取y整數部分，
如果y>0則四舍五入為y位小數，
如果y小于0則四舍五入到小數點向左第y位。
SQL> select round(5555.6666,2.1),round(5555.6666,-2.6),round(5555.6666) from dual;
ROUND(5555.6666,2.1) ROUND(5555.6666,-2.6) ROUND(5555.6666)
-------------------- --------------------- ----------------
5555.67 5600 5556
【相近】trunc(x[,y])
傳回截取後的值，用法同round(x[,y]),隻是不四舍五入
傳回x按精度y截取後的值 trun(x[,y])
【功能】傳回x按精度y截取後的值
【參數】x,y，數字型表達式,
如果y>0則截取到y位小數，
如果y小于0則截取到小數點向左第y位，小數前其它資料用0表示。
SQL> select trunc(5555.66666,2.1),trunc(5555.66666,-2.6),trunc(5555.033333) from dual;
TRUNC(5555.66666,2.1) TRUNC(5555.66666,-2.6) TRUNC(5555.033333)
--------------------- ---------------------- ------------------
5555.66 5500 5555 【相近】round(x[,y])
傳回截取後的值，用法同trunc(x[,y]),隻是要做四舍五入
傳回x的平方根 sqrt(x)
sqrt(x)
【功能】傳回x的平方根
【參數】x數字型表達式
SQL> select sqrt(64),sqrt(10) from dual;
SQRT(64) SQRT(10)
---------- ----------
8 3.16227766 三角函數
SIN(x)
【功能】傳回一個數字的正弦值
SQL> select sin(1.57079) from dual;
SIN(1.57079)
------------
0.9999999999 SIGH(x)
【功能】傳回雙曲正弦的值
SQL> select sin(20),sinh(20) from dual;
SIN(20) SINH(20)
---------- ----------
0.91294525 242582597. COS(x)
【功能】傳回一個給定數字的餘弦
SQL> select cos(-3.1415927) from dual;
COS(-3.1415927)
---------------
-0.999999999999
COSH(x)
【功能】傳回一個數字反餘弦值
SQL> select cosh(20) from dual;
COSH(20)
----------
242582597. TAN
【功能傳回數字的正切值
SQL> select tan(20),tan(10) from dual;
TAN(20) TAN(10)
---------- ----------
2.23716094 0.64836082 TANH
【功能傳回數字n的雙曲正切值
SQL> select tanh(20),tan(20) from dual;
TANH(20) TAN(20)
---------- ----------
1 2.23716094 ASIN(x)
【功能】給出反正弦的值
【示例】select asin(0.5) from dual;
傳回：0.52359878
ACOS(x)
【功能】給出反餘弦的值
SQL> select asin(0.5) from dual;
ASIN(0.5)
----------
0.52359877
ATAN(x)
【功能】傳回一個數字的反正切值
SQL> select atan(1) from dual;
ATAN(1)
----------
0.78539816 字元型函數
傳回字元表達式最左端字元的ASCII 碼值 ASCII(x1)
ASCII(x1)
【功能】：傳回字元表達式最左端字元的ASCII 碼值。
【參數】：x1，字元表達式
【傳回】：數值型
SQL> select ascii('A') A,ascii('a') a,ascii(' ') space,ascii('示') hz from dual;
A A SPACE HZ
---------- ---------- ---------- ----------
65 97 32 51902 【說明】在ASCII（）函數中，純數字的字元串可不用‘’括起來，但含其它字元的字元串必須用‘’括起來使用，否則會出錯。
如果最左端是漢字，隻取漢字最左半邊字元的ASCII 碼
【互反函數】：chr()
傳回ASCII為x的字元 chr()
CHR(n1)
【功能】：将ASCII 碼轉換為字元。
【參數】：n1,為0 ~ 255，整數
【傳回】：字元型
SQL> select chr(54740) zhao,chr(65) chr65 from dual;
ZHAO CHR65
---- -----
趙 A 【互反函數】：ASCII
連接配接兩個字元串 concat(c1,c2)
CONCAT(c1,c2)
【功能】連接配接兩個字元串
【參數】c1，c2 字元型表達式
【傳回】字元型
同：c1||c2
SQL> select concat('010-','88888888')||'轉3456' 電話号碼 from dual;
電話号碼
------------------
010-88888888轉3456 把每個單詞的首字個字母變成大寫 initcap(c1)
INITCAP(c1)
【功能】傳回字元串并将字元串的第一個字母變為大寫，其它字母小寫;
【參數】c1字元型表達式
【傳回】字元型
SQL> select initcap('smith abc aBC') upp from dual ;
UPP
-------------
Smith Abc Abc 把整個字元串轉換為小寫 lower(c1)
LOWER(c1)
【功能】：将字元串全部轉為小寫
【參數】：c1，字元表達式
SQL> select lower('AaBbCcDd')AaBbCcDd from dual;
AABBCCDD
--------
aabbccdd 把整個字元串轉換為大寫 upper(c1)
UPPER(c1)
【功能】将字元串全部轉為大寫
【參數】c1，字元表達式
SQL> select upper('AaBbCcDd') upper from dual;
UPPER
--------
AABBCCDD
把每個單詞首個字母變為大寫 nls_initcap(x[,y])
NLS_INITCAP(x[,y])
【參數】x字元型表達式
【參數】Nls_param可選，
查詢資料級的NLS設定:select * from nls_database_parameters;
例如：
指定排序的方式(nls_sort=) 。
nls_sort=SCHINESE_RADICAL_M（部首、筆畫）
nls_sort=SCHINESE_STROKE_M（筆畫、部首SCHINESE_PINYIN_M（拼音））
SQL> select nls_initcap('ab cde') "test", nls_initcap('a c b d e','nls_sort= SCHINESE_PINYIN_M') "test1" from dual;
test test1
------ ---------
Ab Cde A C B D E SQL> select nls_initcap('ab cde') "test",nls_initcap('a c b d e','NLS_LANGUAGE=AMERICAN') "test1" from dual;
test test1
------ ---------
Ab Cde A C B D E 把整個字元串轉換為小寫 nls_lower(x[,y])
NLS_LOWER(x[,y])
【功能】傳回字元串并将字元串的變為小寫;
【參數】Nls_param可選，指定排序的方式(nls_sort=) 。
SCHINESE_RADICAL_M（部首、筆畫）
SCHINESE_STROKE_M（筆畫、部首SCHINESE_PINYIN_M（拼音））
SQL> select nls_LOWER('ab cde') "test",nls_LOWER('a c b d e','nls_sort= SCHINESE_PINYIN_M') "test1" from dual;
test test1
------ ---------
ab cde a c b d e 把整個字元串轉換為大寫 nls_upper(x[,y])
NLS_UPPER(x[,y])
【功能】傳回字元串并将字元串的轉換為大寫;
SQL> select NLS_UPPER('ab cde') "test",NLS_UPPER('a c b d e','nls_sort= SCHINESE_PINYIN_M') "test1" from dual;
test test1
------ ---------
AB CDE A C B D E 字元串中搜尋字元位置(全角算1字元) instr(C1,C2[,I[,J]])
INSTR(C1,C2[,I[,J]])
【功能】在一個字元串中搜尋指定的字元,傳回發現指定的字元的位置;
【說明】多位元組符(漢字、全角符等)，按1個字元計算
【參數】
C1 被搜尋的字元串
C2 希望搜尋的字元串
I 搜尋的開始位置,預設為1
J 第J次出現的位置,預設為1
【傳回】數值
SQL> select instr('oracle traning','ra',1,2) instring from dual;
INSTRING
----------
9 SQL> select instr('重慶某軟體公司','某',1,1),instrb('重慶某軟體公司','某',1,1) instring from dual;
INSTR('重慶某軟體公司','某',1, INSTRING
------------------------------ ----------
3 5 字元串中搜尋字元位置(全角算2字元) instrb(C1,C2[,I[,J]])
INSTRB(C1,C2[,I[,J]])
【說明】多位元組符(漢字、全角符等)，按2個字元計算
SQL> select instr('重慶某軟體公司','某',1,1),instrb('重慶某軟體公司','某',1,1) instring from dual;
INSTR('重慶某軟體公司','某',1, INSTRING
------------------------------ ----------
3 5 傳回字元串的長度（全角算1字元） length(c1)
LENGTH(c1)
【功能】傳回字元串的長度;
【參數】C1 字元串
【傳回】數值型
SQL> select length('小工匠'),length('北京市海錠區'),length('北京TO_CHAR') from dual;
LENGTH('小工匠') LENGTH('北京市海錠區') LENGTH('北京TO_CHAR')
---------------- ---------------------- ---------------------
3 6 9 傳回字元串的長度（全角算2字元）lengthb(c1)
SQL> select lengthb('小工匠'),lengthb('北京市海錠區'),lengthb('北京TO_CHAR') from dual;
LENGTHB('小工匠') LENGTHB('北京市海錠區') LENGTHB('北京TO_CHAR')
----------------- ----------------------- ----------------------
6 12 11 傳回字元串的長度（其他）lengthc(c1) length2(c1) length4(c1)
LENGTHC(c1).LENGTH2(c1).LENGTH4(c1)
SQL> select length('小工匠'),length('北京市海錠區'),length('北京TO_CHAR') from dual;
LENGTH('小工匠') LENGTH('北京市海錠區') LENGTH('北京TO_CHAR')
---------------- ---------------------- ---------------------
3 6 9 Oracle中的字元函數中，有一類函數是求字元長度的函數，length、lengthB、lengthC、length2、length4幾個函數中比較常用的是length、lengthB。
他們的含義分别是：
Length函數傳回字元的個數，使用定義是給定的字元集來計算字元的個數
LENGTHB給出該字元串的byte
LENGTHC使用純Unicode
LENGTH2使用UCS2
LENGTH4使用UCS4
SQL> Select length('你好'), lengthB('你好'),lengthC('你好'),length2('你好'), length4('你好') from dual;
LENGTH('你好') LENGTHB('你好') LENGTHC('你好') LENGTH2('你好') LENGTH4('你好')
-------------- --------------- --------------- --------------- ---------------
2 4 2 2 2 在左邊添加字元 lpad(c1,n[,c2])
LPAD(c1,n[,c2])
【功能】在字元串c1的左邊用字元串c2填充，直到長度為n時為止
n 追加後字元總長度
c2 追加字元串,預設為空格
【說明】如果c1長度大于n，則傳回c1左邊n個字元
如果如果c1長度小于n，c2和c1連接配接後大于n，則傳回連接配接後的右邊n個字元
SQL> select lpad('楊',10,'*') from dual;
LPAD('楊',10,'*')
-----------------
********楊 不夠字元則用*來填滿
【相似】RPAD()在列的右邊粘貼字元
【相反】LTRIM() 删除左邊出現的字元串
在右邊添加字元 rpad(c1,n[,c2])
RPAD(c1,n[,c2])
【功能】在字元串c1的右邊用字元串c2填充，直到長度為n時為止
如果如果c1長度小于n，c1和c2連接配接後大于n，則傳回連接配接後的左邊n個字元
如果如果c1長度小于n，c1和c2連接配接後小于n，則傳回c1與多個重複c2連接配接(總長度>=n)後的左邊n個字元
SQL> select rpad('gao',10,'*a') from dual;
RPAD('GAO',10,'*A')
-------------------
gao*a*a*a* 【相似】LPAD()在列的左邊粘貼字元
【相反】RTRIM() 删除右邊出現的字元串
删除左邊字元 ltrim(c1,[,c2])
LTRIM(c1,[,c2])
【功能】删除左邊出現的字元串
SQL> select LTRIM(' xiao gong jiang',' ') text from dual;
TEXT
---------------
xiao gong jiang
或者
SQL> select ltrim(' xiao gong jiang') text from dual;
TEXT
---------------
xiao gong jiang
SQL> select ltrim('x xiao gong jiang' ,'x') text from dual;
TEXT
------------------
xiao gong jiang 删除右邊字元 rtrim(c1,[,c2])
RTRIM(c1,[,c2])
【功能】删除右邊出現的字元串
SQL> select RTRIM(' xiao gong jiang XXXX','X') text from dual;
TEXT
-----------------
xiao gong jiang 【相似】LTRIM()删除左邊出現的字元串
【相反】RPAD() 在列的右邊粘貼字元
替換子串字元 replace(c1,c2[,c3])
REPLACE(c1,c2[,c3])
【功能】将字元表達式值中，部分相同字元串，替換成新的字元串
c1 希望被替換的字元或變量
c2 被替換的字元串
c3 要替換的字元串，預設為空(即删除之意，不是空格)
SQL> select replace('he love you','he','i') test from dual;
TEST
----------
i love you 字元串語音表示形式 soundex(c1)
SOUNDEX(c1)
【功能】傳回字元串參數的語音表示形式
【參數】c1,字元型
【傳回】字元串
【說明】相對于比較一些讀音相同，但是拼寫不同的單詞是非常有用的。
計算語音的算法：
1.保留字元串首字母，但删除a、e、h、i、o、w、y
2.将下表中的數字賦給相對應的字母
(1) 1：b、f、p、v
(2) 2：c、g、k、q、s、x、z
(3) 3：d、t
(4) 4：l
(5) 5：m、n
(6) 6：r
3. 如果字元串中存在擁有相同數字的2個以上（包含2個）的字母在一起（例如b和f），或者隻有h或w，則删除其他的，隻保留1個
4.隻傳回前4個位元組，不夠用0填充
示例：
SQL> select soundex('two'),soundex('too'),soundex('to') from dual ;
SOUNDEX('TWO') SOUNDEX('TOO') SOUNDEX('TO')
-------------- -------------- -------------
T000 T000 T000 SQL> select soundex('cap'),soundex('cup') from dual ;
SOUNDEX('CAP') SOUNDEX('CUP')
-------------- --------------
C100 C100 SQL> select soundex('house'),soundex('horse') from dual ;
SOUNDEX('HOUSE') SOUNDEX('HORSE')
---------------- ----------------
H200 H620 截取字元串（全角算1字元） substr(c1,n1[,n2])
SUBSTR(c1,n1[,n2])
【功能】取子字元串
【參數】在字元表達式c1裡，從n1開始取n2個字元;若不指定n2,則從第y個字元直到結束的字串.
SQL> select substr('1301234567890',3,8) test from dual;
TEST
--------
01234567
截取字元串（全角算2字元） substrb(c1,n1[,n2])
SUBSTRB(c1,n1[,n2])
【傳回】字元型,如果從多字元右邊開始,則用空格表示。
SQL> select substr('我手機13012345678',4,11),substrb('我手機13012345678',4,11),substrb('我手機13012345678',3,11) test from dual;
SUBSTR('我手機13012345678',4,1 SUBSTRB('我手機13012345678',4, TEST
------------------------------ ------------------------------ -----------
13012345678 機13012345 手機1301234 替換子字元 translate(c1,c2,c3)
TRANSLATE(c1,c2,c3)
【功能】将字元表達式值中，指定字元替換為新字元
c2 查詢原始的字元集
c3 替換新的字元集，将c2對應順序字元，替換為c3對應順序字元
如果c3長度大于c2，則c3長出後面的字元無效
如果c3長度小于c2，則c2長出後面的字元均替換為空(删除)
如果c3長度為0，則傳回空字元串。
如果c2裡字元重複，按首次位置為替換依據
SQL> select TRANSLATE('he love you','he','i'),
2 TRANSLATE('重慶的人','重慶的','上海男'),
3 TRANSLATE('重慶的人','重慶的重慶','北京男士們'),
4 TRANSLATE('重慶的人','重慶的重慶','1北京男士們'),
5 TRANSLATE('重慶的人','1重慶的重慶','北京男士們') from dual;
TRANSLATE('HELOVEYOU','HE','I' TRANSLATE('重慶的人','重慶的', TRANSLATE('重慶的人','重慶的重 TRANSLATE('重慶的人','重慶的重 TRANSLATE('重慶的人','1重慶的?
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
i lov you 上海男人 北京男人 1北京人 京男士人
删除左邊和右邊字元串 trim(c1 from c2)
TRIM(c1 from c2)
【功能】删除左邊和右邊出現的字元串
【參數】C2 删除前字元串
c1 删除字元串,預設為空格
SQL> select TRIM('X' from 'XXXxiao gong jiangXXXX'),TRIM('X' from 'XXXxiaoXXgongXXXX') text from dual;
TRIM('X'FROM'XXXXIAOGONGJIANGX TEXT
------------------------------ ----------
xiao gong jiang xiaoXXgong 日期函數
傳回系統目前日期 sysydate
sysdate
【功能】：傳回目前日期。
【參數】：沒有參數，沒有括号
【傳回】：日期
SQL> select sysdate from dual;
SYSDATE
-----------
2016-5-25 0 傳回指定月數的日期 add_months()
add_months(d1,n1)
【功能】：傳回在日期d1基礎上再加n1個月後新的日期。
【參數】：d1，日期型，n1數字型
SQL> select sysdate ,add_months(sysdate,5) from dual ;
SYSDATE ADD_MONTHS(SYSDATE,5)
----------- ---------------------
2016-5-25 0 2016-10-25 00:27:59 傳回本月最後一天的日期 last_day()
last_day(d1)
【功能】：傳回日期d1所在月份最後一天的日期。
【參數】：d1，日期型
SQL> select sysdate , last_day(sysdate) from dual ;
SYSDATE LAST_DAY(SYSDATE)
----------- -----------------
2016-5-25 0 2016-5-31 00:30:0 傳回兩個日期間隔月數 months_between
months_between(d1,d2)
【功能】：傳回日期d1到日期d2之間的月數。
【參數】：d1，d2 日期型
【傳回】：數字
如果d1>d2，則傳回正數
如果d1
SQL> select sysdate , months_between(sysdate ,to_date('2016-12-25','YYYY-MM-DD')), months_between(sysdate ,to_date('2015-05-25','YYYY-MM-DD')) from dual ;
SYSDATE MONTHS_BETWEEN(SYSDATE,TO_DATE MONTHS_BETWEEN(SYSDATE,TO_DATE
----------- ------------------------------ ------------------------------
2016-5-25 0 -7 12 傳回時區的對應時間 new_time()
NEW_TIME(dt1,c1,c2)
【功能】：給出時間dt1在c1時區對應c2時區的日期和時間
【參數】：dt1，d2 日期型
【傳回】：日期時間
【參數】：c1,c2對應的 時區及其簡寫
大西洋标準時間：AST或ADT
阿拉斯加_夏威夷時間：HST或HDT
英國夏令時：BST或BDT
美國山區時間：MST或MDT
美國中央時區：CST或CDT
新大陸标準時間：NST
美國東部時間：EST或EDT
太平洋标準時間：PST或PDT
格林威治标準時間：GMT
Yukou标準時間：YST或YDT
SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,
2 to_char(new_time(sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;
BJ_TIME LOS_ANGLES
------------------- -------------------
2016.05.25 00:48:56 2016.05.25 07:48:56 SQL> select sysdate bj_time,
2 new_time(sysdate,'PDT','GMT') los_angles from dual;
BJ_TIME LOS_ANGLES
----------- -----------
2016-5-25 0 2016-5-25 0
四舍五入後的日期第一天 round()
round(d1[,c1])
【功能】：給出日期d1按期間(參數c1)四舍五入後的期間的第一天日期（與數值四舍五入意思相近）
【參數】：d1日期型,c1為字元型(參數)，c1預設為j（即最近0點日期)
【參數表】：c1對應的參數表:
最近0點日期: 取消參數c1或j
最近的星期日：day或dy或d
最近月初日期：month或mon或mm或rm
最近季日期：q
最近年初日期：syear或year或yyyy或yyy或yy或y(多個y表示精度)
最近世紀初日期：cc或scc
SQL> select sysdate 當時日期,
2 round(sysdate) 最近0點日期,
3 round(sysdate,'day') 最近星期日,
4 round(sysdate,'month') 最近月初,
5 round(sysdate,'q') 最近季初日期,
6 round(sysdate,'year') 最近年初日期 from dual;
當時日期 最近0點日期 最近星期日 最近月初 最近季初日期 最近年初日期
----------- ----------- ----------- ----------- ------------ ------------
2016-5-25 0 2016-5-25 2016-5-22 2016-6-1 2016-7-1 2016-1-1 傳回日期所在期間的第一天 trunc()
trunc(d1[,c1])
【功能】：傳回日期d1所在期間(參數c1)的第一天日期
【參數】：d1日期型,c1為字元型(參數)，c1預設為j（即目前日期)
最近的星期日：day或dy或d (每周順序：日，一，二，三，四，五，六）
SQL> select sysdate 當時日期,
2 trunc(sysdate) 今天日期,
3 trunc(sysdate,'day') 本周星期日,
4 trunc(sysdate,'month') 本月初,
5 trunc(sysdate,'q') 本季初日期,
6 trunc(sysdate,'year') 本年初日期 from dual;
當時日期 今天日期 本周星期日 本月初 本季初日期 本年初日期
----------- ----------- ----------- ----------- ----------- -----------
2016-5-25 0 2016-5-25 2016-5-22 2016-5-1 2016-4-1 2016-1-1 傳回下周某一天的日期 next_day()
next_day(d1[,c1])
【功能】：傳回日期d1在下周，星期幾(參數c1)的日期
【參數表】：c1對應:星期一，星期二，星期三……星期日
SQL> select sysdate 當時日期,
2 next_day(sysdate,'星期一') 下周星期一,
3 next_day(sysdate,'星期二') 下周星期二,
4 next_day(sysdate,'星期三') 下周星期三,
5 next_day(sysdate,'星期四') 下周星期四,
6 next_day(sysdate,'星期五') 下周星期五,
7 next_day(sysdate,'星期六') 下周星期六,
8 next_day(sysdate,'星期日') 下周星期日 from dual;
當時日期 下周星期一 下周星期二 下周星期三 下周星期四 下周星期五 下周星期六 下周星期日
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2016-5-25 0 2016-5-30 0 2016-5-31 0 2016-6-1 00 2016-5-26 0 2016-5-27 0 2016-5-28 0 2016-5-29 0 提取時間日期中的資料 extract()
extract(c1 from d1)
【功能】：日期/時間d1中，參數(c1)的值
【參數】：d1日期型(date)/日期時間型(timestamp),c1為字元型(參數)
【參數表】：c1對應的參數表詳見示例
【傳回】：字元
SQL> select
2 extract(hour from timestamp '2001-2-16 2:38:40 ' ) 小時,
3 extract(minute from timestamp '2001-2-16 2:38:40 ' ) 分鐘,
4 extract(second from timestamp '2001-2-16 2:38:40 ' ) 秒,
5 extract(DAY from timestamp '2001-2-16 2:38:40 ' ) 日,
6 extract(MONTH from timestamp '2001-2-16 2:38:40 ' ) 月,
7 extract(YEAR from timestamp '2001-2-16 2:38:40 ' ) 年
8 from dual;
小時 分鐘 秒 日 月 年
---------- ---------- ---------- ---------- ---------- ----------
2 38 40 16 2 2001 SQL> select extract (YEAR from date '2001-2-16' ) from dual;
EXTRACT(YEARFROMDATE'2001-2-16
------------------------------
2001 SQL> select sysdate 目前日期,
2 extract(DAY from sysdate ) 日,
3 extract(MONTH from sysdate ) 月,
4 extract(YEAR from sysdate ) 年
5 from dual;
目前日期 日 月 年
----------- ---------- ---------- ----------
2016-5-25 0 25 5 2016 傳回會話中的時間和日期 localtimestamp
localtimestamp
【功能】：傳回會話中的日期和時間
SQL> select localtimestamp from dual;
LOCALTIMESTAMP
--------------------------------------------------------------------------------
25-MAY-16 06.55.31.054928 PM 傳回目前會話時區中的目前日期和時間 current_timestamp
current_timestamp
【功能】：以timestamp with time zone資料類型傳回目前會話時區中的目前日期
SQL> select current_timestamp from dual ;
CURRENT_TIMESTAMP
--------------------------------------------------------------------------------
25-MAY-16 06.58.09.388569 PM +08:00 傳回資料庫時區設定 dbtimezone
dbtimezone
【功能】：傳回時區
SQL> select dbtimezone from dual ;
DBTIMEZONE
----------
+08:00 傳回目前會話時區 sessiontimezone
SESSIONTIMEZONE
【功能】：傳回會話時區
SQL> select dbtimezone ,sessiontimezone from dual ;
DBTIMEZONE SESSIONTIMEZONE
---------- ---------------------------------------------------------------------------
+08:00 +08:00 變動日期時間數值 interval
INTERVAL c1 set1
【功能】：變動日期時間數值
【參數】：c1為數字字元串或日期時間字元串，set1為日期參數
【參數表】：set1具體參照示例
【傳回】：日期時間格式的數值,前面多個+号
以天或天更小機關時可用數值表達式借用，如1表示1天，1/24表示1小時，1/24/60表示1分鐘
SQL> select trunc(sysdate) ,
2 trunc(sysdate)+(interval '1' second) as pluse1sec, --加1秒(1/24/60/60)
3 trunc(sysdate)+(interval '1' minute) as pluse1min, --加1分鐘(1/24/60)
4 trunc(sysdate)+(interval '1' hour) as pluse1hour , --加1小時(1/24)
5 trunc(sysdate)+(INTERVAL '1' DAY) as pluse1day, --加1天(1)
6 trunc(sysdate)+(INTERVAL '1' MONTH) as pluse1mon, --加1月
7 trunc(sysdate)+(INTERVAL '1' YEAR)as pluse1year, --加1年
8 trunc(sysdate)+(interval '01:02:03' hour to second) as pluseSpecTime1, --加指定小時到秒
9 trunc(sysdate)+(interval '01:02' minute to second) as pluseSpecTime2, --加指定分鐘到秒
10 trunc(sysdate)+(interval '01:02' hour to minute) as pluseSpecTime3, --加指定小時到分鐘
11 trunc(sysdate)+(interval '2 01:02' day to minute) as pluseSpecTime4 --加指定天數到分鐘
12 from dual;
TRUNC(SYSDATE) PLUSE1SEC PLUSE1MIN PLUSE1HOUR PLUSE1DAY PLUSE1MON PLUSE1YEAR PLUSESPECTIME1 PLUSESPECTIME2 PLUSESPECTIME3 PLUSESPECTIME4
-------------- ----------- ----------- ----------- ----------- ----------- ----------- -------------- -------------- -------------- --------------
2016-05-25 2016-05-25 2016-05-25 2016-05-25 2016-05-26 2016-06-25 2017-05-25 2016-05-25 1:0 2016-05-25 0:0 2016-05-25 1:0 2016-05-27 1:0
轉換函數
字元串轉為rowid值 chartorowid(c1)
chartorowid(c1) 。。
【功能】轉換varchar2類型為rowid值
【參數】c1,字元串，長度為18的字元串，字元串必須符合rowid格式
【傳回】傳回rowid值
SQL> SELECT chartorowid('AAAADeAABAAAAZSAAA') FROM DUAL;
CHARTOROWID('AAAADEAABAAAAZSAA
------------------------------
AAAADeAABAAAAZSAAA 【說明】
在Oracle中，每一條記錄都有一個rowid，rowid在整個資料庫中是唯一的，rowid确定了每條記錄是在Oracle中的哪一個資料檔案、塊、行上。
在重複的記錄中，可能所有列的内容都相同，但rowid不會相同.
rowid的值轉換為字元串 rowidtochar(rowid)
ROWIDTOCHAR(rowid) 。。
【功能】轉換rowid值為varchar2類型
【參數】rowid,固定參數
【傳回】傳回長度為18的字元串
SQL> select rowidtochar(rowid) from dual ;
ROWIDTOCHAR(ROWID)
------------------
AAAAECAABAAAAgqAAA
字元串語言字元集轉換 convert(c1,set1,set2)
CONVERT(c1,set1,set2)
【功能】将源字元串c1 從一個語言字元集set2轉換到另一個目的set1字元集
【參數】c1,字元串，set1,set2為字元型參數
【傳回】字元串
SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual;
conversion
----------
strutz SQL> select convert('strutz','we8hp','f7dec') as conversion from dual;
CONVERSION
----------
strutz 十六進制構成的字元串轉換為二進制 HEXTORAW(c1)
HEXTORAW(c1)
【功能】将一個十六進制構成的字元串轉換為二進制
【參數】c1,十六進制的字元串
SQL> select HEXTORAW('A123') from dual;
HEXTORAW('A123')
----------------
A123 二進制構成的字元串轉換為十六進制 rawtohex(c1)
RAWTOHEX(c1)
【功能】将一個二進制構成的字元串轉換為十六進制
【參數】c1,二進制的字元串
SQL> select rawtohex('A123') from dual ;
RAWTOHEX('A123')
----------------
41313233 将日期或資料轉換為char資料類型TO_CHAR(x[[,c2],C3])
【功能】将日期或資料轉換為char資料類型
x是一個date或number資料類型。
c2為格式參數
c3為NLS設定參數
如果x為日期nlsparm=NLS_DATE_LANGUAGE 控制傳回的月份和日份所使用的語言。
如果x為數字nlsparm=NLS_NUMERIC_CHARACTERS 用來指定小數位和千分位的分隔符，以及貨币符号。
NLS_NUMERIC_CHARACTERS =”dg”, NLS_CURRENCY=”string”
【傳回】varchar2字元型
【說明1】x為資料型時
to_char(1210.73, '9999.9') 傳回 '1210.7'
to_char(1210.73, '9,999.99') 傳回 '1,210.73'
to_char(1210.73, '$9,999.00') 傳回 '$1,210.73'
to_char(21, '000099') 傳回 '000021'
to_char(852,'xxxx') 傳回' 354'
【說明2】x為日期型,c2可用參數
to_char(sysdate,'d') 每周第幾天
to_char(sysdate,'dd') 每月第幾天
to_char(sysdate,'ddd') 每年第幾天
to_char(sysdate,'ww') 每年第幾周
to_char(sysdate,'mm') 每年第幾月
to_char(sysdate,'q') 每年第幾季
to_char(sysdate,'yyyy') 年 【示例】帶C3示例
SQL> select to_char(to_date('2016-06-02','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
TO_CHAR(TO_DATE('2016-06-02','
------------------------------
thursday 字元串轉換為日期型 TO_DATE(X[,c2[,c3]])
【功能】将字元串X轉化為日期型
【參數】c2,c3,字元型，參照to_char()
如果x格式為日期型(date)格式時，則相同表達：date x
如果x格式為日期時間型(timestamp)格式時，則相同表達：timestamp x
【相反】 to_char(date[,c2[,c3]])
SQL> select to_date('199912','yyyymm'),
2 to_date('2000.05.20','yyyy.mm.dd'),
3 (date '2008-12-31') XXdate,
4 to_date('2008-12-31 12:31:30','yyyy-mm-dd hh24:mi:ss'),
5 (timestamp '2008-12-31 12:31:30') XXtimestamp
6 from dual;
TO_DATE('199912','YYYYMM') TO_DATE('2000.05.20','YYYY.MM. XXDATE TO_DATE('2008-12-3112:31:30',' XXTIMESTAMP
-------------------------- ------------------------------ ----------- ------------------------------ --------------------------------------------------------------------------------
1999-12-01 2000-05-20 2008-12-31 2008-12-31 12:31:30 31-DEC-08 12.31.30.000000000 PM
字元串轉換為字元型TO_NUMBER(X[[,c2],c3])
TO_NUMBER(X[[,c2],c3])
【功能】将字元串X轉化為數字型
【傳回】數字串
【相反】 to_char(date[[,c2],c3])
SQL> select TO_NUMBER('199912'),TO_NUMBER('450.05') from dual;
TO_NUMBER('199912') TO_NUMBER('450.05')
------------------- -------------------
199912 450.05 轉換為16進制。
TO_CHAR(100,'XX')= 64
SQL> select TO_CHAR(100,'XX') from dual ;
TO_CHAR(100,'XX')
-----------------
64 半角轉化為全角TO_MULTI_BYTE(c1)
TO_MULTI_BYTE(c1)
【功能】将字元串中的半角轉化為全角
SQL> select to_multi_byte('高A') text from dual;
test
--
高Ａ 全角轉化為半角to_single_byte(c1)
to_single_byte(c1)
【功能】将字元串中的全角轉化為半角
SQL> select to_multi_byte('高Ａ') text from dual;
test
----
高A 字元集名稱轉換為ID nls_charset_id(c1)
nls_charset_id(c1)
【功能】傳回字元集名稱參應id值
SQL> select nls_charset_id('zhs16gbk') from dual;
NLS_CHARSET_ID('ZHS16GBK')
--------------------------
852 字元集ID轉換為名稱 nls_charset_name(n1)
nls_charset_name(n1)
【參數】n1,數值型
SQL> select nls_charset_name(852) from dual;
NLS_CHARSET_NAME(852)
---------------------
ZHS16GBK 聚組函數
統計平均值 AVG([distinct|all]x)
AVG([distinct|all]x)
【功能】統計資料表選中行x列的平均值。
【參數】all表示對所有的值求平均值,distinct隻對不同的值求平均值，預設為all
如果有參數distinct或all，需有空格與x(列)隔開。
【參數】x，隻能為數值型字段
【傳回】數字值
SQL> create table table3(xm varchar(8),sal number(7,2));
Table created
SQL> insert into table3 values('gao',1111.11);
1 row inserted
SQL> insert into table3 values('gao',1111.11);
1 row inserted
SQL> insert into table3 values('zhu',5555.55);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from table3 ;
XM SAL
-------- ---------
gao 1111.11
gao 1111.11
zhu 5555.55
SQL> select avg(all sal) ,avg(distinct sal) , avg(sal) from table3;
AVG(ALLSAL) AVG(DISTINCTSAL) AVG(SAL)
----------- ---------------- ----------
2592.59 3333.33 2592.59 統計合計值 SUM([distinct|all]x)
【功能】統計資料表選中行x列的合計值。
【參數】all表示對所有的值求合計值,distinct隻對不同的值求合計值，預設為all
環境：
create table table3(xm varchar(8),sal number(7,2));
insert into table3 values('gao',1111.11);
insert into table3 values('gao',1111.11);
insert into table3 values('zhu',5555.55);
commit;
執行統計：
SQL> select SUM(distinct sal),SUM(all sal),SUM(sal) from table3;
SUM(DISTINCTSAL) SUM(ALLSAL) SUM(SAL)
---------------- ----------- ----------
6666.66 7777.77 7777.77 統計标準誤差 STDDEV([distinct|all]x)
【功能】統計資料表選中行x列的标準誤差。
【參數】all表示對所有的值求标準誤差,distinct隻對不同的值求标準誤差，預設為all
【示例】
環境：
create table table3(xm varchar(8),sal number(7,2));
insert into table3 values('gao',1111.11);
insert into table3 values('gao',1111.11);
insert into table3 values('zhu',5555.55);
commit;
執行統計：
SQL> select STDDEV(distinct sal),STDDEV(all sal),STDDEV(sal) from table3;
STDDEV(DISTINCTSAL) STDDEV(ALLSAL) STDDEV(SAL)
------------------- -------------- -----------
3142.69366257674 2565.998630397 2565.998630
統計方差 VARIANCE([distinct|all]x)
【功能】統計資料表選中行x列的方差。
【參數】all表示對所有的值求方差,distinct隻對不同的值求方差，預設為all
環境：
create table table3(xm varchar(8),sal number(7,2));
insert into table3 values('gao',1111.11);
insert into table3 values('gao',1111.11);
insert into table3 values('zhu',5555.55);
commit;
執行統計：
SQL> select VARIANCE(distinct sal),VARIANCE(all sal),VARIANCE(sal) from table3;
VARIANCE(DISTINCTSAL) VARIANCE(ALLSAL) VARIANCE(SAL)
--------------------- ---------------- -------------
9876523.4568 6584348.9712 6584348.9712 統計查詢所得的行數 count(*|[distinct|all]x)
count(*|[distinct|all]x)
*表示對滿足條件的所有行統計，不管其是否重複或有空值(NULL)
all表示對所有的值統計,預設為all
distinct隻對不同的值統計，
如果有參數distinct或all，需有空格與x(列)隔開，均忽略空值(NULL)。
【參數】x，可為數字、字元、日期型及其它類型的字段
【示例】
環境：
create table table3(xm varchar(8),sal number(7,2));
insert into table3 values('gao',1111.11);
insert into table3 values('gao',1111.11);
insert into table3 values('zhu',5555.55);
insert into table3 values('',1111.11);
insert into table3 values('zhu',0);
SQL> select count(*),count(xm),count(all xm),count(distinct sal),count(all sal),count(sal),sum(1) from table3;
COUNT(*) COUNT(XM) COUNT(ALLXM) COUNT(DISTINCTSAL) COUNT(ALLSAL) COUNT(SAL) SUM(1)
---------- ---------- ------------ ------------------ ------------- ---------- ----------
5 4 4 3 5 5 5 統計最大值 MAX([distinct|all]x)
【功能】統計資料表選中行x列的最大值。
【參數】all表示對所有的值求最大值,distinct隻對不同的值求最大值，預設為all
【參數】x，可為數字、字元或日期型字段
【傳回】對應x字段類型
環境：
create table table3(xm varchar(8),sal number(7,2));
insert into table3 values('gao',1111.11);
insert into table3 values('gao',1111.11);
insert into table3 values('zhu',5555.55);
insert into table3 values('',1111.11);
insert into table3 values('zhu',0);
commit;
SQL> select MAX(distinct sal),MAX(xm) from table3;
MAX(DISTINCTSAL) MAX(XM)
---------------- --------
5555.55 zhu
統計最小值 MIN([distinct|all]x)
【功能】統計資料表選中行x列的最小值。
【參數】all表示對所有的值求最小值,distinct隻對不同的值求最小值，預設為all
如果有參數distinct或all，需有空格與x(列)隔開。
【參數】x，可為數字、字元或日期型字段
【傳回】對應x字段類型
注：字元型字段，将忽略空值(NULL)
環境：
create table table3(xm varchar(8),sal number(7,2));
insert into table3 values('gao',1111.11);
insert into table3 values('gao',1111.11);
insert into table3 values('zhu',5555.55);
insert into table3 values('',1111.11);
insert into table3 values('zhu',0);
SQL> select MIN(distinct sal),MIN(xm),MIN(distinct xm),MIN(all xm) from table3;
MIN(DISTINCTSAL) MIN(XM) MIN(DISTINCTXM) MIN(ALLXM)
---------------- -------- --------------- ----------
0 gao gao gao 其它函數
為空值指派 nvl() nvl2()
nvl()
【文法】NVL (expr1, expr2) 【功能】若expr1為NULL，傳回expr2；expr1不為NULL，傳回expr1。
注意兩者的類型要一緻
nvl2():
【文法】NVL2 (expr1, expr2, expr3)
【功能】expr1不為NULL，傳回expr2；expr2為NULL，傳回expr3。
expr2和expr3類型不同的話，expr3會轉換為expr2的類型
COALESCE函數
C,此表達式的功能為傳回第一個不為空的表達式，如果都為空則傳回空值。
【文法】OALESCE(表達式1,表達式2,…,表達式n)，n>=2
【功能】傳回第一個不為空的表達式，如果都為空則傳回空值
所有表達式必須為同一類型或者能轉換成同一類型
SQL> select coalesce(null ,1,2,3) from dual;
COALESCE(NULL,1,2,3)
--------------------
1
SQL> select coalesce(88,3,4,5,99) from dual;
COALESCE(88,3,4,5,99)
---------------------
88
SQL> select coalesce(null,null,null,null) from dual;
COALESCE(NULL,NULL,NULL,NULL)
-----------------------------
SQL> 
條件取值 decode
decode(條件,值1,翻譯值1,值2,翻譯值2,…值n,翻譯值n,預設值)
【功能】根據條件傳回相應值
【參數】c1, c2, …,cn,字元型/數值型/日期型，必須類型相同或null 注：值1……n
不能為條件表達式,這種情況隻能用case when then end解決
·含義解釋：
decode(條件,值1,翻譯值1,值2,翻譯值2,...值n,翻譯值n,預設值) 該函數的含義如下：
IF 條件=值1 THEN
RETURN(翻譯值1)
ELSIF 條件=值2 THEN
RETURN(翻譯值2)
……
ELSIF 條件=值n THEN
RETURN(翻譯值n)
ELSE
RETURN(預設值)
END IF
或者：
when case 條件=值1 THEN
ElseCase 條件=值2 THEN
ElseCase 條件=值n THEN
END
【示例】
·使用方法：
1、比較大小
select decode(sign(變量1-變量2),-1,變量1,變量2) from dual; –取較小值
sign()函數根據某個值是0、正數還是負數，分别傳回0、1、-1
例如：
變量1=10，變量2=20
則sign(變量1-變量2)傳回-1，decode解碼結果為“變量1”，達到了取較小值的目的。
2、表、視圖結構轉化
現有一個商品銷售表sale，表結構為：
month char(6) --月份
sell number(10,2) --月銷售金額
現有資料為：
200001 1000
200002 1100
200003 1200
200004 1300
200005 1400
200006 1500
200007 1600
200101 1100
200202 1200
200301 1300 year char(4) --年份
month1 number(10,2) --1月銷售金額
month2 number(10,2) --2月銷售金額
month3 number(10,2) --3月銷售金額
month4 number(10,2) --4月銷售金額
month5 number(10,2) --5月銷售金額
month6 number(10,2) --6月銷售金額
month7 number(10,2) --7月銷售金額
month8 number(10,2) --8月銷售金額
month9 number(10,2) --9月銷售金額
month10 number(10,2) --10月銷售金額
month11 number(10,2) --11月銷售金額
month12 number(10,2) --12月銷售金額 create or replace view
v_sale(year,month1,month2,month3,month4,month5,month6,
month7,month8,month9,month10,month11,month12)
as
select
substrb(month,1,4),
sum(decode(substrb(month,5,2),'01',sell,0)),
sum(decode(substrb(month,5,2),'02',sell,0)),
sum(decode(substrb(month,5,2),'03',sell,0)),
sum(decode(substrb(month,5,2),'04',sell,0)),
sum(decode(substrb(month,5,2),'05',sell,0)),
sum(decode(substrb(month,5,2),'06',sell,0)),
sum(decode(substrb(month,5,2),'07',sell,0)),
sum(decode(substrb(month,5,2),'08',sell,0)),
sum(decode(substrb(month,5,2),'09',sell,0)),
sum(decode(substrb(month,5,2),'10',sell,0)),
sum(decode(substrb(month,5,2),'11',sell,0)),
sum(decode(substrb(month,5,2),'12',sell,0))
from sale
group by substrb(month,1,4); ![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)