ch20.sql

OReilly Oracle PL SQL Programming第4版源代码

/*
| Here are the code snippets from Chapter 20 of 
| Oracle PL/SQL Programming, Third Edition.
|
| The purpose of this script is to demonstrate the features
| and syntax--not to do anything else particularly useful.
|
| In order to run this, you will need the DBA to grant you the
| following privileges:
|
|    CONNECT, CREATE TABLE, CREATE TYPE, CREATE PROCEDURE
|
| You will also need some unused space quota on your default tablespace.
|
| This version of the script was designed to work with 9i Release 2.
|
| You may want to SET ECHO ON and SPOOL to some convenient output file
| in order to examine the code and its results together.
|
| WARNING: Examine the DROP statements -- be sure you have no "real" 
| objects matching these names.
*/

SET SERVEROUT ON SIZE 1000000 
SET LINESIZE 80
SET PAGESIZE 25
SET PAUSE OFF

WHENEVER SQLERROR CONTINUE

COLUMN column_name FORMAT A30
COLUMN data_type FORMAT A30
COLUMN value(c).id FORMAT 99999
COLUMN value(c).print() FORMAT A60 WORD_WRAP

DROP TABLE catalog_history;
DROP TYPE subject_names_t FORCE;
DROP TABLE catalog_items;
DROP TABLE subjects;
DROP TYPE subject_refs_t FORCE;
DROP TYPE subject_t FORCE;
DROP TYPE book_t FORCE;
DROP TYPE serial_t FORCE;
DROP TYPE catalog_item_t FORCE;

/*------------------------------------------------------------*/
/* 
| ........here begins the material in the chapter .....  
|  (with the addition of some SHOW ERR commands)
*/

/*------------------------------------------------------------*/
CREATE OR REPLACE TYPE catalog_item_t AS OBJECT (
   id INTEGER,
   title VARCHAR2(4000),
   NOT INSTANTIABLE MEMBER FUNCTION ck_digit_okay
      RETURN BOOLEAN,
   MEMBER FUNCTION print
      RETURN VARCHAR2
) NOT INSTANTIABLE NOT FINAL;
/
SHOW ERR

/*------------------------------------------------------------*/
CREATE OR REPLACE TYPE book_t UNDER catalog_item_t (
   isbn VARCHAR2(13),
   pages INTEGER,

   CONSTRUCTOR FUNCTION book_t (id IN INTEGER DEFAULT NULL,
      title IN VARCHAR2 DEFAULT NULL,
      isbn IN VARCHAR2 DEFAULT NULL,
      pages IN INTEGER DEFAULT NULL)
      RETURN SELF AS RESULT,

   OVERRIDING MEMBER FUNCTION ck_digit_okay
      RETURN BOOLEAN,

   OVERRIDING MEMBER FUNCTION print
      RETURN VARCHAR2
);
/
SHOW ERR

/*------------------------------------------------------------*/
CREATE OR REPLACE TYPE BODY book_t
AS
   CONSTRUCTOR FUNCTION book_t (id IN INTEGER,
      title IN VARCHAR2,
      isbn IN VARCHAR2,
      pages IN INTEGER)
      RETURN SELF AS RESULT
   IS
   BEGIN
      SELF.id := id;
      SELF.title := title;
      SELF.isbn := isbn;
      SELF.pages := pages;
      IF isbn IS NULL OR SELF.ck_digit_okay
      THEN
         RETURN;
      ELSE
         RAISE_APPLICATION_ERROR(-20000, 'ISBN ' || isbn
            || ' has bad check digit');
      END IF;
   END;

   OVERRIDING MEMBER FUNCTION ck_digit_okay
      RETURN BOOLEAN
   IS
      subtotal PLS_INTEGER := 0;
      isbn_digits VARCHAR2(10);
   BEGIN
      /* remove dashes and spaces */
      isbn_digits := REPLACE(REPLACE(SELF.isbn, '-'), ' ');
      IF LENGTH(isbn_digits) != 10
      THEN
         RETURN FALSE;
      END IF;

      FOR nth_digit IN 1..9
      LOOP
         subtotal := subtotal + 
            (11 - nth_digit) * TO_NUMBER(SUBSTR(isbn_digits, nth_digit, 1));
      END LOOP;

      /* check digit can be 'X' which has value of 10 */
      IF UPPER(SUBSTR(isbn_digits, 10, 1)) = 'X'
      THEN
         subtotal := subtotal + 10;
      ELSE
         subtotal := subtotal + TO_NUMBER(SUBSTR(isbn_digits, 10, 1));
      END IF;

      RETURN MOD(subtotal, 11) = 0;

   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN FALSE;
   END;

   OVERRIDING MEMBER FUNCTION print
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN 'id=' || id || '; title=' || title
         || '; isbn=' || isbn || '; pages=' || pages;
   END;
END;
/
SHOW ERR

