Run an anonymous block that updates the number of pages for this book : 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 pages for this book

    

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.

   
    
    
    
  

Related examples in the same category