Run the anonymous block to update the position column

    

SQL> CREATE TABLE book (
  2    isbn      VARCHAR2(10) PRIMARY KEY,
  3    parent_isbn VARCHAR2(10),
  4    series    VARCHAR2(20),
  5    category  VARCHAR2(20),
  6    title     VARCHAR2(100),
  7    num_pages NUMBER,
  8    price     NUMBER,
  9    copyright NUMBER(4));



SQL>
SQL> INSERT INTO book (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
  2    VALUES ('1', '2', 'Oracle', 'Oracle Server', 'SQL', 664, 49.99, 2002);


SQL>
SQL> INSERT INTO book (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
  2    VALUES ('2', null, 'Oracle', 'Oracle Server', 'Java', 772, 49.99, 2000);



SQL>
SQL> INSERT INTO book (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
  2    VALUES ('3', '1', 'Oracle', 'Oracle Server', 'XML', 1008, 54.99, 2004);



SQL>
SQL> INSERT INTO book (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
  2    VALUES ('4', null, 'Oracle Ebusiness', 'Oracle Ebusiness', 'Oracle E-Business Suite Financials Handbook', 820, 59.99, 2002);



SQL>
SQL>
SQL> ALTER TABLE book
  2  ADD position NUMBER(10);


SQL>
SQL> PROMPT
SQL>
SQL> DECLARE
  2     v_level PLS_INTEGER;
  3     v_title book.TITLE%TYPE;
  4
  5     CURSOR cur_tree IS SELECT isbn, title, series FROM book;
  6  BEGIN
  7
  8  FOR l IN cur_tree
  9  LOOP
 10
 11     SELECT max(LEVEL) INTO v_level FROM book START WITH isbn = l.isbn CONNECT BY PRIOR parent_isbn = isbn;
 12
 13     UPDATE book SET position = v_level WHERE isbn = l.isbn;
 14
 15  END LOOP;
 16
 17  COMMIT;
 18
 19  EXCEPTION
 20     WHEN OTHERS
 21     THEN
 22        DBMS_OUTPUT.PUT_LINE(sqlerrm);
 23  END;
 24  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT title, position FROM book ORDER BY series, position;

TITLE
--------------------------------------------------------------------------------
  POSITION
----------
Oracle8.0 PL/SQL Programming
         1

Java
         2

SQL
         3

XML
         4

Oracle E-Business Suite Financials Handbook
         1


5 rows selected.



SQL> drop table book;

Table dropped.

Related examples in the same category

1.	UPDATE statement can be used within PL/SQL programs to update a row or a set of rows
2.	Select for update
3.	Check row count being updating
4.	Update with variable
5.	Two UPDATE statements.
6.	Check SQL%ROWCOUNT after updating
7.	Exception handling for update statement
8.	Update salary with stored procedure
9.	Update table and return if success
10.	Decrease salary with user procedure
11.	Change price and output the result
12.	Run an anonymous block that updates the number of book IN STOCK
13.	Run an anonymous block that updates the number of pages for this book
14.	Ajust price based on price range
15.	Bundle several update and insert statements into one procedure
16.	Use procedure to update table

Run the anonymous block to update the position column : Update Data « PL SQL « Oracle PL / SQL

Related examples in the same category