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
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;
/
CASE not found while executing CASE statement
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>
CASE WHEN statement with variable
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.
CASE WHEN with comparison operator
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>
CASE WHEN with constant
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>
Case with else
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>
Output discount rate based on different category
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>
Simple case demo
-- 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;
/
This CASE statement is labeled
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>
Uses a NUMBER datatype as the selector
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>