Oracle PL/SQL/PL SQL/Case
Содержание
- 1 Case conditions
- 2 CASE not found while executing CASE statement
- 3 CASE WHEN statement with variable
- 4 CASE WHEN with comparison operator
- 5 CASE WHEN with constant
- 6 Case with else
- 7 Output discount rate based on different category
- 8 Simple case demo
- 9 This CASE statement is labeled
- 10 Uses a NUMBER datatype as the selector
Case conditions
<source lang="sql">
set echo on; set verify off; set define "&"; prompt "Input degree (numerically in degrees F)?:"; / accept temp default "100"; / declare
too_hot exception; too_cold exception;
begin
case
when "&temp" < 90.00 then raise too_cold;
when "&temp" > 140.00 then raise too_hot;
else null;
end case;
dbms_output.put_line("The temperature is just right");
exception
when VALUE_ERROR then
dbms_output.put_line("Please enter a numeric temperature (like 100)");
when too_hot then
dbms_output.put_line("The porridge is way too hot...");
when too_cold then
dbms_output.put_line("The porridge is way too cold...");
end; /
</source>
CASE not found while executing CASE statement
<source lang="sql">
SQL> DECLARE
2 v_TestVar NUMBER := 1;
3 BEGIN
4 CASE v_TestVar
5 WHEN 2 THEN DBMS_OUTPUT.PUT_LINE("Two!");
6 WHEN 3 THEN DBMS_OUTPUT.PUT_LINE("Three!");
7 WHEN 4 THEN DBMS_OUTPUT.PUT_LINE("Four!");
8 END CASE;
9 END;
10 /
DECLARE
ERROR at line 1: ORA-06592: CASE not found while executing CASE statement ORA-06512: at line 4
SQL> SQL>
</source>
CASE WHEN statement with variable
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> SQL> select * from lecturer;
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
-------------------- -------------------- ------------------------------ ---------------
- Scott Lawson Computer Science 11.00
- Mar Wells History 4.00
- Jone Bliss Computer Science 8.00
- Man Kyte Economics 8.00
- Pat Poll History 4.00
- Tim Viper History 4.00
- Barbara Blues Economics 7.00
- David Large Music 4.00
- Chris Elegant Nutrition 8.00
- Rose Bond Music 7.00
- Rita Johnson Nutrition 8.00
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
-------------------- -------------------- ------------------------------ ---------------
- Sharon Clear Computer Science 3.00
12 rows selected. SQL> DECLARE
2 v_Major lecturer.major%TYPE; 3 v_CourseName VARCHAR2(10); 4 BEGIN 5 SELECT major 6 INTO v_Major 7 FROM lecturer 8 WHERE ID = 10011; 9 10 CASE v_Major 11 WHEN "Computer Science" THEN 12 v_CourseName := "CS 101"; 13 WHEN "Economics" THEN 14 v_CourseName :="ECN 203"; 15 WHEN "History" THEN 16 v_CourseName := "HIS 101"; 17 WHEN "Music" THEN 18 v_CourseName := "MUS 100"; 19 WHEN "Nutrition" THEN 20 v_CourseName := "NUT 307"; 21 ELSE 22 v_CourseName := "Unknown"; 23 END CASE; 24 25 DBMS_OUTPUT.PUT_LINE(v_CourseName); 26 END; 27 /
NUT 307 PL/SQL procedure successfully completed. SQL> SQL> drop table lecturer; Table dropped.
</source>
CASE WHEN with comparison operator
<source lang="sql">
SQL> SQL> DECLARE
2 v_Test1 NUMBER := 2;
3 v_Test2 VARCHAR2(20) := "Goodbye";
4 BEGIN
5 CASE
6 WHEN v_Test1 = 1 THEN
7 DBMS_OUTPUT.PUT_LINE("One!");
8 DBMS_OUTPUT.PUT_LINE("Another one!");
9 WHEN v_Test1 > 1 THEN
10 DBMS_OUTPUT.PUT_LINE("> 1!");
11 DBMS_OUTPUT.PUT_LINE("Still > 1!");
12 WHEN v_Test2 = "Goodbye" THEN
13 DBMS_OUTPUT.PUT_LINE("Goodbye!");
14 DBMS_OUTPUT.PUT_LINE("Adios!");
15 ELSE
16 DBMS_OUTPUT.PUT_LINE("No match");
17 END CASE;
18 END;
19 /
> 1! Still > 1! PL/SQL procedure successfully completed. SQL> SQL>
</source>
CASE WHEN with constant
<source lang="sql">
SQL> SQL> SQL> DECLARE
2 v_TestVar NUMBER := 2;
3 BEGIN
4 CASE v_TestVar
5 WHEN 2 THEN DBMS_OUTPUT.PUT_LINE("Two!");
6 WHEN 3 THEN DBMS_OUTPUT.PUT_LINE("Three!");
7 WHEN 4 THEN DBMS_OUTPUT.PUT_LINE("Four!");
8 END CASE;
9 END;
10 /
Two! PL/SQL procedure successfully completed. SQL> SQL>
</source>
Case with else
<source lang="sql">
SQL> SQL> -- case with else SQL> SQL> declare
2 l_num number := 8;
3 begin
4 case l_num
5 when 1 then dbms_output.put_line("You selected one");
6 when 2 then dbms_output.put_line("You selected two");
7 when 3 then dbms_output.put_line("You selected three");
8 when 4 then dbms_output.put_line("You selected four");
9 when 5 then dbms_output.put_line("You selected five");
10 when 6 then dbms_output.put_line("You selected six");
11 when 7 then dbms_output.put_line("You selected seven");
12 when 8 then dbms_output.put_line("You selected eight");
13 when 9 then dbms_output.put_line("You selected nine");
14 when 0 then dbms_output.put_line("You selected zero");
15 else dbms_output.put_line("You selected more than one digit...");
16 end case;
17 end;
18 /
You selected eight PL/SQL procedure successfully completed. SQL>
</source>
Output discount rate based on different category
<source lang="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 emp1 NUMBER, 9 emp2 NUMBER, 10 emp3 NUMBER 11 );
Table created. SQL> SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("1", "Database", "Oracle", 563, 39.99, 2009, 1, 2, 3);
1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2)
2 VALUES ("2", "Database", "MySQL", 765, 44.99, 2009, 4, 5);
1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("3", "Database", "SQL Server", 404, 39.99, 2001, 6, 7, 8);
1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("4", "Database", "SQL", 535, 39.99, 2002, 4, 5, 9);
1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2)
2 VALUES ("5", "Database", "Java", 487, 39.99, 2002, 10, 11);
1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2)
2 VALUES ("6", "Database", "JDBC", 592, 39.99, 2002, 12, 13);
1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("7", "Database", "XML", 500, 39.99, 2002, 1, 2, 3);
1 row created. 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 := "3";
5 BEGIN
6 SELECT category INTO v_category FROM books WHERE isbn = v_isbn;
7
8 CASE v_category
9 WHEN "Database"
10 THEN v_discount := .5;
11 WHEN "Oracle Server"
12 THEN v_discount := .1;
13 END CASE;
14 DBMS_OUTPUT.PUT_LINE("The discount is "||v_discount*100||" percent");
15 EXCEPTION
16 WHEN OTHERS
17 THEN
18 DBMS_OUTPUT.PUT_LINE(SQLERRM);
19 END;
20 /
The discount is 50 percent PL/SQL procedure successfully completed. SQL> SQL> drop table books; Table dropped. SQL>
</source>
Simple case demo
<source lang="sql">
-- read user input set echo off set define "&" set verify off set serveroutput on size 10000 prompt accept NUM prompt "Enter a single digit number : " prompt declare
l_num number := &NUM;
begin
case l_num
when 1 then dbms_output.put_line("You selected one");
when 2 then dbms_output.put_line("You selected two");
when 3 then dbms_output.put_line("You selected three");
when 4 then dbms_output.put_line("You selected four");
when 5 then dbms_output.put_line("You selected five");
when 6 then dbms_output.put_line("You selected six");
when 7 then dbms_output.put_line("You selected seven");
when 8 then dbms_output.put_line("You selected eight");
when 9 then dbms_output.put_line("You selected nine");
when 0 then dbms_output.put_line("You selected zero");
end case;
end; /
</source>
This CASE statement is labeled
<source lang="sql">
SQL> SQL> DECLARE
2 v_TestVar NUMBER := 1;
3 BEGIN
4 <<MyCase>>
5 CASE v_TestVar
6 WHEN 1 THEN DBMS_OUTPUT.PUT_LINE("One!");
7 WHEN 2 THEN DBMS_OUTPUT.PUT_LINE("Two!");
8 WHEN 3 THEN DBMS_OUTPUT.PUT_LINE("Three!");
9 WHEN 4 THEN DBMS_OUTPUT.PUT_LINE("Four!");
10 END CASE MyCase;
11 END;
12 /
One! PL/SQL procedure successfully completed. SQL>
</source>
Uses a NUMBER datatype as the selector
<source lang="sql">
SQL> SQL> DECLARE
2 selector NUMBER := 0;
3 BEGIN
4 CASE selector
5 WHEN 0 THEN
6 dbms_output.put_line("Case 0!");
7 WHEN 1 THEN
8 dbms_output.put_line("Case 1!");
9 ELSE
10 dbms_output.put_line("No match!");
11 END CASE;
12 END;
13 /
Case 0! PL/SQL procedure successfully completed. SQL> SQL>
</source>
