Chapter 04-Using Conversion Functions and Conditional Expressions-General Functions

General Functions

The following functions work with any data ype  and pertain to using nulls:

  • NVL(expr1,expr2)
  • NVL2(expr1,expr2,expr3)
  • NULLIF(expr1,expr2)
  • COALESCE(expr1,expr2,...,exprn)

NVL Function

Converts an null value to an actual value:

  • Data types that can be used are date,character,and number.
  • Data types must match:
    • -NVL(commission_pct,0)
    • -NVL(hire_date,'01-JAN-97')
    • -NVL(job_id,'No Job Yet')

Demo-01:NVL(expr1,expr2)如果expr1为NULL,返回expr2;否则返回expr1

View Code
SQL> SELECT last_name,salary,NVL(commission_pct,0),(salary*12) + (salary*12*NVL(commission_pct,0)) AN_SAL FROM employees;

LAST_NAME                     SALARY NVL(COMMISSION_PCT,0)     AN_SAL
------------------------- ---------- --------------------- ----------
OConnell                        2600                     0      31200
Grant                           2600                     0      31200
Whalen                          4400                     0      52800
Hartstein                      13000                     0     156000
Fay                             6000                     0      72000
Mavris                          6500                     0      78000
Baer                           10000                     0     120000
Higgins                        12008                     0     144096
Gietz                           8300                     0      99600
King                           24000                     0     288000
Kochhar                        17000                     0     204000

LAST_NAME                     SALARY NVL(COMMISSION_PCT,0)     AN_SAL
------------------------- ---------- --------------------- ----------
De Haan                        17000                     0     204000
Hunold                          9000                     0     108000
Ernst                           6000                     0      72000
Austin                          4800                     0      57600
Pataballa                       4800                     0      57600
Lorentz                         4200                     0      50400
Greenberg                      12008                     0     144096
Faviet                          9000                     0     108000
Chen                            8200                     0      98400
Sciarra                         7700                     0      92400
Urman                           7800                     0      93600

LAST_NAME                     SALARY NVL(COMMISSION_PCT,0)     AN_SAL
------------------------- ---------- --------------------- ----------
Popp                            6900                     0      82800
Raphaely                       11000                     0     132000
Khoo                            3100                     0      37200
Baida                           2900                     0      34800
Tobias                          2800                     0      33600
Himuro                          2600                     0      31200
Colmenares                      2500                     0      30000
Weiss                           8000                     0      96000
Fripp                           8200                     0      98400
Kaufling                        7900                     0      94800
Vollman                         6500                     0      78000

LAST_NAME                     SALARY NVL(COMMISSION_PCT,0)     AN_SAL
------------------------- ---------- --------------------- ----------
Mourgos                         5800                     0      69600
Nayer                           3200                     0      38400
Mikkilineni                     2700                     0      32400
Landry                          2400                     0      28800
Markle                          2200                     0      26400
Bissot                          3300                     0      39600
Atkinson                        2800                     0      33600
Marlow                          2500                     0      30000
Olson                           2100                     0      25200
Mallin                          3300                     0      39600
Rogers                          2900                     0      34800

LAST_NAME                     SALARY NVL(COMMISSION_PCT,0)     AN_SAL
------------------------- ---------- --------------------- ----------
Gee                             2400                     0      28800
Philtanker                      2200                     0      26400
Ladwig                          3600                     0      43200
Stiles                          3200                     0      38400
Seo                             2700                     0      32400
Patel                           2500                     0      30000
Rajs                            3500                     0      42000
Davies                          3100                     0      37200
Matos                           2600                     0      31200
Vargas                          2500                     0      30000
Russell                        14000                    .4     235200

LAST_NAME                     SALARY NVL(COMMISSION_PCT,0)     AN_SAL
------------------------- ---------- --------------------- ----------
Partners                       13500                    .3     210600
Errazuriz                      12000                    .3     187200
Cambrault                      11000                    .3     171600
Zlotkey                        10500                    .2     151200
Tucker                         10000                    .3     156000
Bernstein                       9500                   .25     142500
Hall                            9000                   .25     135000
Olsen                           8000                    .2     115200
Cambrault                       7500                    .2     108000
Tuvault                         7000                   .15      96600
King                           10000                   .35     162000

