Oracle PL/SQL Tutorial/PL SQL Statements/CASE
Содержание
- 1 An example of comparison of two numbers using a searched CASE expression
- 2 CASE statements
- 3 Case statement to call procedure
- 4 case when
- 5 Named case block
- 6 Return statement with case
- 7 Simple CASE statement with range
- 8 Use CASE statement
- 9 Use case statement in a dbms_output.put_line
- 10 Use case statement in procedure call to use the proper parameter value
- 11 Variable assignment with case statement
- 12 When creating selector CASE statements, you cannot have NULL in the list of possible values.
An example of comparison of two numbers using a searched CASE expression
SQL>
SQL>
SQL> declare
2 a number :=20;
3 b number :=-40;
4 string varchar2(50);
5 begin
6 string :=case
7 when (a>b)then "A is greater than B"
8 when (a<b)then "A is less than B"
9 else
10 "A is equal to B"
11 end;
12 dbms_output.put_line(string);
13 end;
14 /
A is greater than B
PL/SQL procedure successfully completed.
SQL>
SQL>
CASE statements
A Traditional Condition Statement
SQL>
SQL> create or replace function f_getDateType (in_dt DATE)
2 return VARCHAR2
3 is
4 v_out VARCHAR2(10);
5 begin
6 if to_char(in_dt,"d") = 1 then
7 v_out:="SUNDAY";
8 elsif to_char(in_dt,"d") = 7 then
9 v_out:="SATURDAY";
10 else
11 v_out:="WEEKDAY";
12 end if;
13 return v_out;
14 end;
15 /
Function created.
Case statement to call procedure
SQL> DECLARE
2 salary NUMBER := 20000;
3 employee_id NUMBER := 36325;
4
5 PROCEDURE give_bonus (emp_id IN NUMBER, bonus_amt IN NUMBER) IS
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE(emp_id);
8 DBMS_OUTPUT.PUT_LINE(bonus_amt);
9 END;
10
11 BEGIN
12 CASE
13 WHEN salary >= 10000 AND salary <=20000 THEN
14 give_bonus(employee_id, 1500);
15 WHEN salary > 20000 AND salary <= 40000 THEN
16 give_bonus(employee_id, 1000);
17 WHEN salary > 40000 THEN
18 give_bonus(employee_id, 500);
19 ELSE
20 give_bonus(employee_id, 0);
21 END CASE;
22 END;
23 /
PL/SQL procedure successfully completed.
SQL>
SQL>
case when
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL primary key,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> select first_name, description
2 , case when description = "Tester" then " 10%"
3 when description = "Developer" then " 20%"
4 when first_name = "Jason" then " 30%"
5 else " 0%"
6 end as raise
7 from employee;
FIRST_NAME DESCRIPTION RAISE
-------------------- --------------- -----
Jason Programmer 30%
Alison Tester 10%
James Tester 10%
Celia Manager 0%
Robert Tester 10%
Linda Tester 10%
David Manager 0%
James Tester 10%
8 rows selected.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
Named case block
SQL>
SQL>
SQL> create table employee
2 (
3 emp_no integer primary key
4 ,lastname varchar2(20) not null
5 ,firstname varchar2(15) not null
6 ,midinit varchar2(1)
7 ,street varchar2(30)
8 ,city varchar2(20)
9 ,state varchar2(2)
10 ,zip varchar2(5)
11 ,zip_4 varchar2(4)
12 ,area_code varchar2(3)
13 ,phone varchar2(8)
14 ,salary number(5,2)
15 ,birthdate date
16 ,hiredate date
17 ,title varchar2(20)
18 ,dept_no integer
19 ,mgr integer
20 ,region number
21 ,division number
22 ,total_sales number
23 );
Table created.
SQL>
SQL>
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, birthdate, title)
2 values (1,"Gardinia","Joy","R","688 Ave","New York","NY","12122","2333","212","200-3393","12-nov-1956","President");
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
2 values (2,"Anderson","Lucy","J","33 Ave","New York","NY","43552","6633","212","234-4444",7.75,"21-mar-1951","1-feb-1994","Sales Manager",2,1,100,10,40000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (3,"Somers","Ingrid","E","12 Ave","New York","NY","76822","8763","212","867-6893",7.75,"14-feb-1963","15-mar-1995","Sales Clerk",2,2,100,10,10000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
2 values (4,"Washington","Georgia","J","13th Street","New York","NY","43122","4333","212","340-4365",11.50,"2-jul-1963","21-apr-1994","Designer",1,1,100,10,40000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
2 values (5,"Doright","Dudley","J","56 Langer Street","Staten Island","NY","23332","4983","718","777-4365",21.65,"15-may-1958","2-aug-1994","Designer",1,1,100,10,40000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values ( 6,"Doright","Dorothy","R","56 Langer Street","Staten Island","NY","23332","4983","718","777-4365",24.65,"10-dec-1968","2-aug-1994","Designer",1,1,100,10,40000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
2 values ( 7,"Perry","Donna","R","1st Ave","New York","NY","44444","3444","212","111-6893",7.75,"14-feb-1967","15-mar-1995","Sales Clerk",2,1,100,10,40000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
2 values ( 8,"Roger","John","E","67 H Ave","New York","NY","33822","1163","212","122-6893",10.00,"14-jun-1956","15-mar-1995","Accountant",3,1,100,10,40000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
2 values ( 9,"Hall","Ted","R","1236 Lane","New York","NY","33823","1164","212","222-4393",13.00,"10-jun-1959","15-aug-1997","Sales Representative",3,1,100,10,50000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
2 values ( 10,"Barbee","Candice","L","400 Street","New York","NY","33811","2009","212","321-6873",12.00,"10-oct-1964","15-jan-1999","Sales Representative",3,1,100,10,35000);
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> declare
2
3 v_emp employee.emp_no%type := 4;
4 v_sal employee.salary%type;
5 v_title employee.title%type;
6 v_rowid rowid;
7
8 BEGIN
9
10 SELECT salary, title, rowid
11 INTO v_sal, v_title, v_rowid
12 FROM employee
13 WHERE emp_no = v_emp
14 FOR UPDATE;
15
16 <<salary_test>>
17 CASE v_sal
18 WHEN 12 THEN
19 dbms_output.put_line("Salary is "||v_sal);
20 v_sal := v_sal * 1.2 ;
21 dbms_output.put_line("Salary is "||v_sal);
22 WHEN 14 THEN
23 dbms_output.put_line("Salary is "||v_sal);
24 v_sal := v_sal * 1.15 ;
25 dbms_output.put_line("Salary is "||v_sal);
26 ELSE
27 v_sal := v_sal * 1.1 ;
28 END CASE salary_test;
29
30 UPDATE employee
31 SET salary = v_sal
32 WHERE rowid = v_rowid;
33
34 END;
35 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>
Return statement with case
SQL>
SQL> DECLARE
2 boolean_true BOOLEAN := TRUE;
3 boolean_false BOOLEAN := FALSE;
4 boolean_null BOOLEAN;
5
6 FUNCTION boolean_to_varchar2 (flag IN BOOLEAN) RETURN VARCHAR2 IS
7 BEGIN
8 RETURN
9 CASE flag
10 WHEN TRUE THEN "True"
11 WHEN FALSE THEN "False"
12 ELSE "NULL" END;
13 END;
14
15 BEGIN
16 DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_true));
17 DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_false));
18 DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_null));
19 END;
20 /
PL/SQL procedure successfully completed.
SQL>
Simple CASE statement with range
SQL> DECLARE
2 salary NUMBER := 20000;
3 employee_id NUMBER := 36325;
4
5 PROCEDURE give_bonus (emp_id IN NUMBER, bonus_amt IN NUMBER) IS
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE(emp_id);
8 DBMS_OUTPUT.PUT_LINE(bonus_amt);
9 END;
10
11 BEGIN
12 CASE TRUE
13 WHEN salary >= 10000 AND salary <=20000 THEN
14 give_bonus(employee_id, 1500);
15 WHEN salary > 20000 AND salary <= 40000 THEN
16 give_bonus(employee_id, 1000);
17 WHEN salary > 40000 THEN
18 give_bonus(employee_id, 500);
19 ELSE
20 give_bonus(employee_id, 0);
21 END CASE;
22 END;
23 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
Use CASE statement
SQL>
SQL> CREATE TABLE books (
2 isbn CHAR(10) PRIMARY KEY,
3 category VARCHAR2(20),
4 title VARCHAR2(100),
5 num_pages NUMBER,
6 price NUMBER,
7 copyright NUMBER(4)
8 );
Table created.
SQL>
SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright)
2 VALUES ("72122048", "Oracle Basics", "Oracle8i: A Beginner""s Guide", 765, 44.99, 1999);
1 row created.
SQL>
SQL>
SQL>
SQL> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> DECLARE
2 v_category books.category%TYPE;
3 v_discount NUMBER(10,2);
4 v_isbn books.isbn%TYPE := "72230665";
5 BEGIN
6 SELECT category
7 INTO v_category
8 FROM books
9 WHERE isbn = v_isbn;
10
11 -- Determine discount based on category
12 CASE v_category
13 WHEN "Oracle Basics"
14 THEN v_discount := .15;
15 WHEN "Oracle Server"
16 THEN v_discount := .10;
17 END CASE;
18
19 DBMS_OUTPUT.PUT_LINE("The discount is "||v_discount*100||" percent");
20 EXCEPTION
21 WHEN OTHERS
22 THEN
23 DBMS_OUTPUT.PUT_LINE(SQLERRM);
24 END;
25 /
ORA-01403: no data found
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> UPDATE books
2 SET category = "Oracle Programming"
3 WHERE isbn = "72230665";
0 rows updated.
SQL> COMMIT;
Commit complete.
SQL>
SQL> DECLARE
2 v_category books.category%TYPE;
3 v_discount NUMBER(10,2);
4 v_isbn books.isbn%TYPE := "72230665";
5 BEGIN
6 SELECT category
7 INTO v_category
8 FROM books
9 WHERE isbn = v_isbn;
10
11 -- Determine discount based on category
12 CASE v_category
13 WHEN "Oracle Basics"
14 THEN v_discount := .15;
15 WHEN "Oracle Server"
16 THEN v_discount := .10;
17 ELSE v_discount := .5;
18 END CASE;
19
20 DBMS_OUTPUT.PUT_LINE("The discount is "||v_discount*100||" percent");
21 EXCEPTION
22 WHEN OTHERS
23 THEN
24 DBMS_OUTPUT.PUT_LINE(SQLERRM);
25 END;
26 /
ORA-01403: no data found
PL/SQL procedure successfully completed.
SQL>
SQL> drop table books;
Table dropped.
Use case statement in a dbms_output.put_line
SQL>
SQL> create or replace procedure proc3
2 (p1 in number)
3 is
4 BEGIN
5 dbms_output.put_line(CASE (p1)
6 WHEN 1 THEN "A"
7 WHEN 2 THEN "B"
8 WHEN 3 THEN "C"
9 END );
10
11 IF CASE (p1)
12 WHEN 1 THEN "A"
13 WHEN 2 THEN "B"
14 WHEN 3 THEN "C"
15 END
16 = "A" THEN dbms_output.put_line("A");
17 END IF;
18
19 END;
20 /
Procedure created.
SQL> show errors
No errors.
SQL> exec proc3(2)
PL/SQL procedure successfully completed.
SQL>
SQL>
Use case statement in procedure call to use the proper parameter value
SQL> DECLARE
2 salary NUMBER := 20000;
3 employee_id NUMBER := 36325;
4
5 PROCEDURE give_bonus (emp_id IN NUMBER, bonus_amt IN NUMBER) IS
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE(emp_id);
8 DBMS_OUTPUT.PUT_LINE(bonus_amt);
9 END;
10
11 BEGIN
12 give_bonus(employee_id,
13 CASE
14 WHEN salary >= 10000 AND salary <=20000 THEN 1500
15 WHEN salary > 20000 AND salary <= 40000 THEN 1000
16 WHEN salary > 40000 THEN 500
17 ELSE 0
18 END);
19 END;
20 /
PL/SQL procedure successfully completed.
SQL>
SQL>
Variable assignment with case statement
SQL> create or replace procedure proc2
2 (p1 in number)
3 is
4
5 v_switch char(1);
6
7 BEGIN
8
9 v_switch :=
10 CASE
11 WHEN p1 = 1 THEN "A"
12 WHEN p1 = 2 THEN "B"
13 WHEN p1 = 3 THEN "C"
14 END;
15
16 dbms_output.put_line(v_switch);
17
18 END;
19 /
Procedure created.
SQL> show errors
No errors.
SQL> exec proc2(1)
PL/SQL procedure successfully completed.
SQL>
SQL>
When creating selector CASE statements, you cannot have NULL in the list of possible values.
Although the following code is correct from the syntax point of view, it doesn"t work:
create or replace function f_getDateType (in_dt DATE)
return VARCHAR2
is
v_out VARCHAR2(10);
begin
case TO_CHAR(in_dt,"d")
when null then
-- value will be null if in_dt is null
v_out:="<NULL>";
when 1 then
v_out:="SUNDAY";
when 7 then
v_out:="SATURDAY";
else
v_out:="WEEKDAY";
end case;
return v_out;
end;
/