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

Conditional Expressions

  • Prvoide the use of the IF-THEN-ELSE logic within a SQL statement
  • Use two methods
    • -CASE expression(SQL标准)
    • -DECODE function(专属Oracle)

CASE Expression

Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:

CASE expr WHEN comparison_expr1 THEN return_expr1
        [ WHEN comparison_expr2 THEN return_expr2
          WHEN comparison_expr3 THEN return_expr3
          ...
          WHEN comparison_exprn THEN return_exprn
          ELSE else_expr
        ]
END

Using the CASE Expression

Demo-01:CASE

View Code
SQL> SELECT last_name,job_id,salary,CASE job_id WHEN 'IT_PROG' THEN 1.10*salary WHEN 'ST_CLERK' THEN 1.15*salary WHEN 'SA_REP' THEN 1.20*salary ELSE salary END "REVISED_SALARY" FROM employees;

LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
OConnell                  SH_CLERK         2600           2600
Grant                     SH_CLERK         2600           2600
Whalen                    AD_ASST          4400           4400
Hartstein                 MK_MAN          13000          13000
Fay                       MK_REP           6000           6000
Mavris                    HR_REP           6500           6500
Baer                      PR_REP          10000          10000
Higgins                   AC_MGR          12008          12008
Gietz                     AC_ACCOUNT       8300           8300
King                      AD_PRES         24000          24000
Kochhar                   AD_VP           17000          17000

LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
De Haan                   AD_VP           17000          17000
Hunold                    IT_PROG          9000           9900
Ernst                     IT_PROG          6000           6600
Austin                    IT_PROG          4800           5280
Pataballa                 IT_PROG          4800           5280
Lorentz                   IT_PROG          4200           4620
Greenberg                 FI_MGR          12008          12008
Faviet                    FI_ACCOUNT       9000           9000
Chen                      FI_ACCOUNT       8200           8200
Sciarra                   FI_ACCOUNT       7700           7700
Urman                     FI_ACCOUNT       7800           7800

LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
Popp                      FI_ACCOUNT       6900           6900
Raphaely                  PU_MAN          11000          11000
Khoo                      PU_CLERK         3100           3100
Baida                     PU_CLERK         2900           2900
Tobias                    PU_CLERK         2800           2800
Himuro                    PU_CLERK         2600           2600
Colmenares                PU_CLERK         2500           2500
Weiss                     ST_MAN           8000           8000
Fripp                     ST_MAN           8200           8200
Kaufling                  ST_MAN           7900           7900
Vollman                   ST_MAN           6500           6500

LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
Mourgos                   ST_MAN           5800           5800
Nayer                     ST_CLERK         3200           3680
Mikkilineni               ST_CLERK         2700           3105
Landry                    ST_CLERK         2400           2760
Markle                    ST_CLERK         2200           2530
Bissot                    ST_CLERK         3300           3795
Atkinson                  ST_CLERK         2800           3220
Marlow                    ST_CLERK         2500           2875
Olson                     ST_CLERK         2100           2415
Mallin                    ST_CLERK         3300           3795
Rogers                    ST_CLERK         2900           3335

LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
Gee                       ST_CLERK         2400           2760
Philtanker                ST_CLERK         2200           2530
Ladwig                    ST_CLERK         3600           4140
Stiles                    ST_CLERK         3200           3680
Seo                       ST_CLERK         2700           3105
Patel                     ST_CLERK         2500           2875
Rajs                      ST_CLERK         3500           4025
Davies                    ST_CLERK         3100           3565
Matos                     ST_CLERK         2600           2990
Vargas                    ST_CLERK         2500           2875
Russell                   SA_MAN          14000          14000

LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
Partners                  SA_MAN          13500          13500
Errazuriz                 SA_MAN          12000          12000
Cambrault                 SA_MAN          11000          11000
Zlotkey                   SA_MAN          10500          10500
Tucker                    SA_REP          10000          12000
Bernstein                 SA_REP           9500          11400
Hall                      SA_REP           9000          10800
Olsen                     SA_REP           8000           9600
Cambrault                 SA_REP           7500           9000
Tuvault                   SA_REP           7000           8400
King                      SA_REP          10000          12000

LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
Sully                     SA_REP           9500          11400
McEwen                    SA_REP           9000          10800
Smith                     SA_REP           8000           9600
Doran                     SA_REP           7500           9000
Sewall                    SA_REP           7000           8400
Vishney                   SA_REP          10500          12600
Greene                    SA_REP           9500          11400
Marvins                   SA_REP           7200           8640
Lee                       SA_REP           6800           8160
Ande                      SA_REP           6400           7680
Banda                     SA_REP           6200           7440

LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
Ozer                      SA_REP          11500          13800
Bloom                     SA_REP          10000          12000
Fox                       SA_REP           9600          11520
Smith                     SA_REP           7400           8880
Bates                     SA_REP           7300           8760
Kumar                     SA_REP           6100           7320
Abel                      SA_REP          11000          13200
Hutton                    SA_REP           8800          10560
Taylor                    SA_REP           8600          10320
Livingston                SA_REP           8400          10080
Grant                     SA_REP           7000           8400

LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
Johnson                   SA_REP           6200           7440
Taylor                    SH_CLERK         3200           3200
Fleaur                    SH_CLERK         3100           3100
Sullivan                  SH_CLERK         2500           2500
Geoni                     SH_CLERK         2800           2800
Sarchand                  SH_CLERK         4200           4200
Bull                      SH_CLERK         4100           4100
Dellinger                 SH_CLERK         3400           3400
Cabrio                    SH_CLERK         3000           3000
Chung                     SH_CLERK         3800           3800
Dilly                     SH_CLERK         3600           3600

LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
Gates                     SH_CLERK         2900           2900
Perkins                   SH_CLERK         2500           2500
Bell                      SH_CLERK         4000           4000
Everett                   SH_CLERK         3900           3900
McCain                    SH_CLERK         3200           3200
Jones                     SH_CLERK         2800           2800
Walsh                     SH_CLERK         3100           3100
Feeney                    SH_CLERK         3000           3000

107 rows selected.

DECODE Function

Facilitates conditional inquiries by doing the work of a CASE expression or an IF-THEN-ELSE statement:

DECODE(col|expression,search1,result1[,serach2,result2,...,][,default])

Demo-01:DECODE

View Code
SQL> SELECT last_name,job_id,salary,DECODE(job_id,'IT_PROG',1.10*salary,'ST_CLERK',1.15*salary,'SA_REP',1.20*salary,salary) "REVISED_SALARY" FROM employees;

LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
OConnell                  SH_CLERK         2600           2600
Grant                     SH_CLERK         2600           2600
Whalen                    AD_ASST          4400           4400
Hartstein                 MK_MAN          13000          13000
Fay                       MK_REP           6000           6000
Mavris                    HR_REP           6500           6500
Baer                      PR_REP          10000          10000
Higgins                   AC_MGR          12008          12008
Gietz                     AC_ACCOUNT       8300           8300
King                      AD_PRES         24000          24000
Kochhar                   AD_VP           17000          17000

LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
De Haan                   AD_VP           17000          17000
Hunold                    IT_PROG          9000           9900
Ernst                     IT_PROG          6000           6600
Austin                    IT_PROG          4800           5280
Pataballa                 IT_PROG          4800           5280
Lorentz                   IT_PROG          4200           4620
Greenberg                 FI_MGR          12008          12008
Faviet                    FI_ACCOUNT       9000           9000
Chen                      FI_ACCOUNT       8200           8200
Sciarra                   FI_ACCOUNT       7700           7700
Urman                     FI_ACCOUNT       7800           7800

LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
Popp                      FI_ACCOUNT       6900           6900
Raphaely                  PU_MAN          11000          11000
Khoo                      PU_CLERK         3100           3100
Baida                     PU_CLERK         2900           2900
Tobias                    PU_CLERK         2800           2800
Himuro                    PU_CLERK         2600           2600
Colmenares                PU_CLERK         2500           2500
Weiss                     ST_MAN           8000           8000
Fripp                     ST_MAN           8200           8200
Kaufling                  ST_MAN           7900           7900
Vollman                   ST_MAN           6500           6500

LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
Mourgos                   ST_MAN           5800           5800
Nayer                     ST_CLERK         3200           3680
Mikkilineni               ST_CLERK         2700           3105
Landry                    ST_CLERK         2400           2760
Markle                    ST_CLERK         2200           2530
Bissot                    ST_CLERK         3300           3795
Atkinson                  ST_CLERK         2800           3220
Marlow                    ST_CLERK         2500           2875
Olson                     ST_CLERK         2100           2415
Mallin                    ST_CLERK         3300           3795
Rogers                    ST_CLERK         2900           3335

LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
Gee                       ST_CLERK         2400           2760
Philtanker                ST_CLERK         2200           2530
Ladwig                    ST_CLERK         3600           4140
Stiles                    ST_CLERK         3200           3680
Seo                       ST_CLERK         2700           3105
Patel                     ST_CLERK         2500           2875
Rajs                      ST_CLERK         3500           4025
Davies                    ST_CLERK         3100           3565
Matos                     ST_CLERK         2600           2990
Vargas                    ST_CLERK         2500           2875
Russell                   SA_MAN          14000          14000

LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
Partners                  SA_MAN          13500          13500
Errazuriz                 SA_MAN          12000          12000
Cambrault                 SA_MAN          11000          11000
Zlotkey                   SA_MAN          10500          10500
Tucker                    SA_REP          10000          12000
Bernstein                 SA_REP           9500          11400
Hall                      SA_REP           9000          10800
Olsen                     SA_REP           8000           9600
Cambrault                 SA_REP           7500           9000
Tuvault                   SA_REP           7000           8400
King                      SA_REP          10000          12000

LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
Sully                     SA_REP           9500          11400
McEwen                    SA_REP           9000          10800
Smith                     SA_REP           8000           9600
Doran                     SA_REP           7500           9000
Sewall                    SA_REP           7000           8400
Vishney                   SA_REP          10500          12600
Greene                    SA_REP           9500          11400
Marvins                   SA_REP           7200           8640
Lee                       SA_REP           6800           8160
Ande                      SA_REP           6400           7680
Banda                     SA_REP           6200           7440

LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
Ozer                      SA_REP          11500          13800
Bloom                     SA_REP          10000          12000
Fox                       SA_REP           9600          11520
Smith                     SA_REP           7400           8880
Bates                     SA_REP           7300           8760
Kumar                     SA_REP           6100           7320
Abel                      SA_REP          11000          13200
Hutton                    SA_REP           8800          10560
Taylor                    SA_REP           8600          10320
Livingston                SA_REP           8400          10080
Grant                     SA_REP           7000           8400

LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
Johnson                   SA_REP           6200           7440
Taylor                    SH_CLERK         3200           3200
Fleaur                    SH_CLERK         3100           3100
Sullivan                  SH_CLERK         2500           2500
Geoni                     SH_CLERK         2800           2800
Sarchand                  SH_CLERK         4200           4200
Bull                      SH_CLERK         4100           4100
Dellinger                 SH_CLERK         3400           3400
Cabrio                    SH_CLERK         3000           3000
Chung                     SH_CLERK         3800           3800
Dilly                     SH_CLERK         3600           3600

LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
Gates                     SH_CLERK         2900           2900
Perkins                   SH_CLERK         2500           2500
Bell                      SH_CLERK         4000           4000
Everett                   SH_CLERK         3900           3900
McCain                    SH_CLERK         3200           3200
Jones                     SH_CLERK         2800           2800
Walsh                     SH_CLERK         3100           3100
Feeney                    SH_CLERK         3000           3000

107 rows selected.

Using the DECODE function

Display the applicable tax rate for each employee in department 80:

View Code
SQL> SELECT last_name,salary,DECODE(TRUNC(salary/2000,0),0,0.00,1,0.09,2,0.20,3,0.30,4,0.40,5,0.42,6,0.44,0.45)TAX_RATE FROM employees WHERE department_id = 80;

LAST_NAME                     SALARY   TAX_RATE
------------------------- ---------- ----------
Russell                        14000        .45
Partners                       13500        .44
Errazuriz                      12000        .44
Cambrault                      11000        .42
Zlotkey                        10500        .42
Tucker                         10000        .42
Bernstein                       9500         .4
Hall                            9000         .4
Olsen                           8000         .4
Cambrault                       7500         .3
Tuvault                         7000         .3

LAST_NAME                     SALARY   TAX_RATE
------------------------- ---------- ----------
King                           10000        .42
Sully                           9500         .4
McEwen                          9000         .4
Smith                           8000         .4
Doran                           7500         .3
Sewall                          7000         .3
Vishney                        10500        .42
Greene                          9500         .4
Marvins                         7200         .3
Lee                             6800         .3
Ande                            6400         .3

LAST_NAME                     SALARY   TAX_RATE
------------------------- ---------- ----------
Banda                           6200         .3
Ozer                           11500        .42
Bloom                          10000        .42
Fox                             9600         .4
Smith                           7400         .3
Bates                           7300         .3
Kumar                           6100         .3
Abel                           11000        .42
Hutton                          8800         .4
Taylor                          8600         .4
Livingston                      8400         .4

LAST_NAME                     SALARY   TAX_RATE
------------------------- ---------- ----------
Johnson                         6200         .3

34 rows selected.

Summary

In this lesson,you should have learned how to:

  • After data format for display using functions
  • Convert column data types using functions
  • Use NVL functions
  • Use IF-THEN-ELSE logic and other conditional expressions in a SELECT statement.

 

 

 

 

 

 

 

 

posted @ 2013-04-12 18:06  ArcerZhang  阅读(199)  评论(0编辑  收藏  举报