/*------------------------------------------------------------*/
DECLARE
   generic_item catalog_item_t;
   abook book_t;
BEGIN
   abook := NEW book_t(title => 'Out of the Silent Planet',
      isbn => '0-6848-238-02');
   generic_item := abook;
   DBMS_OUTPUT.PUT_LINE('BOOK: ' || abook.print());
   DBMS_OUTPUT.PUT_LINE('ITEM: ' || generic_item.print());
END;
/

/*---------BEGIN EXTRA CODE NOT IN BOOK-----------------------*/
/*------------------------------------------------------------*/
CREATE OR REPLACE TYPE serial_t UNDER catalog_item_t (
   issn VARCHAR2(10),
   open_or_closed VARCHAR2(1),

   CONSTRUCTOR FUNCTION serial_t (id IN INTEGER DEFAULT NULL,
      title IN VARCHAR2 DEFAULT NULL,
      issn IN VARCHAR2 DEFAULT NULL,
      open_or_closed IN VARCHAR2 DEFAULT NULL)
      RETURN SELF AS RESULT,

   OVERRIDING MEMBER FUNCTION ck_digit_okay
      RETURN BOOLEAN,

   OVERRIDING MEMBER FUNCTION print
      RETURN VARCHAR2
) NOT FINAL;
/
SHOW ERR
/*-----------END EXTRA CODE NOT IN BOOK-----------------------*/


/*------------------------------------------------------------*/
CREATE OR REPLACE TYPE BODY serial_t
AS
   CONSTRUCTOR FUNCTION serial_t (id IN INTEGER,
      title IN VARCHAR2,
      issn IN VARCHAR2,
      open_or_closed IN VARCHAR2)
      RETURN SELF AS RESULT
   IS
   BEGIN
      SELF.id := id;
      SELF.title := title;
      SELF.issn := issn;
      SELF.open_or_closed := open_or_closed;
      IF issn IS NULL OR SELF.ck_digit_okay
      THEN
         RETURN;
      ELSE
         RAISE_APPLICATION_ERROR(-20000, 'ISSN ' || issn || ' has bad check digit');
      END IF;
   END;

   OVERRIDING MEMBER FUNCTION ck_digit_okay
      RETURN BOOLEAN
   IS
      subtotal PLS_INTEGER := 0;
      issn_digits VARCHAR2(8);
   BEGIN
      issn_digits := REPLACE(REPLACE(SELF.issn, '-'), ' ');
      IF LENGTH(issn_digits) != 8
      THEN
         RETURN FALSE;
      END IF;

      FOR nth_digit IN 1..7
      LOOP
         subtotal := subtotal + (9 - nth_digit) * TO_NUMBER(SUBSTR(issn_digits, nth_digit, 1));
      END LOOP;

      /* check digit can be 'X' which has value of 10 */
      IF UPPER(SUBSTR(issn_digits, 8, 1)) = 'X'
      THEN
         subtotal := subtotal + 10;
      ELSE
         subtotal := subtotal + TO_NUMBER(SUBSTR(issn_digits, 8, 1));
      END IF;

      RETURN MOD(subtotal, 11) = 0;

   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN FALSE;
   END;

   OVERRIDING MEMBER FUNCTION print
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN 'id=' || id || '; title=' || title || '; issn=' || issn
         || '; open_or_closed=' 
         || CASE open_or_closed
               WHEN 'O' THEN 'Open'
               WHEN 'C' THEN 'Closed'
               ELSE NULL
            END;
   END;

END;
/
SHOW ERR


/*------------------------------------------------------------*/
DECLARE
   generic_item catalog_item_t;
   abook book_t;
   ajournal serial_t;
BEGIN
   abook := NEW book_t(id => 10001, title => 'Learning Oracle PL/SQL',
      isbn => '0-596-00180-0', pages => 450);
   generic_item := abook;
   IF generic_item.ck_digit_okay
   THEN
      DBMS_OUTPUT.PUT_LINE('a-okay, boss');
   ELSE
      DBMS_OUTPUT.PUT_LINE('try again, dude');
   END IF;
   ajournal := NEW serial_t(id => 10002, title=> 'Time', issn => '0040-781X',
      open_or_closed => 'O');
   generic_item := ajournal;
   IF generic_item.ck_digit_okay
   THEN
      DBMS_OUTPUT.PUT_LINE('a-okay, boss');
   ELSE
      DBMS_OUTPUT.PUT_LINE('try again, dude');
   END IF;
END;
/

/*------------------------------------------------------------*/
CREATE TABLE catalog_items OF catalog_item_t
   (CONSTRAINT catalog_items_pk PRIMARY KEY (id))
    /* OBJECT IDENTIFIER IS PRIMARY KEY */
;

/*------------------------------------------------------------*/
SELECT column_name, data_type, hidden_column, virtual_column
  FROM user_tab_cols
 WHERE table_name = 'CATALOG_ITEMS';

