SQL 通用函数

2024 年 8 月 29 日 | 阅读 11 分钟

今天，我们将了解结构化查询语言（SQL）中的一些新函数。我们将学习的几个函数是

NVL()
COALESCE()
NVL2()
NANVL()
DECODE()yb
LNNVL()

NVL()

这是结构化查询语言（SQL）广泛使用的一项 SQL 功能。

此函数仅接受两个输入值。如果输入值超过 2，则会返回错误。在函数内搜索时，此函数会返回第一个非 NULL 值。

如果任何一个或两个参数为 NULL，此函数将没有输出。

空白区域表示开发者创建的 SQL 表中存在 NULL 值。

输入数据类型包括整数、浮点数、字符串、字符输入等。

语法

示例查询

查询 1

查询输出

NVL(15,20)
_ _ _ _ _ _
      15

查询 2

查询输出

NVL(NULL,115)
_ _ _ _ _ _ _ _
    115

查询 3

查询输出

NVL(1.027584 ,1.0275384)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ 

       1.027584

查询 4

查询输出

NVL(NULL,7.0124375)
_ _ _ _ _ _ _ _ _ _ _ _
     7.0214375

查询 5

查询输出

NVL
_ _ _
JAMES

查询 6

查询输出

NVL(
_ _ _ _
ANDERSON

查询 7

查询输出

COMMISSION_PERCENT
_ _ _ _ _ _ _ _ _ _ _ _ _ _
    .16
    .14
    .05


    .385

8 rows selected.

查询 8

查询输出

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() 类似。唯一的区别是它现在接受大于两个的输入。返回第一个非 NULL 输入元素。

可以使用任何形式的输入数据。输入可能采取整数、浮点数、字符串、字符、数字等形式。

语法

COALESCE(input value 1 , input value 2 , input value 3 , . . . . . . . . . . , input value n)

示例查询

查询 1

查询输出

COALESCE(NULL,1)
_ _ _ _ _ _ _ _ _ _ _
     15

查询 2

查询输出

COALESCE(16,29,32)
_ _ _ _ _ _ _ _ _ _ _
      16

查询 3

查询输出

COALESCE(NULL,29,32)
_ _ _ _ _ _ _ _ _ _ _ _ 
        29

查询 4

查询输出

COALESCE(NULL,NULL,32)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
        32

查询 5

查询输出

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 解释

NVL2()

这是结构化查询语言（SQL）广泛使用的一项 SQL 功能。

此函数只能存储三个输入值。如果输入值超过三个，则会返回错误。在函数内搜索时，此函数会返回第一个非 NULL 值。

如果第一个值为非 NULL，则返回第二个值。
如果第一个值为 NULL 且第二个值为非 NULL，则返回第三个值。
返回的值可能为 NULL。
此功能在 SQL 中的工作方式与 NVL() 相同。
如果任何一个或两个参数为 NULL，此函数将没有输出。
输入数据类型包括整数、浮点数、字符串、字符输入等。

语法

示例

查询 1

查询输出

NVL2(11,21,13)
_ _ _ _ _ _ _ _
    21

查询 2

查询输出

NVL2(62,62,63)
_ _ _ _ _ _ _ _ _
    62

查询 3

查询输出

NVL2(4,2,63)
_ _ _ _ _ _ _ _ 
    2

查询 4

查询输出

NVL2(4,NULL,63)
_ _ _ _ _ _ _ _ _

查询 5

查询输出

NVL2(NULL,NULL,63)
_ _ _ _ _ _ _ _ _ _ _ _
       63

查询 6

查询输出

NVL2(NULL,89,63)
_ _ _ _ _ _ _ _ _ _ _
     63

查询 7

查询输出

NVL2('JA
_ _ _ _ _ _
ANDERSON

查询 8

查询输出

NVL
_ _ _
179

查询 9

查询输出

NVL2('NU
--------
ANDERSON

查询 10

查询输出

NVL2(56.2,35.6,23.4)
--------------------
     35.6

查询 11

查询输出

NVL2(NULL,35.6,23.4)
--------------------
       23.4

查询 12

查询输出

NVL2(NULL,NULL,23.459)
----------------------
        23.459

查询 13

查询输出

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

在输入值 n2 为 NaN（非数字）的备选情况下，此函数 NANVL() 返回 n1；在 n2 不是 NaN 的情况下，返回 n2。使用此函数，只能使用 BINARY FLOAT 或 BINARY DOUBLE 类型的浮点数。

该函数可以隐式地将任何输入数据类型（无论是数字还是非数字）转换为数字数据类型。

如果参数是 BINARY FLOAT，则方法返回 BINARY DOUBLE 值；否则，它返回与参数匹配的数字数据类型。

语法

示例查询

查询 1

查询输出

   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（打印表内容）

查询输出

   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

查询输出

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

语法

表查询 1

表查询输出

 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

查询输出

SID 	 SNAME             SAL
_ _ _   _ _ _ _ _     _ _ _ _ _
3 	Sir                14

查询 2

查询输出

SID 	 SNAME             SAL
_ _ _   _ _ _ _ _     _ _ _ _ _
 1 	Dhoni           12
2 	DK              6.25
4 	KL              16
5	Pant            16
6 	Yuzi            6.25
7 	SKY             12

表查询

示例表查询输出

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

查询输出

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

查询输出

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

下一主题7 个学习 SQL 的理由 2023

← 上一步下一步 →

SQL 通用函数

NVL()

COALESCE()

NVL2()

NANVL()

DECODE

LNNVL()

联系信息

关注我们

教程

面试题

在线编译器

Python

Java

.Net Framework

AI, ML and Data Science

Cloud Technology

B.Tech and MCA

Web Technology

PHP

Software Testing

Technical Interview

Java Interview

Python

Web Interview

Database Interview

B.Tech / MCA

Important Interview

Software Testing Interview

Company Interviews

Online Compilers

Multiple Choice Questions

SQL 教程

SQL 数据库

SQL 表

SQL Select

SQL 子句

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL 键

Interview

SQL 多选题

SQL 注入

区别

PL/SQL 教程

其他

SQL 字符串函数

测验

SQL 通用函数

NVL()

COALESCE()

NVL2()

NANVL()

DECODE

LNNVL()

相关帖子

什么是 SQL

SQL 中的 SUM 函数

SQL CRUD 操作

SQL 中的 POWER 函数

如何使用 SQL 中的 UNION

SQL 练习查询

SQL 中的 SIGN 函数

SQL 中的 SUBSTRING 函数

SQL 语言

SQL 中的 OCTET_LENGTH 函数

订阅 Tpoint Tech

联系信息

关注我们

教程

面试题

在线编译器