Oracle PL/SQL/PL SQL/Case

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 := #
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>