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 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>
SQL> DECLARE
2 v_price books.price%TYPE;
3 v_isbn books.isbn%TYPE := '3';
4 BEGIN
5 SELECT price INTO v_price FROM books WHERE isbn = v_isbn;
6
7 DBMS_OUTPUT.PUT_LINE('Starting price: '||v_price);
8
9 IF v_price < 40
10 THEN
11 DBMS_OUTPUT.PUT_LINE('This book is already discounted');
12 ELSIF v_price BETWEEN 40 AND 50
13 THEN
14 v_price := v_price - (v_price * .10);
15 UPDATE books SET price = v_price WHERE isbn = v_isbn;
16 ELSIF v_price > 50
17 THEN
18 v_price := v_price - (v_price * .10);
19 UPDATE books SET price = v_price WHERE isbn = v_isbn;
20 END IF;
21
22 DBMS_OUTPUT.PUT_LINE('Ending price: '||v_price);
23
24 ROLLBACK;
25 EXCEPTION
26 WHEN OTHERS
27 THEN
28 DBMS_OUTPUT.PUT_LINE(SQLERRM);
29 ROLLBACK;
30 END;
31 /
Starting price: 39.99
This book is already discounted
Ending price: 39.99
PL/SQL procedure successfully completed.
SQL> drop table books;
Table dropped.
SQL>