⭐ 欢迎来到虫虫下载站！ | 📦 资源下载 📁 资源专辑 ℹ️ 关于我们
⭐ 虫虫下载站
📤 上传资源

📄 ch20.sql

📁 OReilly Oracle PL SQL Programming第4版源代码
💻 SQL
📖 第 1 页 / 共 3 页
字号:
🔍
123 
   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
         || '; publication_date=' || publication_date
         || '; isbn=' || isbn || '; pages=' || pages;
   END;
END;
/

ALTER TYPE serial_t
   DROP CONSTRUCTOR FUNCTION serial_t (id INTEGER DEFAULT NULL,
      title VARCHAR2 DEFAULT NULL,
      issn VARCHAR2 DEFAULT NULL,
      open_or_closed VARCHAR2 DEFAULT NULL)
      RETURN SELF AS RESULT
   CASCADE;

ALTER TYPE serial_t
   ADD CONSTRUCTOR FUNCTION serial_t (id INTEGER DEFAULT NULL,
      title VARCHAR2 DEFAULT NULL,
      publication_date VARCHAR2 DEFAULT NULL,
      issn VARCHAR2 DEFAULT NULL,
      open_or_closed VARCHAR2 DEFAULT NULL)
      RETURN SELF AS RESULT
   CASCADE;

CREATE OR REPLACE TYPE BODY serial_t
AS
   CONSTRUCTOR FUNCTION serial_t (id INTEGER,
      title IN VARCHAR2,
      publication_date IN VARCHAR2,
      issn IN VARCHAR2,
      open_or_closed IN VARCHAR2)
      RETURN SELF AS RESULT
   IS
   BEGIN
      SELF.id := id;
      SELF.title := title;
      SELF.publication_date := publication_date;
      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 
         || '; publication_date=' || publication_date
         || '; issn=' || issn
         || '; open_or_closed='
         || CASE open_or_closed
               WHEN 'O' THEN 'Open'
               WHEN 'C' THEN 'Closed'
               ELSE NULL
            END;
   END;

END;
/
SHOW ERR
/*---------END EXTRA CODE NOT IN BOOK-------------------------*/

CREATE TYPE subject_t AS OBJECT (
   name VARCHAR2(2000),
   broader_term_ref REF subject_t
);
/
SHOW ERR

CREATE TYPE subject_refs_t AS TABLE OF REF subject_t;
/
SHOW ERR

CREATE TABLE subjects OF subject_t
   (CONSTRAINT subject_pk PRIMARY KEY (name),
   CONSTRAINT subject_self_ref FOREIGN KEY (broader_term_ref)
      REFERENCES subjects);


ALTER TYPE catalog_item_t
   ADD ATTRIBUTE subject_refs subject_refs_t
   CASCADE INCLUDING TABLE DATA;

/*---------BEGIN EXTRA CODE NOT IN BOOK------------------------*/
ALTER TYPE book_t
   DROP 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;

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

CREATE OR REPLACE TYPE BODY book_t
AS
   CONSTRUCTOR FUNCTION book_t (id INTEGER,
      title IN VARCHAR2,
      publication_date IN VARCHAR2,
      subject_refs subject_refs_t,
      isbn IN VARCHAR2,
      pages IN INTEGER)
      RETURN SELF AS RESULT
   IS
   BEGIN
      SELF.id := id;
      SELF.title := title;
      SELF.publication_date := publication_date;
      SELF.subject_refs := subject_refs;
      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
         || '; publication_date=' || publication_date
         || '; isbn=' || isbn || '; pages=' || pages;
   END;
END;
/

ALTER TYPE serial_t
   DROP CONSTRUCTOR FUNCTION serial_t (id INTEGER DEFAULT NULL,
      title VARCHAR2 DEFAULT NULL,
      publication_date VARCHAR2 DEFAULT NULL,
      issn VARCHAR2 DEFAULT NULL,
      open_or_closed VARCHAR2 DEFAULT NULL)
      RETURN SELF AS RESULT
   CASCADE;

ALTER TYPE serial_t
   ADD CONSTRUCTOR FUNCTION serial_t (id INTEGER DEFAULT NULL,
      title VARCHAR2 DEFAULT NULL,
      publication_date VARCHAR2 DEFAULT NULL,
      subject_refs IN subject_refs_t DEFAULT NULL,
      issn VARCHAR2 DEFAULT NULL,
      open_or_closed VARCHAR2 DEFAULT NULL)
      RETURN SELF AS RESULT
   CASCADE;

