Oracle PL/SQL Tutorial/PL SQL Statements/CASE — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Версия 16:45, 26 мая 2010
Содержание
- 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>