Output discount rate based on different category : Case « PL SQL « Oracle PL / 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>

   
    
    
    
  








Related examples in the same category

1.Simple case demo
2.Case conditions
3.Case with else
4.CASE WHEN statement with variable
5.CASE WHEN with constant
6.CASE not found while executing CASE statement
7.This CASE statement is labeled
8.CASE WHEN with comparison operator
9.Uses a NUMBER datatype as the selector