|
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> CREATE TABLE inventory (
2 isbn CHAR(10),
3 status VARCHAR2(25) CHECK (status IN ('IN STOCK', 'BACKORDERED', 'FUTURE')),
4 status_date DATE,
5 amount NUMBER
6 );
SQL>
SQL> INSERT INTO inventory (isbn, status, status_date, amount)VALUES ('1', 'BACKORDERED', TO_DATE('06-JUN-2004', 'DD-MON-YYYY'), 1000);
SQL> INSERT INTO inventory (isbn, status, status_date, amount)VALUES ('2', 'IN STOCK', NULL, 5000);
SQL> INSERT INTO inventory (isbn, status, status_date, amount)VALUES ('3', 'IN STOCK', NULL, 1000);
SQL>
SQL>
SQL> DECLARE
2
3 v_isbn INVENTORY.ISBN%TYPE;
4 v_amount INVENTORY.AMOUNT%TYPE;
5
6 CURSOR inventory_cur IS SELECT isbn, amount FROM inventory
7 WHERE status = 'IN STOCK'
8 AND isbn IN (SELECT isbn FROM book)
9 FOR UPDATE OF amount;
10
11 BEGIN
12
13 FOR y IN inventory_cur
14 LOOP
15 FETCH inventory_cur INTO v_isbn, v_amount;
16 EXIT WHEN inventory_cur%NOTFOUND;
17
18 DBMS_OUTPUT.PUT_LINE(v_isbn||'Amount IN STOCK before: '||v_amount);
19
20 v_amount := v_amount + 50;
21
22 UPDATE inventory SET amount = v_amount WHERE CURRENT OF inventory_cur;
23
24 DBMS_OUTPUT.PUT_LINE(v_isbn||'Amount IN STOCK after: '||v_amount);
25
26 END LOOP;
27
28 COMMIT;
29
30 EXCEPTION
31 WHEN OTHERS
32 THEN
33 DBMS_OUTPUT.PUT_LINE(SQLERRM);
34 ROLLBACK;
35 END;
36 /
SQL> drop table book;
Table dropped.
|