Run an anonymous block that updates the number of book IN STOCK : Update Data « PL SQL « Oracle PL / SQL

1. Oracle PL / SQL
2. PL SQL
3. Update Data

Run an anonymous block that updates the number of book IN STOCK

    
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.

   
    
    
    
  

Related examples in the same category