极客教程SQL通用函数
今天，我们将了解结构化查询语言（SQL）中的一些新函数。我们要学习的几个函数是。
- NVL()
- COALESCE()
- NVL2()
- NANVL()
- DECODE()yb
- LNNVL()
NVL()
这是结构化查询语言大量使用的SQL功能之一（SQL）。
这个函数只接受两个输入值。如果提供的值超过2，将返回一个错误。当在函数中搜索时，这个函数返回第一个NOT NULL值。
如果任何一个或两个参数都是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()。唯一的区别是，它现在接受大于两个的输入。第一个NOT NULL输入元素被返回。
可以使用任何形式的输入数据。输入的形式可以是整数、浮点数、字符串、字符、数字等等。
语法
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 解释
Here, the output of the query is NULL.
This is because NULL is a word specified under the quotes.
So, because of this factor NULL is considered as String but not as a NULL value.
So, that was the reason why NULL was printed as an output.
查询8
SQL> select COALESCE(NULL , ' ' , 'James Anderson' , 'Ricky Ponting' , 'NULLED') from dual;
查询输出
C
_
查询8 解释
Here, the Output is just a blank space, but not a NULL value.
NVL2()
这是结构化查询语言大量使用的SQL功能之一（SQL）。
在这个函数中只能存储三个输入值。如果提供的值超过三个，将返回一个错误。在函数中搜索时，该函数返回第一个不为空的值。
- 如果第一个值为NOT NULL，则返回第二个值。
- 如果第一个值是NULL并且第二个值不是NULL，则返回第三个值。
- 被返回的值有可能是NULL。
- 这个功能的操作方式与SQL中的NVL()相同。
- 如果任何参数或两个参数都是NULL，这个函数没有输出。
- 输入的数据类型包括整数、浮点数、字符串、字符输入等等。
语法
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中采用的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