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
<source lang="sql">
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></source>
CASE statements
A Traditional Condition Statement
<source lang="sql">
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.</source>
Case statement to call procedure
<source lang="sql">
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></source>
case when
<source lang="sql">
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.</source>
Named case block
<source lang="sql">
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></source>
Return statement with case
<source lang="sql">
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></source>
Simple CASE statement with range
<source lang="sql">
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></source>
Use CASE statement
<source lang="sql">
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.</source>
Use case statement in a dbms_output.put_line
<source lang="sql">
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></source>
Use case statement in procedure call to use the proper parameter value
<source lang="sql">
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></source>
Variable assignment with case statement
<source lang="sql">
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></source>
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:
<source lang="sql">
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;
/</source>
