ch20.sql

OReilly Oracle PL SQL Programming第4版源代码

      THEN
         RETURN str;
      ELSE
         UTL_REF.SELECT_OBJECT(SELF.broader_term_ref, bt);
         RETURN bt.print_bt(NVL(str,SELF.name)) || ' (' || bt.name || ')';
      END IF;
   END;
END;
/
SHOW ERR

DECLARE
   book book_t;
   item catalog_item_t;
   itemref REF catalog_item_t;
   bookref REF book_t;
BEGIN
   /* Of course a REF that exactly matches variable's type works fine */
   SELECT REF(c) INTO itemref FROM catalog_items c WHERE id = 10007;

   /* Similarly, you can dereference into the exact type */
   UTL_REF.select_object(itemref, item);
   SELECT DEREF(itemref) INTO item FROM DUAL;

   /* However, you cannot narrow a REF:
   SELECT REF(c) INTO bookref FROM catalog_items c WHERE id = 10007;
   ...
   BUT you can downcast it explicitly:
   */
   SELECT TREAT(REF(c) AS ref book_t) INTO bookref FROM catalog_items c WHERE id = 10007;

   /* You can widen or upcast while derefencing: */
   UTL_REF.select_object(TREAT(bookref AS ref catalog_item_t), item);
   SELECT DEREF(bookref) INTO item FROM DUAL;

   /* And, although you cannot narrow or downcast while DEREFing:
   | SELECT DEREF(itemref) INTO book FROM DUAL;
   | you can do the expected downcast with TREAT:
   */
   SELECT DEREF(TREAT(itemref AS REF book_t)) INTO book FROM catalog_items c WHERE id = 10007;

   /* Or, amazingly enough, you CAN do it with UTL_REF: */
   UTL_REF.select_object(itemref, book);


END;
/


CREATE OR REPLACE FUNCTION printany (adata IN ANYDATA)
   RETURN VARCHAR2      
AS
   aType ANYTYPE;
   retval VARCHAR2(32767);
   result_code PLS_INTEGER;
BEGIN
   CASE adata.GetType(aType)
   WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
      RETURN 'NUMBER: ' || TO_CHAR(adata.AccessNumber);
   WHEN DBMS_TYPES.TYPECODE_VARCHAR2 THEN
      RETURN 'VARCHAR2: ' || adata.AccessVarchar2;
   WHEN DBMS_TYPES.TYPECODE_CHAR THEN
      RETURN 'CHAR: ' || RTRIM(adata.AccessChar);
   WHEN DBMS_TYPES.TYPECODE_DATE THEN
      RETURN 'DATE: ' || TO_CHAR(adata.AccessDate, 'YYYY-MM-DD hh24:mi:ss');
   WHEN DBMS_TYPES.TYPECODE_OBJECT THEN
      EXECUTE IMMEDIATE 'DECLARE ' ||
                        '   myobj ' || adata.GetTypeName || '; ' ||
                        '   myad sys.ANYDATA := :ad; ' ||
                        'BEGIN ' ||
                        '   :res := myad.GetObject(myobj); ' ||
                        '   :ret := myobj.print(); ' ||
                        'END;'
                        USING IN adata, OUT result_code, OUT retval;
      retval := adata.GetTypeName || ': ' || retval;
   WHEN DBMS_TYPES.TYPECODE_REF THEN
      EXECUTE IMMEDIATE 'DECLARE ' ||
                        '   myref ' || adata.GetTypeName || '; ' ||
                        '   myobj ' || SUBSTR(adata.GetTypeName,
                                       INSTR(adata.GetTypeName, ' ')) || '; ' ||
                        '   myad sys.ANYDATA := :ad; ' ||
                        'BEGIN ' ||
                        '   :res := myad.GetREF(myref); ' ||
                        '   UTL_REF.SELECT_OBJECT(myref, myobj);' ||
                        '   :ret := myobj.print(); ' ||
                        'END;'
                        USING IN adata, OUT result_code, OUT retval;
      retval := adata.GetTypeName || ': ' || retval;
   ELSE
      retval := '<data of type ' || adata.GetTypeName ||'>';
   END CASE;

   RETURN retval;