LAST_NAME                     SALARY NVL(COMMISSION_PCT,0)     AN_SAL
------------------------- ---------- --------------------- ----------
Sully                           9500                   .35     153900
McEwen                          9000                   .35     145800
Smith                           8000                    .3     124800
Doran                           7500                    .3     117000
Sewall                          7000                   .25     105000
Vishney                        10500                   .25     157500
Greene                          9500                   .15     131100
Marvins                         7200                    .1      95040
Lee                             6800                    .1      89760
Ande                            6400                    .1      84480
Banda                           6200                    .1      81840

LAST_NAME                     SALARY NVL(COMMISSION_PCT,0)     AN_SAL
------------------------- ---------- --------------------- ----------
Ozer                           11500                   .25     172500
Bloom                          10000                    .2     144000
Fox                             9600                    .2     138240
Smith                           7400                   .15     102120
Bates                           7300                   .15     100740
Kumar                           6100                    .1      80520
Abel                           11000                    .3     171600
Hutton                          8800                   .25     132000
Taylor                          8600                    .2     123840
Livingston                      8400                    .2     120960
Grant                           7000                   .15      96600

LAST_NAME                     SALARY NVL(COMMISSION_PCT,0)     AN_SAL
------------------------- ---------- --------------------- ----------
Johnson                         6200                    .1      81840
Taylor                          3200                     0      38400
Fleaur                          3100                     0      37200
Sullivan                        2500                     0      30000
Geoni                           2800                     0      33600
Sarchand                        4200                     0      50400
Bull                            4100                     0      49200
Dellinger                       3400                     0      40800
Cabrio                          3000                     0      36000
Chung                           3800                     0      45600
Dilly                           3600                     0      43200

LAST_NAME                     SALARY NVL(COMMISSION_PCT,0)     AN_SAL
------------------------- ---------- --------------------- ----------
Gates                           2900                     0      34800
Perkins                         2500                     0      30000
Bell                            4000                     0      48000
Everett                         3900                     0      46800
McCain                          3200                     0      38400
Jones                           2800                     0      33600
Walsh                           3100                     0      37200
Feeney                          3000                     0      36000

107 rows selected.

Demo-02:NVL2(expr1,expr2,expr3)如果expr1为NULL,返回expr3,否则返回expr2;

View Code
SQL> SELECT last_name,salary,commission_pct,NVL2(commission_pct,'SAL+COMM','SAL') incomme FROM employees WHERE department_id IN(50,80);

LAST_NAME                     SALARY COMMISSION_PCT INCOMME
------------------------- ---------- -------------- --------
OConnell                        2600                SAL
Grant                           2600                SAL
Weiss                           8000                SAL
Fripp                           8200                SAL
Kaufling                        7900                SAL
Vollman                         6500                SAL
Mourgos                         5800                SAL
Nayer                           3200                SAL
Mikkilineni                     2700                SAL
Landry                          2400                SAL
Markle                          2200                SAL

LAST_NAME                     SALARY COMMISSION_PCT INCOMME
------------------------- ---------- -------------- --------
Bissot                          3300                SAL
Atkinson                        2800                SAL
Marlow                          2500                SAL
Olson                           2100                SAL
Mallin                          3300                SAL
Rogers                          2900                SAL
Gee                             2400                SAL
Philtanker                      2200                SAL
Ladwig                          3600                SAL
Stiles                          3200                SAL
Seo                             2700                SAL

LAST_NAME                     SALARY COMMISSION_PCT INCOMME
------------------------- ---------- -------------- --------
Patel                           2500                SAL
Rajs                            3500                SAL
Davies                          3100                SAL
Matos                           2600                SAL
Vargas                          2500                SAL
Taylor                          3200                SAL
Fleaur                          3100                SAL
Sullivan                        2500                SAL
Geoni                           2800                SAL
Sarchand                        4200                SAL
Bull                            4100                SAL

LAST_NAME                     SALARY COMMISSION_PCT INCOMME
------------------------- ---------- -------------- --------
Dellinger                       3400                SAL
Cabrio                          3000                SAL
Chung                           3800                SAL
Dilly                           3600                SAL
Gates                           2900                SAL
Perkins                         2500                SAL
Bell                            4000                SAL
Everett                         3900                SAL
McCain                          3200                SAL
Jones                           2800                SAL
Walsh                           3100                SAL

LAST_NAME                     SALARY COMMISSION_PCT INCOMME
------------------------- ---------- -------------- --------
Feeney                          3000                SAL
Russell                        14000             .4 SAL+COMM
Partners                       13500             .3 SAL+COMM
Errazuriz                      12000             .3 SAL+COMM
Cambrault                      11000             .3 SAL+COMM
Zlotkey                        10500             .2 SAL+COMM
Tucker                         10000             .3 SAL+COMM
Bernstein                       9500            .25 SAL+COMM
Hall                            9000            .25 SAL+COMM
Olsen                           8000             .2 SAL+COMM
Cambrault                       7500             .2 SAL+COMM

LAST_NAME                     SALARY COMMISSION_PCT INCOMME
------------------------- ---------- -------------- --------
Tuvault                         7000            .15 SAL+COMM
King                           10000            .35 SAL+COMM
Sully                           9500            .35 SAL+COMM
McEwen                          9000            .35 SAL+COMM
Smith                           8000             .3 SAL+COMM
Doran                           7500             .3 SAL+COMM
Sewall                          7000            .25 SAL+COMM
Vishney                        10500            .25 SAL+COMM
Greene                          9500            .15 SAL+COMM
Marvins                         7200             .1 SAL+COMM
Lee                             6800             .1 SAL+COMM

LAST_NAME                     SALARY COMMISSION_PCT INCOMME
------------------------- ---------- -------------- --------
Ande                            6400             .1 SAL+COMM
Banda                           6200             .1 SAL+COMM
Ozer                           11500            .25 SAL+COMM
Bloom                          10000             .2 SAL+COMM
Fox                             9600             .2 SAL+COMM
Smith                           7400            .15 SAL+COMM
Bates                           7300            .15 SAL+COMM
Kumar                           6100             .1 SAL+COMM
Abel                           11000             .3 SAL+COMM
Hutton                          8800            .25 SAL+COMM
Taylor                          8600             .2 SAL+COMM

LAST_NAME                     SALARY COMMISSION_PCT INCOMME
------------------------- ---------- -------------- --------
Livingston                      8400             .2 SAL+COMM
Johnson                         6200             .1 SAL+COMM

79 rows selected.

Demo-03:NULLIF(expr1,expr2)如果expr1等于expr2,返回NULL;否则返回expr1

View Code
SQL> SELECT first_name,LENGTH(first_name) "expr1",last_name,LENGTH(last_name) "exp2",NULLIF(LENGTH(first_name),LENGTH(last_name)) result FROM employees;

FIRST_NAME                expr1 LAST_NAME                       exp2     RESULT
-------------------- ---------- ------------------------- ---------- ----------
Ellen                         5 Abel                               4          5
Sundar                        6 Ande                               4          6
Mozhe                         5 Atkinson                           8          5
David                         5 Austin                             6          5
Hermann                       7 Baer                               4          7
Shelli                        6 Baida                              5          6
Amit                          4 Banda                              5          4
Elizabeth                     9 Bates                              5          9
Sarah                         5 Bell                               4          5
David                         5 Bernstein                          9          5
Laura                         5 Bissot                             6          5

FIRST_NAME                expr1 LAST_NAME                       exp2     RESULT
-------------------- ---------- ------------------------- ---------- ----------
Harrison                      8 Bloom                              5          8
Alexis                        6 Bull                               4          6
Anthony                       7 Cabrio                             6          7
Gerald                        6 Cambrault                          9          6
Nanette                       7 Cambrault                          9          7
John                          4 Chen                               4
Kelly                         5 Chung                              5
Karen                         5 Colmenares                        10          5
Curtis                        6 Davies                             6
Lex                           3 De Haan                            7          3
Julia                         5 Dellinger                          9          5

FIRST_NAME                expr1 LAST_NAME                       exp2     RESULT
-------------------- ---------- ------------------------- ---------- ----------
Jennifer                      8 Dilly                              5          8
Louise                        6 Doran                              5          6
Bruce                         5 Ernst                              5
Alberto                       7 Errazuriz                          9          7
Britney                       7 Everett                            7
Daniel                        6 Faviet                             6
arcerzhang                   10 Fay                                3         10
Kevin                         5 Feeney                             6          5
Jean                          4 Fleaur                             6          4
Tayler                        6 Fox                                3          6
Adam                          4 Fripp                              5          4

FIRST_NAME                expr1 LAST_NAME                       exp2     RESULT
-------------------- ---------- ------------------------- ---------- ----------
Timothy                       7 Gates                              5          7
Ki                            2 Gee                                3          2
Girard                        6 Geoni                              5          6
William                       7 Gietz                              5          7
Douglas                       7 Grant                              5          7
Kimberely                     9 Grant                              5          9
Nancy                         5 Greenberg                          9          5
Danielle                      8 Greene                             6          8
Peter                         5 Hall                               4          5
Michael                       7 Hartstein                          9          7
Shelley                       7 Higgins                            7