CREATE OR REPLACE TYPE BODY serial_t
AS
   CONSTRUCTOR FUNCTION serial_t (id INTEGER,
      title IN VARCHAR2,
      publication_date IN VARCHAR2,
      subject_refs IN subject_refs_t,
      issn IN VARCHAR2,
      open_or_closed IN VARCHAR2)
      RETURN SELF AS RESULT
   IS
   BEGIN
      SELF.id := id;
      SELF.title := title;
      SELF.publication_date := publication_date;
      SELF.subject_refs := subject_refs;
      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 
         || '; publication_date=' || publication_date
         || '; issn=' || issn
         || '; open_or_closed='
         || CASE open_or_closed
               WHEN 'O' THEN 'Open'
               WHEN 'C' THEN 'Closed'
               ELSE NULL
            END;
   END;

END;
/
SHOW ERR
/*---------END EXTRA CODE NOT IN BOOK-------------------------*/

INSERT INTO subjects VALUES (subject_t('Computer file', NULL));

INSERT INTO subjects VALUES (subject_t('Computer program language', NULL));

INSERT INTO subjects VALUES (subject_t('Oracle',
   (SELECT REF(s) FROM subjects s WHERE name = 'Computer file')));

INSERT INTO subjects VALUES (subject_t('PL/SQL',
   (SELECT REF(s) FROM subjects s WHERE name = 'Computer program language')));

INSERT INTO subjects VALUES (subject_t('Relational databases', NULL));

COMMIT;

COLUMN name FORMAT a25
COLUMN bt FORMAT a25

SELECT VALUE(s) FROM subjects s;

SELECT s.name, DEREF(s.broader_term_ref).name bt
  FROM subjects s;

SELECT s.name, s.broader_term_ref.name FROM subjects s;

INSERT INTO catalog_items
VALUES (NEW book_t(10007,
   'Oracle PL/SQL Programming',
   'Sept 1997',
   (SELECT CAST(MULTISET(SELECT REF(s)
                           FROM subjects s
                          WHERE name IN ('Oracle', 'PL/SQL',
                                         'Relational databases'))
             AS subject_refs_t)
      FROM DUAL),
   '1-56592-335-9',
   987));

ROLLBACK;

DECLARE
   subrefs subject_refs_t;
BEGIN
   SELECT REF(s)
     BULK COLLECT INTO subrefs
     FROM subjects s
    WHERE name IN ('Oracle', 'PL/SQL', 'Relational databases');

   INSERT INTO catalog_items VALUES (NEW book_t(10007,
      'Oracle PL/SQL Programming', 'Sept 1997', subrefs, '1-56592-335-9', 987));
END;
/

DECLARE
   subject subject_t;
   subrefs subject_refs_t;
   broader_term subject_t;
BEGIN
   SELECT TREAT(VALUE(c) AS book_t).subject_refs
     INTO subrefs
     FROM catalog_items c
    WHERE id = 10007;
   FOR i IN 1..subrefs.COUNT
   LOOP
      UTL_REF.SELECT_OBJECT(subrefs(i), subject);
      DBMS_OUTPUT.PUT(subject.name);
      IF subject.broader_term_ref IS NOT NULL
      THEN
         UTL_REF.SELECT_OBJECT(subject.broader_term_ref, broader_term);
         DBMS_OUTPUT.PUT_LINE(' (' || broader_term.name || ')');
      ELSE
         DBMS_OUTPUT.PUT_LINE('');
      END IF;
   END LOOP;
END;
/

SELECT VALUE(s).name
  || ' (' || VALUE(s).broader_term_ref.name || ')' plsql_subjects
  FROM TABLE(SELECT subject_refs
              FROM catalog_items
             WHERE id=10007) s
/


CREATE OR REPLACE TYPE subject_names_t AS TABLE OF VARCHAR2(400);
/

SELECT ci.title, CAST(MULTISET(SELECT s.column_value.name
                                 FROM TABLE(SELECT c.subject_refs
                                              FROM catalog_items c
                                             WHERE id = ci.id) s
                               )
                   AS subject_names_t) subjects
  FROM catalog_items ci
 WHERE ci.id = 10007
/


ALTER TYPE subject_t
   ADD MEMBER FUNCTION print_bt (str IN VARCHAR2 DEFAULT NULL)
      RETURN VARCHAR2
   CASCADE INCLUDING TABLE DATA;

CREATE OR REPLACE TYPE BODY subject_t
AS
   MEMBER FUNCTION print_bt (str IN VARCHAR2)
      RETURN VARCHAR2
   IS
      bt subject_t;
   BEGIN
      IF SELF.broader_term_ref IS NULL
123

⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -