「oracle 函数」Oracle函数

oracle 函数

概述

oracle 函数系列：

Oracle常见函数大全

Oracle-分析函数之连续求和sum(…) over(…)

Oracle-分析函数之排序值rank()和dense_rank()

Oracle-分析函数之排序后顺序号row_number()

Oracle-分析函数之取上下行数据lag()和lead()

数值型函数

返回绝对值 abs(x)

ABS(X)
【功能】返回x的绝对值
【参数】x，数字型表达式
【返回】数字
1
2
3
4

sql> select abs(100) , abs(-100) from dual ;

  ABS(100)  ABS(-100)
---------- ----------
       100        1001
2
3
4
5

返回正负值 sign(x)

sign(x)
【功能】返回x的正负值
【参数】x，数字型表达式
【返回】数字，若为正值返回1，负值返回-1，0返回0
1
2
3
4

SQL> select sign(100), sign(-100),sign(0) from dual ;

 SIGN(100) SIGN(-100)    SIGN(0)
---------- ---------- ----------
         1         -1          01
2
3
4
5

返回较大的最小整数 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          01
2
3
4
5

返回较小的最大整数 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          01
2
3
4
5

返回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.051
2
3
4
5

【相近】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.049787061
2
3
4
5

【相近】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
1
2
3
4
5
6
7

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                       31
2
3
4
5

【相近】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
1
2
3
4
5
6

【相近】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
1
2
3
4
5
6

返回四舍五入后的值 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
1
2
3
4
5
6
7

【相近】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               55551
2
3
4
5
6

【相近】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.162277661
2
3
4
5
6

三角函数

SIN(x)

【功能】返回一个数字的正弦值

SQL> select sin(1.57079) from dual;

SIN(1.57079)
------------
0.99999999991
2
3
4
5

SIGH(x)

【功能】返回双曲正弦的值

SQL> select sin(20),sinh(20) from dual;

   SIN(20)   SINH(20)
---------- ----------
0.91294525 242582597.1
2
3
4
5

COS(x)

【功能】返回一个给定数字的余弦

SQL> select cos(-3.1415927) from dual;

COS(-3.1415927)
---------------
-0.999999999999
1
2
3
4
5
6

COSH(x)

【功能】返回一个数字反余弦值

SQL> select cosh(20) from dual;

  COSH(20)
----------
242582597.1
2
3
4
5

TAN

【功能返回数字的正切值

SQL> select tan(20),tan(10) from dual;

   TAN(20)    TAN(10)
---------- ----------
2.23716094 0.648360821
2
3
4
5

TANH

【功能返回数字n的双曲正切值

SQL> select tanh(20),tan(20) from dual;

  TANH(20)    TAN(20)
---------- ----------
         1 2.237160941
2
3
4
5

ASIN(x)

【功能】给出反正弦的值

【示例】select asin(0.5) from dual;

返回：0.52359878

ACOS(x)

【功能】给出反余弦的值

SQL> select asin(0.5) from dual;

 ASIN(0.5)
----------
0.52359877
1
2
3
4
5
6

ATAN(x)

【功能】返回一个数字的反正切值

SQL>  select atan(1) from dual;

   ATAN(1)
----------
0.785398161
2
3
4
5

字符型函数

返回字符表达式最左端字符的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      519021
2
3
4
5

【说明】在ASCII（）函数中，纯数字的字符串可不用‘’括起来，但含其它字符的字符串必须用‘’括起来使用，否则会出错。

如果最左端是汉字，只取汉字最左半边字符的ASCII 码

【互反函数】：chr()

返回ASCII为x的字符 chr()

CHR(n1)
【功能】：将ASCII 码转换为字符。
【参数】：n1,为0 ~ 255，整数
【返回】：字符型


SQL>  select chr(54740) zhao,chr(65) chr65 from dual;

ZHAO CHR65
---- -----
赵   A1
2
3
4
5
6

【互反函数】：ASCII

连接两个字符串 concat(c1,c2)

CONCAT(c1,c2)
【功能】连接两个字符串
【参数】c1，c2 字符型表达式
【返回】字符型
同：c1||c2

SQL> select concat('010-','88888888')||'转3456' 电话号码 from dual;

电话号码
------------------
010-88888888转34561
2
3
4
5

把每个单词的首字个字母变成大写 initcap(c1)

INITCAP(c1)
【功能】返回字符串并将字符串的第一个字母变为大写，其它字母小写;
【参数】c1字符型表达式
【返回】字符型

SQL> select initcap('smith abc aBC') upp from dual ;

UPP
-------------
Smith Abc Abc1
2
3
4
5

把整个字符串转换为小写 lower(c1)