/*------------------------------------------------------------*/
INSERT INTO catalog_items
   VALUES (NEW book_t(10003, 'Perelandra', '0-684-82382-9', 222));
INSERT INTO catalog_items
   VALUES (NEW serial_t(10004, 'Time', '0040-781X', 'O'));

COMMIT;

/*------------------------------------------------------------*/
SELECT VALUE(c)
  FROM catalog_items c;

/*------------------------------------------------------------*/
SELECT VALUE(c).id, VALUE(c).print()
  FROM catalog_items c;

/*------------------------------------------------------------*/
DECLARE
   catalog_item catalog_item_t;
   CURSOR ccur IS
      SELECT VALUE(c)
        FROM catalog_items c;
BEGIN
   OPEN ccur;
   FETCH ccur INTO catalog_item;
   DBMS_OUTPUT.PUT_LINE('I fetched item #' || catalog_item.id);
   CLOSE ccur;
END;
/

/*------------------------------------------------------------*/
/* 
| This example modified from first printing of book, which
| is a bit confusing.
*/
UPDATE catalog_items c
   SET c = NEW book_t(c.id, c.title,
                     (SELECT TREAT(VALUE(y) AS book_t).isbn
                        FROM catalog_items y
                        WHERE id = 10003),
                     1000)
 WHERE id = 10003;

ROLLBACK;


/*------------------------------------------------------------*/
DECLARE
   book book_t;
   catalog_item catalog_item_t := NEW book_t();
BEGIN
   book := TREAT (catalog_item AS book_t);
END;
/


DECLARE
   abook book_t;
   CURSOR ccur IS
      SELECT TREAT (VALUE(c) AS book_t)
        FROM catalog_items c
       WHERE VALUE(c) IS OF (book_t);
BEGIN
   OPEN ccur;
   FETCH ccur INTO abook;
   DBMS_OUTPUT.PUT_LINE('I fetched a book with ISBN ' || abook.isbn);
   CLOSE ccur;
END;
/


DECLARE
   CURSOR ccur IS
      SELECT VALUE(c) item
        FROM catalog_items c;
   arec ccur%ROWTYPE;
BEGIN
   FOR arec IN ccur
   LOOP
      CASE
         WHEN arec.item IS OF (book_t)
         THEN
            DBMS_OUTPUT.PUT_LINE('Found a book with ISBN ' 
               || TREAT(arec.item AS book_t).isbn);
         WHEN arec.item IS OF (serial_t)
         THEN
            DBMS_OUTPUT.PUT_LINE('Found a serial with ISSN '
               || TREAT(arec.item AS serial_t).issn);
         ELSE
            DBMS_OUTPUT.PUT_LINE('Found unknown catalog item');
      END CASE;
   END LOOP;
END;
/

/*------------------------------------------------------------*/
ALTER TYPE catalog_item_t
   ADD ATTRIBUTE publication_date VARCHAR2(400)
   CASCADE INCLUDING TABLE DATA;

/* Note:
|| You may need to disconnect and reconnect in order to see
|| the next describe
*/

DESC catalog_item_t

DESC catalog_items

ALTER TYPE book_t
   DROP CONSTRUCTOR FUNCTION book_t (id INTEGER DEFAULT NULL,
      title VARCHAR2 DEFAULT NULL,
      isbn VARCHAR2 DEFAULT NULL,
      pages INTEGER DEFAULT NULL)
      RETURN SELF AS RESULT
   CASCADE;

ALTER TYPE book_t
   ADD CONSTRUCTOR FUNCTION book_t (id INTEGER DEFAULT NULL,
      title VARCHAR2 DEFAULT NULL,
      publication_date VARCHAR2 DEFAULT NULL,
      isbn VARCHAR2 DEFAULT NULL,
      pages INTEGER DEFAULT NULL)
      RETURN SELF AS RESULT
   CASCADE;

/*---------BEGIN EXTRA CODE NOT IN BOOK-----------------------*/
CREATE OR REPLACE TYPE BODY book_t
AS
   CONSTRUCTOR FUNCTION book_t (id IN INTEGER,
      title IN VARCHAR2,
      publication_date IN VARCHAR2,
      isbn IN VARCHAR2,
      pages IN INTEGER)
      RETURN SELF AS RESULT
   IS
   BEGIN
      SELF.id := id;
      SELF.title := title;
      SELF.publication_date := publication_date;
      SELF.isbn := isbn;
      SELF.pages := pages;
      IF isbn IS NULL OR SELF.ck_digit_okay
      THEN
         RETURN;
      ELSE
         RAISE_APPLICATION_ERROR(-20000, 'ISBN ' || isbn
            || ' has bad check digit');
      END IF;
   END;

   OVERRIDING MEMBER FUNCTION ck_digit_okay
      RETURN BOOLEAN
   IS
      subtotal PLS_INTEGER := 0;
      isbn_digits VARCHAR2(10);