对于general functions 的一点笔记，仅仅作为自己的理解记录笔记
1、NVL(expr1,expr2)
expr1,需要转换的字段名或者表达式。
expr2,null的替代值
expr2,null的替代值
2、NVL2(expr1,expr2,expr3)
NVL2 (expr1, expr2, expr3) ->expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型
3、NULLIF(expr1,expr2)
NULLIF (expr1, expr2) ->相等返回NULL，不等返回expr1
4、COALESCE（expr1,expr2,...,exprn）
从左往右数，遇到第一个非null值，则返回该非null值。
多层判断
多层判断
NVL函数
---NVL函数是将NULL值的字段转换成默认字段输出。
将空值转换为实际值
1、可以使用的数据类型为日期、字符和数字
2、数据类型必须匹配
例子是NUMBER, DATE, CHAR OR VARCHAR2
-NVL(commission_pct,0)
-NVL(hire_date,'01-JAN-97')
-NVL(job_id,'No Job Yet')
SQL> SELECT last_name,
2 salary,
3 NVL(commission_pct, 0),
4 (salary * 12) + (salary * 12 * NVL(commission_pct, 0)) AN_SAL
5 FROM employees;
LAST_NAME SALARY NVL(COMMISSION_PCT,0) AN_SAL
------------------------- ---------- --------------------- ----------
King 24000.00 0 288000
Kochhar 17000.00 0 204000
De Haan 17000.00 0 204000
Hunold 9000.00 0 108000
Ernst 6000.00 0 72000
Austin 4800.00 0 57600
Pataballa 4800.00 0 57600
Lorentz 4200.00 0 50400
Greenberg 12000.00 0 144000
Faviet 9000.00 0 108000
Chen 8200.00 0 98400
Sciarra 7700.00 0 92400
Urman 7800.00 0 93600
Popp 6900.00 0 82800
Raphaely 11000.00 0 132000
Khoo 3100.00 0 37200
Baida 2900.00 0 34800
Tobias 2800.00 0 33600
Himuro 2600.00 0 31200
Colmenares 2500.00 0 30000
LAST_NAME SALARY NVL(COMMISSION_PCT,0) AN_SAL
------------------------- ---------- --------------------- ----------
Weiss 8000.00 0 96000
Fripp 8200.00 0 98400
Kaufling 7900.00 0 94800
Vollman 6500.00 0 78000
Mourgos 5800.00 0 69600
Nayer 3200.00 0 38400
Mikkilineni 2700.00 0 32400
Landry 2400.00 0 28800
Markle 2200.00 0 26400
Bissot 3300.00 0 39600
Atkinson 2800.00 0 33600
Marlow 2500.00 0 30000
Olson 2100.00 0 25200
Mallin 3300.00 0 39600
Rogers 2900.00 0 34800
Gee 2400.00 0 28800
Philtanker 2200.00 0 26400
Ladwig 3600.00 0 43200
Stiles 3200.00 0 38400
Seo 2700.00 0 32400
Patel 2500.00 0 30000
LAST_NAME SALARY NVL(COMMISSION_PCT,0) AN_SAL
------------------------- ---------- --------------------- ----------
Rajs 3500.00 0 42000
Davies 3100.00 0 37200
Matos 2600.00 0 31200
Vargas 2500.00 0 30000
Russell 14000.00 0.4 235200
Partners 13500.00 0.3 210600
Errazuriz 12000.00 0.3 187200
Cambrault 11000.00 0.3 171600
Zlotkey 10500.00 0.2 151200
Tucker 10000.00 0.3 156000
Bernstein 9500.00 0.25 142500
Hall 9000.00 0.25 135000
Olsen 8000.00 0.2 115200
Cambrault 7500.00 0.2 108000
Tuvault 7000.00 0.15 96600
King 10000.00 0.35 162000
Sully 9500.00 0.35 153900
McEwen 9000.00 0.35 145800
Smith 8000.00 0.3 124800
Doran 7500.00 0.3 117000
Sewall 7000.00 0.25 105000
LAST_NAME SALARY NVL(COMMISSION_PCT,0) AN_SAL
------------------------- ---------- --------------------- ----------
Vishney 10500.00 0.25 157500
Greene 9500.00 0.15 131100
Marvins 7200.00 0.1 95040
Lee 6800.00 0.1 89760
Ande 6400.00 0.1 84480
Banda 6200.00 0.1 81840
Ozer 11500.00 0.25 172500
Bloom 10000.00 0.2 144000
Fox 9600.00 0.2 138240
Smith 7400.00 0.15 102120
Bates 7300.00 0.15 100740
Kumar 6100.00 0.1 80520
Abel 11000.00 0.3 171600
Hutton 8800.00 0.25 132000
Taylor 8600.00 0.2 123840
Livingston 8400.00 0.2 120960
Grant 7000.00 0.15 96600
Johnson 6200.00 0.1 81840
Taylor 3200.00 0 38400
Fleaur 3100.00 0 37200
Sullivan 2500.00 0 30000
LAST_NAME SALARY NVL(COMMISSION_PCT,0) AN_SAL
------------------------- ---------- --------------------- ----------
Geoni 2800.00 0 33600
Sarchand 4200.00 0 50400
Bull 4100.00 0 49200
Dellinger 3400.00 0 40800
Cabrio 3000.00 0 36000
Chung 3800.00 0 45600
Dilly 3600.00 0 43200
Gates 2900.00 0 34800
Perkins 2500.00 0 30000
Bell 4000.00 0 48000
Everett 3900.00 0 46800
McCain 3200.00 0 38400
Jones 2800.00 0 33600
Walsh 3100.00 0 37200
Feeney 3000.00 0 36000
OConnell 2600.00 0 31200
Grant 2600.00 0 31200
Whalen 4400.00 0 52800
Hartstein 13000.00 0 156000
Fay 6000.00 0 72000
Mavris 6500.00 0 78000
LAST_NAME SALARY NVL(COMMISSION_PCT,0) AN_SAL
------------------------- ---------- --------------------- ----------
Baer 10000.00 0 120000
Higgins 12000.00 0 144000
Gietz 8300.00 0 99600
107 rows selected---nvl2
SQL> select p.last_name,p.salary,p.commission_pct,nvl2(p.commission_pct,'p.salary+p.comission_pct','p.salary') as income from employees p where p.department_id in(50,80);
LAST_NAME SALARY COMMISSION_PCT INCOME
------------------------- ---------- -------------- ------------------------
Weiss 8000.00 p.salary
Fripp 8200.00 p.salary
Kaufling 7900.00 p.salary
Vollman 6500.00 p.salary
Mourgos 5800.00 p.salary
Nayer 3200.00 p.salary
Mikkilineni 2700.00 p.salary
Landry 2400.00 p.salary
Markle 2200.00 p.salary
Bissot 3300.00 p.salary
Atkinson 2800.00 p.salary
Marlow 2500.00 p.salary
Olson 2100.00 p.salary
Mallin 3300.00 p.salary
Rogers 2900.00 p.salary
Gee 2400.00 p.salary
Philtanker 2200.00 p.salary
Ladwig 3600.00 p.salary
Stiles 3200.00 p.salary
Seo 2700.00 p.salary
LAST_NAME SALARY COMMISSION_PCT INCOME
------------------------- ---------- -------------- ------------------------
Patel 2500.00 p.salary
Rajs 3500.00 p.salary
Davies 3100.00 p.salary
Matos 2600.00 p.salary
Vargas 2500.00 p.salary
Russell 14000.00 0.40 p.salary+p.comission_pct
Partners 13500.00 0.30 p.salary+p.comission_pct
Errazuriz 12000.00 0.30 p.salary+p.comission_pct
Cambrault 11000.00 0.30 p.salary+p.comission_pct
Zlotkey 10500.00 0.20 p.salary+p.comission_pct
Tucker 10000.00 0.30 p.salary+p.comission_pct
Bernstein 9500.00 0.25 p.salary+p.comission_pct
Hall 9000.00 0.25 p.salary+p.comission_pct
Olsen 8000.00 0.20 p.salary+p.comission_pct
Cambrault 7500.00 0.20 p.salary+p.comission_pct
Tuvault 7000.00 0.15 p.salary+p.comission_pct
King 10000.00 0.35 p.salary+p.comission_pct
Sully 9500.00 0.35 p.salary+p.comission_pct
McEwen 9000.00 0.35 p.salary+p.comission_pct
Smith 8000.00 0.30 p.salary+p.comission_pct
Doran 7500.00 0.30 p.salary+p.comission_pct
LAST_NAME SALARY COMMISSION_PCT INCOME
------------------------- ---------- -------------- ------------------------
Sewall 7000.00 0.25 p.salary+p.comission_pct
Vishney 10500.00 0.25 p.salary+p.comission_pct
Greene 9500.00 0.15 p.salary+p.comission_pct
Marvins 7200.00 0.10 p.salary+p.comission_pct
Lee 6800.00 0.10 p.salary+p.comission_pct
Ande 6400.00 0.10 p.salary+p.comission_pct
Banda 6200.00 0.10 p.salary+p.comission_pct
Ozer 11500.00 0.25 p.salary+p.comission_pct
Bloom 10000.00 0.20 p.salary+p.comission_pct
Fox 9600.00 0.20 p.salary+p.comission_pct
Smith 7400.00 0.15 p.salary+p.comission_pct
Bates 7300.00 0.15 p.salary+p.comission_pct
Kumar 6100.00 0.10 p.salary+p.comission_pct
Abel 11000.00 0.30 p.salary+p.comission_pct
Hutton 8800.00 0.25 p.salary+p.comission_pct
Taylor 8600.00 0.20 p.salary+p.comission_pct
Livingston 8400.00 0.20 p.salary+p.comission_pct
Johnson 6200.00 0.10 p.salary+p.comission_pct
Taylor 3200.00 p.salary
Fleaur 3100.00 p.salary
Sullivan 2500.00 p.salary
LAST_NAME SALARY COMMISSION_PCT INCOME
------------------------- ---------- -------------- ------------------------
Geoni 2800.00 p.salary
Sarchand 4200.00 p.salary
Bull 4100.00 p.salary
Dellinger 3400.00 p.salary
Cabrio 3000.00 p.salary
Chung 3800.00 p.salary
Dilly 3600.00 p.salary
Gates 2900.00 p.salary
Perkins 2500.00 p.salary
Bell 4000.00 p.salary
Everett 3900.00 p.salary
McCain 3200.00 p.salary
Jones 2800.00 p.salary
Walsh 3100.00 p.salary
Feeney 3000.00 p.salary
OConnell 2600.00 p.salary
Grant 2600.00 p.salary
79 rows selected
---nullif
SQL> select o.first_name,length(o.first_name) as "expr1",o.last_name,length(o.last_name) as "expr2",nullif(length(o.first_name),length(o.last_name)) as "expr1=expr2返空，!=返expr1" from employees o ;
FIRST_NAME expr1 LAST_NAME expr2 expr1=expr2返空，!=返expr1
-------------------- ---------- ------------------------- ---------- --------------------------
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
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
FIRST_NAME expr1 LAST_NAME expr2 expr1=expr2返空，!=返expr1
-------------------- ---------- ------------------------- ---------- --------------------------
Lex 3 De Haan 7 3
Julia 5 Dellinger 9 5
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
Pat 3 Fay 3
Kevin 5 Feeney 6 5
Jean 4 Fleaur 6 4
Tayler 6 Fox 3 6
Adam 4 Fripp 5 4
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
FIRST_NAME expr1 LAST_NAME expr2 expr1=expr2返空，!=返expr1
-------------------- ---------- ------------------------- ---------- --------------------------
Peter 5 Hall 4 5
Michael 7 Hartstein 9 7
Shelley 7 Higgins 7
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
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
FIRST_NAME expr1 LAST_NAME expr2 expr1=expr2返空，!=返expr1
-------------------- ---------- ------------------------- ---------- --------------------------
James 5 Marlow 6 5
Mattea 6 Marvins 7 6
Randall 7 Matos 5 7
Susan 5 Mavris 6 5
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
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
FIRST_NAME expr1 LAST_NAME expr2 expr1=expr2返空，!=返expr1
-------------------- ---------- ------------------------- ---------- --------------------------
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
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
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
FIRST_NAME expr1 LAST_NAME expr2 expr1=expr2返空，!=返expr1
-------------------- ---------- ------------------------- ---------- --------------------------
Matthew 7 Weiss 5 7
Jennifer 8 Whalen 6 8
Eleni 5 Zlotkey 7 5
107 rows selected
SQL>
使用coalesce 函数
1、coalesce 函数优于nvl函数之处于在coalesce 函数可以接受多个备选值
2、如果第一个表达式不为空，则coalesce 函数返回该表达式；否则，它将对其余的表达式进行coalesce 运算
官方文档上对这个函数做了如下的解释
COALESCE returns the first non-null expr in the expression list. You must specify at least two expressions. If all occurrences of expr evaluate to null, then the function returns null.
Oracle Database uses short-circuit evaluation. The database evaluates each expr value and determines whether it is NULL, rather than evaluating all of theexpr values before determining whether any of them is NULL.
If all occurrences of expr are numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype, then Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.
SQL> select o.last_name,o.employee_id,coalesce(to_char(o.commission_pct),to_char(o.manager_id),'No commission and no manager') as "结果" from employees o;
LAST_NAME EMPLOYEE_ID 结果
------------------------- ----------- ----------------------------------------
King 100 No commission and no manager
Kochhar 101 100
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
Popp 113 108
Raphaely 114 100
Khoo 115 114
Baida 116 114
Tobias 117 114
Himuro 118 114
Colmenares 119 114
LAST_NAME EMPLOYEE_ID 结果
------------------------- ----------- ----------------------------------------
Weiss 120 100
Fripp 121 100
Kaufling 122 100
Vollman 123 100
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
Gee 135 122
Philtanker 136 122
Ladwig 137 123
Stiles 138 123
Seo 139 123
Patel 140 123
LAST_NAME EMPLOYEE_ID 结果
------------------------- ----------- ----------------------------------------
Rajs 141 124
Davies 142 124
Matos 143 124
Vargas 144 124
Russell 145 .4
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
Sully 157 .35
McEwen 158 .35
Smith 159 .3
Doran 160 .3
Sewall 161 .25
LAST_NAME EMPLOYEE_ID 结果
------------------------- ----------- ----------------------------------------
Vishney 162 .25
Greene 163 .15
Marvins 164 .1
Lee 165 .1
Ande 166 .1
Banda 167 .1
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
Johnson 179 .1
Taylor 180 120
Fleaur 181 120
Sullivan 182 120
LAST_NAME EMPLOYEE_ID 结果
------------------------- ----------- ----------------------------------------
Geoni 183 120
Sarchand 184 121
Bull 185 121
Dellinger 186 121
Cabrio 187 121
Chung 188 122
Dilly 189 122
Gates 190 122
Perkins 191 122
Bell 192 123
Everett 193 123
McCain 194 123
Jones 195 123
Walsh 196 124
Feeney 197 124
OConnell 198 124
Grant 199 124
Whalen 200 101
Hartstein 201 100
Fay 202 201
Mavris 203 101
LAST_NAME EMPLOYEE_ID 结果
------------------------- ----------- ----------------------------------------
Baer 204 101
Higgins 205 101
Gietz 206 205
107 rows selected
--附录
SQL> select manager_id,last_name,commission_pct from employees;
MANAGER_ID LAST_NAME COMMISSION_PCT
---------- ------------------------- --------------
King
100 Kochhar
100 De Haan
102 Hunold
103 Ernst
103 Austin
103 Pataballa
103 Lorentz
101 Greenberg
108 Faviet
108 Chen
108 Sciarra
108 Urman
108 Popp
100 Raphaely
114 Khoo
114 Baida
114 Tobias
114 Himuro
114 Colmenares
MANAGER_ID LAST_NAME COMMISSION_PCT
---------- ------------------------- --------------
100 Weiss
100 Fripp
100 Kaufling
100 Vollman
100 Mourgos
120 Nayer
120 Mikkilineni
120 Landry
120 Markle
121 Bissot
121 Atkinson
121 Marlow
121 Olson
122 Mallin
122 Rogers
122 Gee
122 Philtanker
123 Ladwig
123 Stiles
123 Seo
123 Patel
MANAGER_ID LAST_NAME COMMISSION_PCT
---------- ------------------------- --------------
124 Rajs
124 Davies
124 Matos
124 Vargas
100 Russell 0.40
100 Partners 0.30
100 Errazuriz 0.30
100 Cambrault 0.30
100 Zlotkey 0.20
145 Tucker 0.30
145 Bernstein 0.25
145 Hall 0.25
145 Olsen 0.20
145 Cambrault 0.20
145 Tuvault 0.15
146 King 0.35
146 Sully 0.35
146 McEwen 0.35
146 Smith 0.30
146 Doran 0.30
146 Sewall 0.25
MANAGER_ID LAST_NAME COMMISSION_PCT
---------- ------------------------- --------------
147 Vishney 0.25
147 Greene 0.15
147 Marvins 0.10
147 Lee 0.10
147 Ande 0.10
147 Banda 0.10
148 Ozer 0.25
148 Bloom 0.20
148 Fox 0.20
148 Smith 0.15
148 Bates 0.15
148 Kumar 0.10
149 Abel 0.30
149 Hutton 0.25
149 Taylor 0.20
149 Livingston 0.20
149 Grant 0.15
149 Johnson 0.10
120 Taylor
120 Fleaur
120 Sullivan
MANAGER_ID LAST_NAME COMMISSION_PCT
---------- ------------------------- --------------
120 Geoni
121 Sarchand
121 Bull
121 Dellinger
121 Cabrio
122 Chung
122 Dilly
122 Gates
122 Perkins
123 Bell
123 Everett
123 McCain
123 Jones
124 Walsh
124 Feeney
124 OConnell
124 Grant
101 Whalen
100 Hartstein
201 Fay
101 Mavris
MANAGER_ID LAST_NAME COMMISSION_PCT
---------- ------------------------- --------------
101 Baer
101 Higgins
205 Gietz
107 rows selected
SQL>
377
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