LOWER(c1)
【功能】：将字符串全部转为小写
【参数】：c1，字符表达式
【返回】：字符型

SQL> select lower('AaBbCcDd')AaBbCcDd from dual;

AABBCCDD
--------
aabbccdd1
2
3
4
5

把整个字符串转换为大写 upper(c1)

UPPER(c1)
【功能】将字符串全部转为大写
【参数】c1，字符表达式
【返回】字符型


SQL>  select upper('AaBbCcDd') upper from dual;

UPPER
--------
AABBCCDD
1
2
3
4
5
6
7

把每个单词首个字母变为大写 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 E1
2
3
4
5


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 E1
2
3
4
5
6

把整个字符串转换为小写 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 e1
2
3
4
5

把整个字符串转换为大写 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 E1
2
3
4
5

字符串中搜索字符位置(全角算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
----------
         91
2
3
4
5

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

INSTR('重庆某软件公司','某',1,   INSTRING
------------------------------ ----------
                             3          51
2
3
4
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          51
2
3
4
5

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

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

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

LENGTH('小工匠') LENGTH('北京市海锭区') LENGTH('北京TO_CHAR')
---------------- ---------------------- ---------------------
               3                      6                     91
2
3
4
5

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

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

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

LENGTHB('小工匠') LENGTHB('北京市海锭区') LENGTHB('北京TO_CHAR')
----------------- ----------------------- ----------------------
                6                      12                     111
2
3
4
5

返回字符串的长度（其他）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                     91
2
3
4
5

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               21
2
3
4
5

在左边添加字符 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,'*')
-----------------
********杨1
2
3
4
5

不够字符则用*来填满

【相似】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*1
2
3
4
5

【相似】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 jiang1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

删除右边字符 rtrim(c1,[,c2])

RTRIM(c1,[,c2])
【功能】删除右边出现的字符串
【参数】C1 字符串
c2 追加字符串,默认为空格
【返回】字符型

SQL>  select RTRIM(' xiao gong jiang XXXX','X') text from dual;

TEXT
-----------------
 xiao gong jiang1
2
3
4
5

【相似】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 you1
2
3
4
5

字符串语音表示形式 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           T0001
2
3
4
5

SQL> select soundex('cap'),soundex('cup') from dual ;

SOUNDEX('CAP') SOUNDEX('CUP')
-------------- --------------
C100           C1001
2
3
4
5


SQL> select soundex('house'),soundex('horse') from dual ;

SOUNDEX('HOUSE') SOUNDEX('HORSE')
---------------- ----------------
H200             H6201
2
3
4
5
6

截取字符串（全角算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
1
2
3
4
5
6

截取字符串（全角算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                    手机13012341
2
3
4
5
6

替换子字符 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北京人                        京男士人
1
2
3
4
5
6
7
8
9
10

删除左边和右边字符串 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                xiaoXXgong1
2
3
4
5

日期函数

返回系统当前日期 sysydate

sysdate
【功能】：返回当前日期。
【参数】：没有参数，没有括号
【返回】：日期

SQL> select sysdate from dual;

SYSDATE
-----------
2016-5-25 01
2
3
4
5

返回指定月数的日期 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:591
2
3
4
5
6

返回本月最后一天的日期 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:01
2
3
4
5

返回两个日期间隔月数 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                             121
2
3
4
5

返回时区的对应时间 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:561
2
3
4
5
6

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
1
2
3
4
5
6
7

四舍五入后的日期第一天 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-11
2
3
4
5
6
7
8
9
10

返回日期所在期间的第一天 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-11
2
3
4
5
6
7
8
9
10

返回下周某一天的日期 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 01
2
3
4
5
6
7
8
9
10
11
12

提取时间日期中的数据 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       20011
2
3
4
5
6
7
8
9
10
11
12

SQL> select extract (YEAR from date '2001-2-16' ) from dual;

EXTRACT(YEARFROMDATE'2001-2-16
------------------------------
                          20011
2
3
4
5

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       20161
2
3
4
5
6
7
8
9

返回会话中的时间和日期 localtimestamp

localtimestamp
【功能】：返回会话中的日期和时间
【参数】：没有参数，没有括号
【返回】：日期

SQL> select localtimestamp from dual;

LOCALTIMESTAMP
--------------------------------------------------------------------------------
25-MAY-16 06.55.31.054928 PM1
2
3
4
5

返回当前会话时区中的当前日期和时间 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:001
2
3
4
5
6

返回数据库时区设置 dbtimezone

dbtimezone
【功能】：返回时区
【参数】：没有参数，没有括号
【返回】：字符型


SQL> select dbtimezone from dual ;

DBTIMEZONE
----------
+08:001
2
3
4
5
6

返回当前会话时区 sessiontimezone

SESSIONTIMEZONE
【功能】：返回会话时区
【参数】：没有参数，没有括号
【返回】：字符型

SQL> select dbtimezone ,sessiontimezone from dual ;

DBTIMEZONE SESSIONTIMEZONE
---------- ---------------------------------------------------------------------------
+08:00 +08:001
2
3
4
5

变动日期时间数值 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

转换函数

字符串转为rowid值 chartorowid(c1)

chartorowid(c1) 。。
【功能】转换varchar2类型为rowid值
【参数】c1,字符串，长度为18的字符串，字符串必须符合rowid格式
【返回】返回rowid值

SQL> SELECT chartorowid('AAAADeAABAAAAZSAAA') FROM DUAL;

CHARTOROWID('AAAADEAABAAAAZSAA
------------------------------
AAAADeAABAAAAZSAAA1
2
3
4
5

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

rowid的值转换为字符串 rowidtochar(rowid)

ROWIDTOCHAR(rowid) 。。
【功能】转换rowid值为varchar2类型
【参数】rowid,固定参数
【返回】返回长度为18的字符串

SQL> select rowidtochar(rowid) from dual ;

ROWIDTOCHAR(ROWID)
------------------
AAAAECAABAAAAgqAAA
1
2
3
4
5
6

【说明】
在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
----------
strutz1
2
3
4
5

SQL> select convert('strutz','we8hp','f7dec')  as conversion from dual;

CONVERSION
----------
strutz1
2
3
4
5

十六进制构成的字符串转换为二进制 HEXTORAW(c1)

HEXTORAW(c1)
【功能】将一个十六进制构成的字符串转换为二进制
【参数】c1,十六进制的字符串
【返回】字符串

SQL> select HEXTORAW('A123')  from dual;

HEXTORAW('A123')
----------------
A1231
2
3
4
5

二进制构成的字符串转换为十六进制 rawtohex(c1)

RAWTOHEX(c1)
【功能】将一个二进制构成的字符串转换为十六进制
【参数】c1,二进制的字符串
【返回】字符串

SQL> select rawtohex('A123') from dual ;

RAWTOHEX('A123')
----------------
413132331
2
3
4
5

将日期或数据转换为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'
1
2
3
4
5
6

【说明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') 年1
2
3
4
5
6
7

【示例】带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','
------------------------------
thursday1
2
3
4
5

字符串转换为日期型 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
1
2
3
4
5
6
7
8
9
10
11

字符串转换为字符型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.051
2
3
4
5

转换为16进制。 
TO_CHAR(100,'XX')= 64 

SQL> select TO_CHAR(100,'XX') from dual ;

TO_CHAR(100,'XX')
-----------------
 641
2
3
4
5
6
7
8

半角转化为全角TO_MULTI_BYTE(c1)

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

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

test
--
高Ａ1
2
3
4
5

全角转化为半角to_single_byte(c1)

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

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

test
----
高A1
2
3
4
5

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

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

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

NLS_CHARSET_ID('ZHS16GBK')
--------------------------
                       8521
2
3
4
5

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

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

SQL> select nls_charset_name(852) from dual;

NLS_CHARSET_NAME(852)
---------------------
ZHS16GBK1
2
3
4
5

聚组函数

统计平均值 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.591
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

统计合计值 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.771
2
3
4
5
6
7
8
9
10
11
12
13

统计标准误差 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

统计方差 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.97121
2
3
4
5
6
7
8
9
10
11
12
13

统计查询所得的行数 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);
1
2
3
4
5
6
7
8
9

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          51
2
3
4
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;



1
2
3
4
5
6
7
8
9
10
11
12

SQL> select MAX(distinct sal),MAX(xm) from table3;

MAX(DISTINCTSAL) MAX(XM)
---------------- --------
         5555.55 zhu
1
2
3
4
5
6

统计最小值 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);
1
2
3
4
5
6
7
8

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         gao1
2
3
4
5

其它函数

为空值赋值 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> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

所有表达式必须为同一类型或者能转换成同一类型

这里写图片描述

条件取值 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,缺省值)　1

该函数的含义如下：　　

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)　　　--月销售金额　1
2

现有数据为：　　

　200001　　1000
　　200002　　1100
　　200003　　1200
　　200004　　1300
　　200005　　1400
　　200006　　1500
　　200007　　1600
　　200101　　1100
　　200202　　1200
　　200301　　13001
2
3
4
5
6
7
8
9
10

想要转化为以下结构的数据：　　

　　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月销售金额1
2
3
4
5
6
7
8
9
10
11
12
13

结构转化的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);1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20