第105页 第4章 Level.sql

/*
 * Level.sql
 * Chapter 4, Oracle10g PL/SQL Programming
 * by Ron Hardman, Mike McLaughlin, Scott Urman
 *
 * This script shows the pseudocolumn LEVEL and an 
 * example of using the levels.  
 */

exec clean_schema.trigs
exec clean_schema.procs
exec clean_schema.tables
exec clean_schema.ind

-- 创建表books;

CREATE TABLE books (
  isbn      VARCHAR2(10) PRIMARY KEY,
  parent_isbn VARCHAR2(10),
  series    VARCHAR2(20),
  category  VARCHAR2(20),
  title     VARCHAR2(100),
  num_pages NUMBER,
  price     NUMBER,
  copyright NUMBER(4));
--向books表中插入数据；
INSERT INTO books (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
  VALUES ('72191473', '72121467', 'Oracle PL/SQL', 'Oracle Server', 'Oracle9i PL/SQL Programming', 664, 49.99, 2002);

INSERT INTO books (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
  VALUES ('72121467', null, 'Oracle PL/SQL', 'Oracle Server', 'Oracle8i Advanced PL/SQL Programming', 772, 49.99, 2000);

INSERT INTO books (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
  VALUES ('72230665', '72191473', 'Oracle PL/SQL', 'Oracle Server', 'Oracle Database 10g PL/SQL Programming', 1008, 54.99, 2004);

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

commit;
--参数设置完毕；
--开启屏幕输出设置；
SET SERVEROUTPUT ON

PROMPT
PROMPT ** The following is a demonstration of LEVEL, and the 
PROMPT **  use of START WITH ... CONNECT BY PRIOR to display
PROMPT **  parent/child hierarchical relationships.
PROMPT

DECLARE
--pls_integer类型是数字类型，只能存储-2147483647到+2147483647之间的整数，
   v_level PLS_INTEGER;
--v_title 为变量名，BOOKS为表名，TITLE为表BOOKS的一个列名；
--把书名列锚定在表BOOKS上；
--%TYPE将声明的变量直接映射到数据库的TITLE列上；
   v_title BOOKS.TITLE%TYPE;

--设定游标，指定的项为 isbn , title , series;
   CURSOR cur_tree
   IS
      SELECT isbn, title, series
      FROM books;
BEGIN

- - 循环主体：Loop through the cursor, one record at a time
- - 使用Loop遍历游标。
FOR l IN cur_tree
LOOP

SELECT max(LEVEL)
INTO v_level
FROM books
START WITH isbn = l.isbn
CONNECT BY PRIOR parent_isbn = isbn;

DBMS_OUTPUT.PUT_LINE(l.title||' is book '
                     ||v_level||' in the '||l.series||' series');

END LOOP;

CLOSE cur_tree;

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;
/