FIRST_NAME                expr1 LAST_NAME                       exp2     RESULT
-------------------- ---------- ------------------------- ---------- ----------
Guy                           3 Himuro                             6          3
Alexander                     9 Hunold                             6          9
Alyssa                        6 Hutton                             6
Charles                       7 Johnson                            7
Vance                         5 Jones                              5
Payam                         5 Kaufling                           8          5
Alexander                     9 Khoo                               4          9
Janette                       7 King                               4          7
Steven                        6 King                               4          6
Neena                         5 Kochhar                            7          5
Sundita                       7 Kumar                              5          7

FIRST_NAME                expr1 LAST_NAME                       exp2     RESULT
-------------------- ---------- ------------------------- ---------- ----------
Renske                        6 Ladwig                             6
James                         5 Landry                             6          5
David                         5 Lee                                3          5
Jack                          4 Livingston                        10          4
Diana                         5 Lorentz                            7          5
Jason                         5 Mallin                             6          5
Steven                        6 Markle                             6
James                         5 Marlow                             6          5
Mattea                        6 Marvins                            7          6
Randall                       7 Matos                              5          7
Susan                         5 Mavris                             6          5

FIRST_NAME                expr1 LAST_NAME                       exp2     RESULT
-------------------- ---------- ------------------------- ---------- ----------
Samuel                        6 McCain                             6
Allan                         5 McEwen                             6          5
Irene                         5 Mikkilineni                       11          5
Kevin                         5 Mourgos                            7          5
Julia                         5 Nayer                              5
Donald                        6 OConnell                           8          6
Christopher                  11 Olsen                              5         11
TJ                            2 Olson                              5          2
Lisa                          4 Ozer                               4
Karen                         5 Partners                           8          5
Valli                         5 Pataballa                          9          5

FIRST_NAME                expr1 LAST_NAME                       exp2     RESULT
-------------------- ---------- ------------------------- ---------- ----------
Joshua                        6 Patel                              5          6
Randall                       7 Perkins                            7
Hazel                         5 Philtanker                        10          5
Luis                          4 Popp                               4
Trenna                        6 Rajs                               4          6
Den                           3 Raphaely                           8          3
Michael                       7 Rogers                             6          7
John                          4 Russell                            7          4
Nandita                       7 Sarchand                           8          7
Ismael                        6 Sciarra                            7          6
John                          4 Seo                                3          4

FIRST_NAME                expr1 LAST_NAME                       exp2     RESULT
-------------------- ---------- ------------------------- ---------- ----------
Sarath                        6 Sewall                             6
Lindsey                       7 Smith                              5          7
William                       7 Smith                              5          7
Stephen                       7 Stiles                             6          7
Martha                        6 Sullivan                           8          6
Patrick                       7 Sully                              5          7
Jonathon                      8 Taylor                             6          8
Winston                       7 Taylor                             6          7
Sigal                         5 Tobias                             6          5
Peter                         5 Tucker                             6          5
Oliver                        6 Tuvault                            7          6

FIRST_NAME                expr1 LAST_NAME                       exp2     RESULT
-------------------- ---------- ------------------------- ---------- ----------
Jose Manuel                  11 Urman                              5         11
Peter                         5 Vargas                             6          5
Clara                         5 Vishney                            7          5
Shanta                        6 Vollman                            7          6
Alana                         5 Walsh                              5
Matthew                       7 Weiss                              5          7
Jennifer                      8 Whalen                             6          8
Eleni                         5 Zlotkey                            7          5

107 rows selected.

Using the COALESCE Function

  • The advantage of the COALESCE function over the NVL function is that the COALESCE function can take multiple alternate values.
  • If the first expression is not null,the COALESCE function returns that expression;otherwise,it does a COALESCE of the remaining expressions.

Demo-01:COALESCE(expr1,expr2,expr3...exprn)如果expr1,为NULL,接着判断expr2;如果expr2为NULL,接着判断expr3;直到返回第一个不为NULL值得exprn;

View Code
SQL> SELECT last_name,employee_id,COALESCE(TO_CHAR(commission_pct),TO_CHAR(manager_id),'No commission and no manager') FROM employees;