EXCEPTION
   WHEN OTHERS
   THEN
      IF INSTR(SQLERRM, 'component ''PRINT'' must be declared') > 0
      THEN
         RETURN adata.GetTypeName || ': <no print() function>';
      ELSE
         RETURN 'Error: ' || SQLERRM;
      END IF;
END;
/

show err
   
DECLARE
   achar CHAR(20) := 'fixed-length string';
   abook book_t := NEW book_t(id => 12345, title => 'my book', pages => 100);
   sref REF serial_t;
   asub subject_t := subject_t('The World', NULL);
BEGIN
   DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertNumber(3.141592654)));
   DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertChar(achar)));
   DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertObject(abook)));
   DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertObject(asub)));
   SELECT TREAT(REF(c) AS REF serial_t) INTO sref
     FROM catalog_items c WHERE title = 'Time';
   DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertRef(sref)));
END;
/


ALTER TYPE catalog_item_t
   ADD MEMBER PROCEDURE save,
   ADD MEMBER FUNCTION retrieve_matching
      RETURN SYS_REFCURSOR,
   ADD MEMBER PROCEDURE remove
   CASCADE;

CREATE OR REPLACE TYPE BODY catalog_item_t
AS
   MEMBER PROCEDURE save
   IS
   BEGIN
      UPDATE catalog_items c
         SET c = SELF
       WHERE id = SELF.id;
       IF SQL%ROWCOUNT = 0
       THEN
          INSERT INTO catalog_items VALUES (SELF);
       END IF;
   END;

   MEMBER FUNCTION retrieve_matching
      RETURN SYS_REFCURSOR
   IS
      l_refcur SYS_REFCURSOR;
   BEGIN
      IF SELF IS OF (book_t)
      THEN
         OPEN l_refcur FOR
            SELECT VALUE(c)
              FROM catalog_items c
             WHERE (SELF.id IS NULL OR id = SELF.id)
               AND (SELF.title IS NULL OR title LIKE SELF.title || '%')
               AND (SELF.publication_date IS NULL
                    OR publication_date = SELF.publication_date)
               AND (TREAT(SELF AS book_t).isbn IS NULL
                    OR TREAT(VALUE(c) AS book_t).isbn =
                          TREAT(SELF AS book_t).isbn)
               AND (TREAT(SELF AS book_t).pages IS NULL
                    OR TREAT(VALUE(c) AS book_t).pages =
                          TREAT(SELF AS book_t).pages);
      ELSIF SELF IS OF (serial_t)
      THEN
         OPEN l_refcur FOR
            SELECT VALUE(c)
              FROM catalog_items c
             WHERE (SELF.id IS NULL OR id = SELF.id)
               AND (SELF.title IS NULL OR title LIKE SELF.title || '%')
               AND (SELF.publication_date IS NULL
                    OR publication_date = SELF.publication_date)
               AND (TREAT(SELF AS serial_t).issn IS NULL
                    OR TREAT(VALUE(c) AS serial_t).issn =
                          TREAT(SELF AS serial_t).issn)
               AND (TREAT(SELF AS serial_t).open_or_closed IS NULL
                    OR TREAT(VALUE(c) AS serial_t).open_or_closed =
                          TREAT(SELF AS serial_t).open_or_closed);
      END IF;
      RETURN l_refcur;
   END;

   MEMBER PROCEDURE remove
   IS
   BEGIN
      DELETE catalog_items
       WHERE id = SELF.id;
      SELF := NULL;
   END;

   MEMBER FUNCTION print
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN 'stub for not-yet-implemented print method';
   END;
END;
/
SHOW ERR

DECLARE
   catalog_item catalog_item_t;
   l_refcur SYS_REFCURSOR;
   l_sample_object book_t := NEW book_t(title => '%');
   new_book book_t := NEW book_t(id => 12345, title => 'Test book', pages => 77);
   new_serial serial_t := NEW serial_t(id => 54321, open_or_closed => 'C');
BEGIN
   new_book.save;
   l_refcur := l_sample_object.retrieve_matching();
   LOOP
      FETCH l_refcur INTO catalog_item;
      EXIT WHEN l_refcur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Matching item:' || catalog_item.print);
   END LOOP;
   CLOSE l_refcur;
   new_book.remove;
