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>