Oracle常见函数大全

共计 28114 个字符，预计需要花费 71 分钟才能阅读完成。

概述

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 的最大整数值
【参数】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 为数学常量）

【关系】z=power(x,y), 则 y =1/log(z,x) (条件 z,x>0)

---

返回以 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 不为整数则截取 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])
【功能】返回字符串并将字符串的变为小写;
【参数】x 字符型表达式
【参数】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])
【功能】返回字符串并将字符串的转换为大写;
【参数】x 字符型表达式
【参数】Nls_param 可选，指定排序的方式(nls_sort=)。
SCHINESE_RADICAL_M（部首、笔画）
SCHINESE_STROKE_M（笔画、部首 SCHINESE_PINYIN_M（拼音））
【返回】字符型

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 个字符计算
【参数】
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置, 默认为 1
J 第 J 次出现的位置, 默认为 1
【返回】数值

SQL> select instr('重庆某软件公司','某',1,1),instrb('重庆某软件公司','某',1,1) instring from dual;

INSTR('重庆某软件公司','某',1,   INSTRING
------------------------------ ----------
                             3          5

---

返回字符串的长度（全角算 1 字符）length(c1)

LENGTH(c1)
【功能】返回字符串的长度;
【说明】多字节符(汉字、全角符等)，按 1 个字符计算
【参数】C1 字符串
【返回】数值型

SQL>  select length('小工匠'),length('北京市海锭区'),length('北京 TO_CHAR') from dual;

LENGTH('小工匠') LENGTH('北京市海锭区') LENGTH('北京 TO_CHAR')
---------------- ---------------------- ---------------------
               3                      6                     9

---

返回字符串的长度（全角算 2 字符）lengthb(c1)

LENGTH(c1)
【功能】返回字符串的长度;
【说明】多字节符(汉字、全角符等)，按 2 个字符计算
【参数】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)
【功能】返回字符串的长度;
【说明】多字节符(汉字、全角符等)，按 1 个字符计算
【参数】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 时为止
【参数】C1 字符串
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 追加后字符总长度
c2 追加字符串, 默认为空格
【返回】字符型
【说明】如果 c1 长度大于 n，则返回 c1 左边 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])
【功能】删除左边出现的字符串
【参数】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])
【功能】删除右边出现的字符串
【参数】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])
【功能】取子字符串
【说明】多字节符(汉字、全角符等)，按 1 个字符计算
【参数】在字符表达式 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])
【功能】取子字符串
【说明】多字节符(汉字、全角符等)，按 2 个字符计算
【参数】在字符表达式 c1 里，从 n1 开始取 n2 个字符; 若不指定 n2, 则从第 y 个字符直到结束的字串.
【返回】字符型, 如果从多字符右边开始, 则用空格表示。

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)
【功能】将字符表达式值中，指定字符替换为新字符
【说明】多字节符 (汉字、全角符等)，按 1 个字符计算
【参数】
c1 希望被替换的字符或变量
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（即当前日期)
【参数表】：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  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) 的日期
【参数】：d1 日期型,c1 为字符型(参数)，c1 默认为 j（即当前日期)
【参数表】：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

【说明】
在 Oracle 中，每一条记录都有一个 rowid，rowid 在整个数据库中是唯一的，rowid 确定了每条记录是在 Oracle 中的哪一个数据文件、块、行上。
在重复的记录中，可能所有列的内容都相同，但 rowid 不会相同.

---

字符串语言字符集转换 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 转化为数字型
【参数】c2,c3, 字符型，参照 to_char()
【返回】数字串
【相反】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)
【功能】将字符串中的半角转化为全角
【参数】c1, 字符型
【返回】字符串

SQL> select to_multi_byte('高 A') text from dual;

test
--
高A

---

全角转化为半角 to_single_byte(c1)

to_single_byte(c1)
【功能】将字符串中的全角转化为半角
【参数】c1, 字符型
【返回】字符串

SQL> select to_multi_byte('高A') text from dual;

test
----
高 A 

---

字符集名称转换为 ID nls_charset_id(c1)

nls_charset_id(c1)
【功能】返回字符集名称参应 id 值
【参数】c1, 字符型
【返回】数值型

SQL> select nls_charset_id('zhs16gbk') from dual;

NLS_CHARSET_ID('ZHS16GBK')
--------------------------
                       852

---

字符集 ID 转换为名称 nls_charset_name(n1)

nls_charset_name(n1)
【功能】返回字符集名称参应 id 值
【参数】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
如果有参数 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);
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
如果有参数 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);
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
如果有参数 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);
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)
【功能】统计数据表选中行 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
如果有参数 distinct 或 all，需有空格与 x(列)隔开。
【参数】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 的类型

条件取值 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
RETURN(翻译值 1)
ElseCase 条件 = 值 2 THEN
RETURN(翻译值 2)
……
ElseCase 条件 = 值 n THEN
RETURN(翻译值 n)
ELSE
RETURN(缺省值)
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 月销售金额

结构转化的 SQL 语句为：

 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);

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-11/136844.htm