END;
/



CREATE TABLE catalog_history
(id INTEGER NOT NULL PRIMARY KEY,
action CHAR(1) NOT NULL,
action_time TIMESTAMP DEFAULT (SYSTIMESTAMP) NOT NULL,
old_item catalog_item_t,
new_item catalog_item_t)
NESTED TABLE old_item.subject_refs STORE AS catalog_history_old_subrefs
NESTED TABLE new_item.subject_refs STORE AS catalog_history_new_subrefs
/


SELECT * FROM catalog_history c
 WHERE c.old_item.id > 10000
 ORDER BY NVL(TREAT(c.old_item as book_t).isbn, TREAT(c.old_item AS serial_t).issn)
/

CREATE INDEX catalog_history_old_id_idx ON catalog_history c (c.old_item.id)
/

ALTER TYPE catalog_item_t
   ADD MAP MEMBER FUNCTION mapit RETURN NUMBER
   CASCADE;

CREATE OR REPLACE TYPE BODY catalog_item_t
AS
   MEMBER PROCEDURE save
   IS
   BEGIN
      UPDATE catalog_items c
         SET c = SELF
       WHERE id = SELF.id;
       IF SQL%ROWCOUNT = 0
       THEN
          INSERT INTO catalog_items VALUES (SELF);
       END IF;
   END;

   MEMBER FUNCTION retrieve_matching
      RETURN SYS_REFCURSOR
   IS
      l_refcur SYS_REFCURSOR;
   BEGIN
      IF SELF IS OF (book_t)
      THEN
         OPEN l_refcur FOR
            SELECT VALUE(c)
              FROM catalog_items c
             WHERE (SELF.id IS NULL OR id = SELF.id)
               AND (SELF.title IS NULL OR title LIKE SELF.title || '%')
               AND (SELF.publication_date IS NULL
                    OR publication_date = SELF.publication_date)
               AND (TREAT(SELF AS book_t).isbn IS NULL
                    OR TREAT(VALUE(c) AS book_t).isbn =
                          TREAT(SELF AS book_t).isbn)
               AND (TREAT(SELF AS book_t).pages IS NULL
                    OR TREAT(VALUE(c) AS book_t).pages =
                          TREAT(SELF AS book_t).pages);
      ELSIF SELF IS OF (serial_t)
      THEN
         OPEN l_refcur FOR
            SELECT VALUE(c)
              FROM catalog_items c
             WHERE (SELF.id IS NULL OR id = SELF.id)
               AND (SELF.title IS NULL OR title LIKE SELF.title || '%')
               AND (SELF.publication_date IS NULL
                    OR publication_date = SELF.publication_date)
               AND (TREAT(SELF AS serial_t).issn IS NULL
                    OR TREAT(VALUE(c) AS serial_t).issn =
                          TREAT(SELF AS serial_t).issn)
               AND (TREAT(SELF AS serial_t).open_or_closed IS NULL
                    OR TREAT(VALUE(c) AS serial_t).open_or_closed =
                          TREAT(SELF AS serial_t).open_or_closed);
      END IF;
      RETURN l_refcur;
   END;

   MEMBER PROCEDURE remove
   IS
   BEGIN
      DELETE catalog_items
       WHERE id = SELF.id;
      SELF := NULL;
   END;

   MEMBER FUNCTION print
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN 'stub for not-yet-implemented print method';
   END;

   MAP MEMBER FUNCTION mapit RETURN NUMBER
   IS
   BEGIN
      RETURN id;
   END;
END;
/
SHOW ERR

ALTER TYPE catalog_item_t
   DROP MAP MEMBER FUNCTION mapit RETURN NUMBER
   CASCADE;

ALTER TYPE catalog_item_t
   ADD ORDER MEMBER FUNCTION orderit (obj2 IN catalog_item_t)
      RETURN INTEGER
   CASCADE;

