|
SQL> CREATE TABLE book (
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 book (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 book (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 book (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>
SQL> DECLARE
2
3 v_num_pages book.NUM_PAGES%TYPE;
4 v_isbn book.ISBN%TYPE := '3';
5
6 BEGIN
7
8 SELECT num_pages INTO v_num_pages FROM book WHERE isbn = v_isbn;
9
10 DBMS_OUTPUT.PUT_LINE('Number of pages before: '||v_num_pages);
11
12 v_num_pages := v_num_pages + 200;
13
14 UPDATE book SET num_pages = v_num_pages WHERE isbn = v_isbn;
15
16 DBMS_OUTPUT.PUT_LINE('Number of pages after: '||v_num_pages);
17
18 COMMIT;
19
20 EXCEPTION
21 WHEN OTHERS
22 THEN
23 DBMS_OUTPUT.PUT_LINE(SQLERRM);
24 ROLLBACK;
25 END;
26 /
Number of pages before: 404
Number of pages after: 604
PL/SQL procedure successfully completed.
SQL>
SQL> drop table book;
Table dropped.
|