pseudocolumn LEVEL and an example of using the levels. : Hierarchical Queries « Query Select « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Query Select
3. Hierarchical Queries

SQL>
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));

Table created.

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);

1 row created.

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);

1 row created.

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);

1 row created.

SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     v_level PLS_INTEGER;
  3     v_title book.TITLE%TYPE;
  4     CURSOR cur_tree IS SELECT isbn, title, series FROM book;
  5  BEGIN
  6  FOR l IN cur_tree
  7  LOOP
  8      SELECT max(LEVEL) INTO v_level FROM book
  9      START WITH isbn = l.isbn
 10      CONNECT BY PRIOR parent_isbn = isbn;
 11
 12      DBMS_OUTPUT.PUT_LINE(l.title||' is book '||v_level||' in the '||l.series||' series');
 13
 14  END LOOP;
 15  CLOSE cur_tree;
 16  EXCEPTION
 17     WHEN OTHERS
 18     THEN
 19        DBMS_OUTPUT.PUT_LINE(sqlerrm);
 20  END;
 21  /
SQL is book 2 in the Oracle series
Java is book 1 in the Oracle series
XML is book 3 in the Oracle series
ORA-01001: invalid cursor

PL/SQL procedure successfully completed.

SQL>
SQL> drop table book;

Table dropped.