Oracle PL/SQL Tutorial/PL SQL Programming/LEVEL

Use the pseudocolumn LEVEL

SQL>
SQL> CREATE TABLE books (
  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));
Table created.
SQL>
SQL> INSERT INTO books (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
  2    VALUES ("72191473", "72121467", "Oracle PL/SQL", "Oracle Server", "Oracle9i PL/SQL Programming", 664, 49.99, 2002);
1 row created.
SQL>
SQL> INSERT INTO books (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
  2    VALUES ("72121467", null, "Oracle PL/SQL", "Oracle Server", "Oracle8i Advanced PL/SQL Programming", 772, 49.99, 2000);
1 row created.
SQL>
SQL> INSERT INTO books (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
  2    VALUES ("72230665", "72191473", "Oracle PL/SQL", "Oracle Server", "Oracle Database 10g PL/SQL Programming", 1008, 54.99, 2004);
1 row created.
SQL>
SQL> INSERT INTO books (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
  2    VALUES ("72132302", null, "Oracle Ebusiness", "Oracle Ebusiness", "Oracle E-Business Suite Financials Handbook", 820, 59.99, 2002);
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> PROMPT
SQL> PROMPT ** The following is a demonstration of LEVEL, and the
** The following is a demonstration of LEVEL, and the
SQL> PROMPT **  use of START WITH ... CONNECT BY PRIOR to display
**  use of START WITH ... CONNECT BY PRIOR to display
SQL> PROMPT **  parent/child hierarchical relationships.
**  parent/child hierarchical relationships.
SQL> PROMPT
SQL>
SQL> DECLARE
  2     v_level PLS_INTEGER;
  3     v_title BOOKS.TITLE%TYPE;
  4
  5     CURSOR cur_tree
  6     IS
  7        SELECT isbn, title, series
  8        FROM books;
  9  BEGIN
 10
 11  FOR l IN cur_tree
 12  LOOP
 13
 14  SELECT max(LEVEL)
 15  INTO v_level
 16  FROM books
 17  START WITH isbn = l.isbn
 18  CONNECT BY PRIOR parent_isbn = isbn;
 19
 20  DBMS_OUTPUT.PUT_LINE(l.title||" is book "
 21                       ||v_level||" in the "||l.series||" series");
 22
 23  END LOOP;
 24
 25  CLOSE cur_tree;
 26
 27  EXCEPTION
 28     WHEN OTHERS
 29     THEN
 30        DBMS_OUTPUT.PUT_LINE(sqlerrm);
 31  END;
 32  /
Oracle9i PL/SQL Programming is book 2 in the Oracle PL/SQL series
Oracle8i Advanced PL/SQL Programming is book 1 in the Oracle PL/SQL series
Oracle Database 10g PL/SQL Programming is book 3 in the Oracle PL/SQL series
Oracle E-Business Suite Financials Handbook is book 1 in the Oracle Ebusiness series
ORA-01001: invalid cursor
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> drop table books;
Table dropped.