Mega Code Archive
Use CASE statement
<![CDATA[
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 );
Table created.
SQL>
SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright)
2 VALUES ('72122048', 'Oracle Basics', 'Oracle8i: A Beginner''s Guide', 765, 44.99, 1999);
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 := '72230665';
5 BEGIN
6 SELECT category
7 INTO v_category
8 FROM books
9 WHERE isbn = v_isbn;
10
11 -- Determine discount based on category
12 CASE v_category
13 WHEN 'Oracle Basics'
14 THEN v_discount := .15;
15 WHEN 'Oracle Server'
16 THEN v_discount := .10;
17 END CASE;
18
19 DBMS_OUTPUT.PUT_LINE('The discount is '||v_discount*100||' percent');
20 EXCEPTION
21 WHEN OTHERS
22 THEN
23 DBMS_OUTPUT.PUT_LINE(SQLERRM);
24 END;
25 /
ORA-01403: no data found
PL/SQL procedure successfully completed.
SQL>
SQL> UPDATE books
2 SET category = 'Oracle Programming'
3 WHERE isbn = '72230665';
0 rows updated.
SQL> COMMIT;
Commit complete.
SQL>
SQL> DECLARE
2 v_category books.category%TYPE;
3 v_discount NUMBER(10,2);
4 v_isbn books.isbn%TYPE := '72230665';
5 BEGIN
6 SELECT category
7 INTO v_category
8 FROM books
9 WHERE isbn = v_isbn;
10
11 -- Determine discount based on category
12 CASE v_category
13 WHEN 'Oracle Basics'
14 THEN v_discount := .15;
15 WHEN 'Oracle Server'
16 THEN v_discount := .10;
17 ELSE v_discount := .5;
18 END CASE;
19
20 DBMS_OUTPUT.PUT_LINE('The discount is '||v_discount*100||' percent');
21 EXCEPTION
22 WHEN OTHERS
23 THEN
24 DBMS_OUTPUT.PUT_LINE(SQLERRM);
25 END;
26 /
ORA-01403: no data found
PL/SQL procedure successfully completed.
SQL>
SQL> drop table books;
Table dropped.
]]>