LAST_NAME                 EMPLOYEE_ID COALESCE(TO_CHAR(COMMISSION_PCT),TO_CHAR
------------------------- ----------- ----------------------------------------
OConnell                          198 124
Grant                             199 124
Whalen                            200 101
Hartstein                         201 100
Fay                               202 201
Mavris                            203 101
Baer                              204 101
Higgins                           205 101
Gietz                             206 205
King                              100 No commission and no manager
Kochhar                           101 100

LAST_NAME                 EMPLOYEE_ID COALESCE(TO_CHAR(COMMISSION_PCT),TO_CHAR
------------------------- ----------- ----------------------------------------
De Haan                           102 100
Hunold                            103 102
Ernst                             104 103
Austin                            105 103
Pataballa                         106 103
Lorentz                           107 103
Greenberg                         108 101
Faviet                            109 108
Chen                              110 108
Sciarra                           111 108
Urman                             112 108

LAST_NAME                 EMPLOYEE_ID COALESCE(TO_CHAR(COMMISSION_PCT),TO_CHAR
------------------------- ----------- ----------------------------------------
Popp                              113 108
Raphaely                          114 100
Khoo                              115 114
Baida                             116 114
Tobias                            117 114
Himuro                            118 114
Colmenares                        119 114
Weiss                             120 100
Fripp                             121 100
Kaufling                          122 100
Vollman                           123 100

LAST_NAME                 EMPLOYEE_ID COALESCE(TO_CHAR(COMMISSION_PCT),TO_CHAR
------------------------- ----------- ----------------------------------------
Mourgos                           124 100
Nayer                             125 120
Mikkilineni                       126 120
Landry                            127 120
Markle                            128 120
Bissot                            129 121
Atkinson                          130 121
Marlow                            131 121
Olson                             132 121
Mallin                            133 122
Rogers                            134 122

LAST_NAME                 EMPLOYEE_ID COALESCE(TO_CHAR(COMMISSION_PCT),TO_CHAR
------------------------- ----------- ----------------------------------------
Gee                               135 122
Philtanker                        136 122
Ladwig                            137 123
Stiles                            138 123
Seo                               139 123
Patel                             140 123
Rajs                              141 124
Davies                            142 124
Matos                             143 124
Vargas                            144 124
Russell                           145 .4

LAST_NAME                 EMPLOYEE_ID COALESCE(TO_CHAR(COMMISSION_PCT),TO_CHAR
------------------------- ----------- ----------------------------------------
Partners                          146 .3
Errazuriz                         147 .3
Cambrault                         148 .3
Zlotkey                           149 .2
Tucker                            150 .3
Bernstein                         151 .25
Hall                              152 .25
Olsen                             153 .2
Cambrault                         154 .2
Tuvault                           155 .15
King                              156 .35

LAST_NAME                 EMPLOYEE_ID COALESCE(TO_CHAR(COMMISSION_PCT),TO_CHAR
------------------------- ----------- ----------------------------------------
Sully                             157 .35
McEwen                            158 .35
Smith                             159 .3
Doran                             160 .3
Sewall                            161 .25
Vishney                           162 .25
Greene                            163 .15
Marvins                           164 .1
Lee                               165 .1
Ande                              166 .1
Banda                             167 .1

LAST_NAME                 EMPLOYEE_ID COALESCE(TO_CHAR(COMMISSION_PCT),TO_CHAR
------------------------- ----------- ----------------------------------------
Ozer                              168 .25
Bloom                             169 .2
Fox                               170 .2
Smith                             171 .15
Bates                             172 .15
Kumar                             173 .1
Abel                              174 .3
Hutton                            175 .25
Taylor                            176 .2
Livingston                        177 .2
Grant                             178 .15

LAST_NAME                 EMPLOYEE_ID COALESCE(TO_CHAR(COMMISSION_PCT),TO_CHAR
------------------------- ----------- ----------------------------------------
Johnson                           179 .1
Taylor                            180 120
Fleaur                            181 120
Sullivan                          182 120
Geoni                             183 120
Sarchand                          184 121
Bull                              185 121
Dellinger                         186 121
Cabrio                            187 121
Chung                             188 122
Dilly                             189 122

LAST_NAME                 EMPLOYEE_ID COALESCE(TO_CHAR(COMMISSION_PCT),TO_CHAR
------------------------- ----------- ----------------------------------------
Gates                             190 122
Perkins                           191 122
Bell                              192 123
Everett                           193 123
McCain                            194 123
Jones                             195 123
Walsh                             196 124
Feeney                            197 124

107 rows selected.

 

 

 

 

 

 

posted @ 2013-04-12 17:24  ArcerZhang  阅读(236)  评论(0编辑  收藏  举报