CREATE OR REPLACE TYPE BODY catalog_item_t
AS
   MEMBER PROCEDURE save
   IS
   BEGIN
      UPDATE catalog_items c
         SET c = SELF
       WHERE id = SELF.id;
       IF SQL%ROWCOUNT = 0
       THEN
          INSERT INTO catalog_items VALUES (SELF);
       END IF;
   END;

   MEMBER FUNCTION retrieve_matching
      RETURN SYS_REFCURSOR
   IS
      l_refcur SYS_REFCURSOR;
   BEGIN
      IF SELF IS OF (book_t)
      THEN
         OPEN l_refcur FOR
            SELECT VALUE(c)
              FROM catalog_items c
             WHERE (SELF.id IS NULL OR id = SELF.id)
               AND (SELF.title IS NULL OR title LIKE SELF.title || '%')
               AND (SELF.publication_date IS NULL
                    OR publication_date = SELF.publication_date)
               AND (TREAT(SELF AS book_t).isbn IS NULL
                    OR TREAT(VALUE(c) AS book_t).isbn =
                          TREAT(SELF AS book_t).isbn)
               AND (TREAT(SELF AS book_t).pages IS NULL
                    OR TREAT(VALUE(c) AS book_t).pages =
                          TREAT(SELF AS book_t).pages);
      ELSIF SELF IS OF (serial_t)
      THEN
         OPEN l_refcur FOR
            SELECT VALUE(c)
              FROM catalog_items c
             WHERE (SELF.id IS NULL OR id = SELF.id)
               AND (SELF.title IS NULL OR title LIKE SELF.title || '%')
               AND (SELF.publication_date IS NULL
                    OR publication_date = SELF.publication_date)
               AND (TREAT(SELF AS serial_t).issn IS NULL
                    OR TREAT(VALUE(c) AS serial_t).issn =
                          TREAT(SELF AS serial_t).issn)
               AND (TREAT(SELF AS serial_t).open_or_closed IS NULL
                    OR TREAT(VALUE(c) AS serial_t).open_or_closed =
                          TREAT(SELF AS serial_t).open_or_closed);
      END IF;
      RETURN l_refcur;
   END;

   MEMBER PROCEDURE remove
   IS
   BEGIN
      DELETE catalog_items
       WHERE id = SELF.id;
      SELF := NULL;
   END;

   MEMBER FUNCTION print
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN NULL; /* never gets invoked; non-instantiable type */
   END;

   ORDER MEMBER FUNCTION orderit (obj2 IN catalog_item_t)
      RETURN INTEGER
   IS
      self_gt_o2 CONSTANT PLS_INTEGER := 1;
      eq CONSTANT PLS_INTEGER := 0;
      o2_gt_self CONSTANT PLS_INTEGER := -1;
      l_matching_count NUMBER;
   BEGIN
      CASE 
         WHEN obj2 IS OF (book_t) AND SELF IS OF (serial_t) THEN
            RETURN o2_gt_self;
         WHEN obj2 IS OF (serial_t) AND SELF IS OF (book_t) THEN
            RETURN self_gt_o2;
         ELSE
            IF obj2.title = SELF.title
               AND obj2.publication_date = SELF.publication_date
            THEN
               IF obj2.subject_refs IS NOT NULL
                  AND SELF.subject_refs IS NOT NULL
                  AND obj2.subject_refs.COUNT = SELF.subject_refs.COUNT
               THEN
                  SELECT COUNT(*) INTO l_matching_count FROM
                     (SELECT *
                       FROM TABLE(SELECT CAST(SELF.subject_refs AS subject_refs_t)
                                    FROM dual)
                     INTERSECT
                     SELECT *
                       FROM TABLE(SELECT CAST(obj2.subject_refs AS subject_refs_t)
                                    FROM dual));
                  IF l_matching_count = SELF.subject_refs.COUNT
                  THEN
                     RETURN eq;
                  END IF;
               END IF;
            END IF;
            RETURN NULL;
      END CASE;
   END;
END;
/
SHOW ERR

-- vim: noet ts=3 sw=3 syntax=plsql


/*======================================================================
| Supplement to the third edition of Oracle PL/SQL Programming by Steven
| Feuerstein with Bill Pribyl, Copyright (c) 1997-2002 O'Reilly &
| Associates, Inc. To submit corrections or find more code samples visit
| http://www.oreilly.com/catalog/oraclep3/
*/