极客笔记SQL 通用函数
今天，我们将了解一些在结构化查询语言（SQL）中存在的新函数。我们将要学习的几个函数包括：
- NVL()
- COALESCE()
- NVL2()
- NANVL()
- DECODE()yb
- LNNVL()
NVL()
这是SQL功能之一，结构化查询语言（SQL）广泛使用它。
此函数仅接受两个输入值。如果提供的值超过2个，将返回错误。在函数内搜索时，此函数返回第一个非空值。
如果任何一个或两个参数为NULL，则此函数没有输出。
空白区域表示在开发者创建的SQL表中存在NULL值。
输入数据类型包括整数、浮点数、字符串、字符输入等等。
语法
NVL(input value 1 , input value 2)
示例查询
查询1
SQL> select NVL(15 , 20) from dual;
查询输出：
NVL(15,20)
_ _ _ _ _ _
15
查询2
SQL> select NVL(NULL , 115) from dual;
查询结果：
NVL(NULL,115)
_ _ _ _ _ _ _ _
115
查询3
SQL> select NVL(1.027584 , 1.0275384) from dual;
查询输出：
NVL(1.027584 ,1.0275384)
_ _ _ _ _ _ _ _ _ _ _ _ _ _
1.027584
查询 4
SQL> select NVL(NULL , 7.0124375) from dual;
查询输出：
NVL(NULL,7.0124375)
_ _ _ _ _ _ _ _ _ _ _ _
7.0214375
查询 5
SQL> select NVL('JAMES' , 'ANDERSON') from dual;
查询输出：
NVL
_ _ _
JAMES
查询6
SQL> select NVL(NULL , 'ANDERSON') from dual;
查询输出:
NVL(
_ _ _ _
ANDERSON
查询 7
SQL> select Commission_percent from salary;
查询输出:
COMMISSION_PERCENT
_ _ _ _ _ _ _ _ _ _ _ _ _ _
.16
.14
.05
.385
8 rows selected.
查询8
SQL> select select NVL(Commission_percent,0) from salary;
查询输出：
NVL(COMMISSION_PERCENT,0)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
.16
.14
.05
0
0
.385
0
0
8 rows selected.
查询9
SQL> select ID_NO , NAME , SALARY , NVL(COMMISSION_PERCENT , 0) ,
SALARY +(SALARY * NVL(COMMISSION_PERCENT, 0))
TOTAL FROM SALARY;
查询输出：
ID_NO NAME SALARY NVL(COMMISSION_PERCENT,0) TOTAL
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1 Joe Burns 52600 .16 61016
2 Ross Boss 93700 .14 106818
3 Steven Smith 75000 .05 78750
4 Chris Lynn 35000 0 35000
5 Mitch Starc 39900 0 39900
6 Ricky Ponting 230000 .385 318550
7 Pat Cummins 23000 0 23000
8 Scott Boland 10000 0 10000
8 rows selected.
查询10
SQL> select ID_NO , NAME , SALARY , NVL(COMMISSION_PERCENT , 0) ,
(SALARY * 12) +(SALARY * NVL(COMMISSION_PERCENT, 0))
YEAR_SAL FROM SALARY;
查询结果输出:
ID_NO NAME SALARY NVL(COMMISSION_PERCENT,0) YEAR_SAL
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1 Joe Burns 52600 .16 639616
2 Ross Boss 93700 .14 1137518
3 Steven Smith 75000 .05 903750
4 Chris Lynn 35000 0 420000
5 Mitch Starc 39900 0 478800
6 Ricky Ponting 230000 .385 2848550
7 Pat Cummins 23000 0 276000
8 Scott Boland 10000 0 120000
8 rows selected.
COALESCE（）
这也是SQL表达式之一。该表达式的功能类似于NVL（）。唯一的区别是它现在可以接受两个以上的输入。将返回第一个非空输入元素。
可以使用任何形式的输入数据。输入可以是整数、浮点数、字符串、字符、数字等。
语法
COALESCE(input value 1 , input value 2 , input value 3 , . . . . . . . . . . , input value n)
示例查询
查询1
SQL> select COALESCE(NULL , 15) from dual;
查询输出：
COALESCE(NULL,1)
_ _ _ _ _ _ _ _ _ _ _
15
查询2
SQL> select COALESCE(16 , 29 ,32) from dual;
查询输出：
COALESCE(16,29,32)
_ _ _ _ _ _ _ _ _ _ _
16
查询3
SQL> select COALESCE(NULL , 29 , 32) from dual;
查询输出：
COALESCE(NULL,29,32)
_ _ _ _ _ _ _ _ _ _ _ _
29
查询4
SQL> select COALESCE(NULL , NULL , 32) from dual;
查询结果输出：
COALESCE(NULL,NULL,32)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _
32
查询5
SQL> select COALESCE(NULL , NULL , NULL, NULL, NULL) from dual;
查询输出：
C
_
查询 6
SQL> select COALESCE(NULL , NULL , NULL , 41 , NULL , 36 , 77 , NULL , 64 , NULL) from dual;
查询输出:
COALESCE(NULL,NULL,NULL,41,NULL,36,77,NULL,64,NULL)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
41
查询 7
SQL> select COALESCE(NULL , 'NULL' , 'James Anderson' , 'Ricky Ponting' , 'NULLED') from dual;
查询输出：
COAL
_ _ _ _
NULL
查询 7 解释
在这里，查询的输出是NULL。
这是因为NULL是在引号下指定的单词。
因此，由于这个因素，NULL被认为是字符串，而不是NULL值。
这就是NULL被打印为输出的原因。
查询 8
SQL> select COALESCE(NULL , ' ' , 'James Anderson' , 'Ricky Ponting' , 'NULLED') from dual;
查询输出：
C
_
查询8解释
这里的输出只是一个空格，而不是NULL值。
NVL2()
这是结构化查询语言（SQL）中广泛使用的一个功能之一。
只能存储三个输入值。如果提供的值超过三个，则会返回错误。在函数内搜索时，此函数将返回第一个非空值。
- 如果第一个值非空，则返回第二个值。
- 如果第一个值为空且第二个值非空，则返回第三个值。
- 返回的值可能为空。
- 这个功能与SQL中的NVL()函数的功能相同。
- 如果任何一个或两个参数为空，则此函数不会输出任何内容。
- 输入数据类型包括整数、浮点数、字符串、字符输入等等。
语法
NVL2(input value 1 , input value 2 , input value 3)
示例
查询1
SQL> select NVL2(11 , 21 , 13) from dual;
查询输出：
NVL2(11,21,13)
_ _ _ _ _ _ _ _
21
查询2
SQL> select NVL2(62 , 62 , 63) from dual;
查询结果：
NVL2(62,62,63)
_ _ _ _ _ _ _ _ _
62
查询3
SQL> select NVL2(4 , 2 , 63) from dual;
查询输出：
NVL2(4,2,63)
_ _ _ _ _ _ _ _
2
查询4
SQL> select NVL2(4 , NULL , 63) from dual;
查询输出：
NVL2(4,NULL,63)
_ _ _ _ _ _ _ _ _
查询 5
SQL> select NVL2(NULL , NULL , 63) from dual;
查询结果：
NVL2(NULL,NULL,63)
_ _ _ _ _ _ _ _ _ _ _ _
63
查询 6
SQL> select NVL2(NULL , 89 , 63) from dual;
查询结果：
NVL2(NULL,89,63)
_ _ _ _ _ _ _ _ _ _ _
63
查询 7
SQL> select NVL2('James' , 'ANDERSON' , 179) from dual;
查询结果：
NVL2('JA
_ _ _ _ _ _
ANDERSON
查询 8
SQL> select NVL2(NULL , 'ANDERSON' , 179) from dual;
查询结果：
NVL
_ _ _
179
查询9
SQL> select NVL2(' NULL' , 'ANDERSON' , 179) from dual;
查询结果：
NVL2('NU
--------
ANDERSON
查询 10
SQL> select NVL2(56.2 , 35.6 , 23.4) from dual;
查询输出：
NVL2(56.2,35.6,23.4)
--------------------
35.6
查询11
SQL> select NVL2(NULL , 35.6 , 23.4) from dual;
查询输出：
NVL2(NULL,35.6,23.4)
--------------------
23.4
查询 12
SQL> select NVL2(NULL , NULL , 23.459) from dual;
查询结果：
NVL2(NULL,NULL,23.459)
----------------------
23.459
查询13
SQL> select NVL2(NULL , NULL , NULL) from dual;
查询输出：
N
-
进行操作的表格
ID_NO NAME SALARY COMMISSION_PERCENT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1 Joe Burns 52600 .16
2 Ross Boss 93700 .14
3 Steven Smith 75000 .05
4 Chris Lynn 35000
5 Mitch Starc 39900
6 Ricky Ponting 230000 .385
7 Pat Cummins 23000
8 Scott Boland 10000
查询14
SQL> select ID_NO , NAME , SALARY , NVL2(COMMISSION_PERCENT , SALARY , 0) ,
SALARY +(SALARY * NVL2(COMMISSION_PERCENT , 0.2 , 0.1))
WierdSAL FROM SALARY;
查询结果：
ID_NO NAME SALARY NVL2(COMMISSION_PERCENT,SALARY,0) WIERDSAL
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1 Joe Burns 52600 52600 63120
2 Ross Boss 93700 93700 112440
3 Steven Smith 75000 75000 90000
4 Chris Lynn 35000 0 38500
5 Mitch Starc 39900 0 43890
6 Ricky Ponting 230000 230000 276000
7 Pat Cummins 23000 0 25300
8 Scott Boland 10000 0 11000
8 rows selected.
NANVL()
这个方法 NANVL() 在输入值 n2 为 NaN（非数字）时返回 n1，而在 n2 不为 NaN 时返回 n2 的替代值。使用此函数时，只能使用 BINARY FLOAT 或 BINARY DOUBLE 类型的浮点数。
此函数可以隐式转换任何输入数据类型，无论是非数值型还是数值型，都可以转换为数值数据类型。
如果参数是 BINARY FLOAT，则此方法返回一个 BINARY DOUBLE 值；否则，它返回与参数匹配的数值数据类型。
语法
NANVL(input value 1 , input value 2)
示例查询
查询1
SQL> SELECT * FROM NUMBER;
查询输出：
DEC_NUM BIN_DOUBLE BIN_FLOAT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
9273.420 9.273E+003 9.273E+003
插入查询
SQL> INSERT INTO FPT VALUES(0 , 'NaN' , 'NaN');
SQL> INSERT INTO FPT VALUES(10 , 'NaN' , 'NaN');
SQL> INSERT INTO FPT VALUES(100 , 'NaN' , 'NaN');
插入查询结果
1 row created.
1 row created.
1 row created.
查询2（打印表内容）
SQL> SELECT * FROM NUMBER ;
查询结果：
DEC_NUM BIN_DOUBLE BIN_FLOAT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
3563.971 3.564E+003 3.564E+003
0 Nan Nan
10 Nan Nan
100 Nan Nan
查询 3
SQL> SELECT DEC_NUM, BIN_DOUBLE, BIN_FLOAT,
NANVL(DEC_NUM, 0), NANVL(BIN_DOUBLE, 0), NANVL(BIN_FLOAT, 0)
from NUMBER;
查询输出：
DEC_NUM BIN_DOUBLE BIN_FLOAT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
3563.971 3.564E+003 3.564E+003
0 Nan Nan
10 Nan Nan
100 Nan Nan
NANVL(DEC_NUM,0) NANVL(BIN_DOUBLE,0) NANVL(BIN_DOUBLE,0)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
3563.971 3.564E+003 3.564E+003
0 0 0
10 0 0
100 0 0
DECODE
这也是结构化查询语言（SQL）中常见的表达式之一。使用这个解码词组时，会采用IF、ELSE、IF、ELSE梯级样式。根据所提到的条件，这个解码器进行操作。
在这种情况下，任何列出的过程都会起作用。
必须根据提供的数据类型选择输入类型。
语法
DECODE(column name , number 1 to be searched , result 1 to be updated
, number 2 to be searched , result 2 to be updated
, number 3 to be searched , result 3 to be updated
. . . . . . . . .
number n to be searched , result n to be updated , default)
示例查询
查询1
SQL> select * from iplb;
查询输出：
SID SNAME SAL
_ _ _ _ _ _ _ _ _ _ _ _ _
1 Dhoni 12
2 DK 6.25
3 Sir 14
4 KL 16
5 Pant 16
6 Yuzi 6.25
7 SKY 12
查询2
SQL > SELECT Sname, sid, sal,
DECODE(sid, 4, 1.5*sal,
7, 3*sal,
6, 7*sal,
1, 12*sal,
sal)
"FAKE SALARY"
from iplb ;
查询输出：
SNAME SID SAL FAKE SALARY
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Dhoni 1 12 144
DK 2 6.25 6.25
Sir 3 14 14
KL 4 16 24
Pant 5 16 16
Yuzi 6 6.25 43.75
SKY 7 12 36
7 rows selected.
查询3
SQL > SELECT Sid, Sname, Sal ,
DECODE(sid ,1 , 1 * sid * sal ,
2 , 4 * sid * sal ,
3 , 9 * sid * sal ,
4 , 16 * sid * sal ,
5 , 25 * sid * sal ,
6 , 36 * sid * sal ,
7 , 49 * sid * sal ,
sal/2)
"F A K E SALARY"
from iplb ;
查询输出：
SID SNAME SAL F A K E SALARY
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1 Dhoni 12 12
2 DK 6.25 50
3 Sir 14 378
4 KL 16 1024
5 Pant 16 2000
6 Yuzi 6.25 1350
7 SKY 12 4116
LNNVL()
这是SQL中使用的一种函数。它用于改变True和False的值。
LNNVL()函数可以满足一定条件，从而改变情况。
如果条件为SID = 3，则SID! = 3等同于LNNVL(SID = 3)。
语法
LNNVL(Condition)
表查询1
SQL> select * from iplb;
查询结果表格：
SID SNAME SAL
_ _ _ _ _ _ _ _ _ _ _ _ _
1 Dhoni 12
2 DK 6.25
3 Sir 14
4 KL 16
5 Pant 16
6 Yuzi 6.25
7 SKY 12
示例查询
查询 1
SQL> select * from iplb where sid = 3;
查询输出：
SID SNAME SAL
_ _ _ _ _ _ _ _ _ _ _ _ _
3 Sir 14
查询2
SQL> select * from iplb where LNNVL(sid = 3);
查询输出：
SID SNAME SAL
_ _ _ _ _ _ _ _ _ _ _ _ _
1 Dhoni 12
2 DK 6.25
4 KL 16
5 Pant 16
6 Yuzi 6.25
7 SKY 12
表查询
SQL> select * from salary;
示例表查询输出：
ID_NO NAME SALARY COMMISSION_PERCENT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1 Joe Burns 52600 .16
2 Ross Boss 93700 .14
3 Steven Smith 75000 .05
4 Chris Lynn 35000
5 Mitch Starc 39900
6 Ricky Ponting 230000 .385
7 Pat Cummins 23000
8 Scott Boland 10000
8 rows selected.
查询3
SQL> select * from salary where NVL(commission_percent, 0) = 0;
查询输出:
ID_NO NAME SALARY COMMISSION_PERCENT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
4 Chris Lynn 35000 0
5 Mitch Starc 39900 0
7 Pat Cummins 23000 0
8 Scott Boland 10000 0
查询4
SQL> select * from salary where LNNVL(NVL(commission_percent, 0) = 0);
查询输出：
ID_NO NAME SALARY COMMISSION_PERCENT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1 Joe Burns 52600 .16
2 Ross Boss 93700 .14
3 Steven Smith 75000 .05
6 Ricky Ponting 230000 .385