create_addressbook3.sql

来自「介绍Oracle PL SQL编程」· SQL 代码 · 共 89 行

/*
 * create_addressbook3.sql
 * Chapter 6, Oracle10g PL/SQL Programming
 * by Ron Hardman, Michael McLaughlin and Scott Urman
 *
 * This script builds objects for the example with standard Oracle data types
 * in all but one column. It uses a nested table of VARCHAR2(30 CHAR) for the
 * ADDRESSES.STREET_ADDRESS column.
 */

BEGIN
  FOR i IN (SELECT null
            FROM user_tables
            WHERE table_name = 'INDIVIDUALS') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE individuals CASCADE CONSTRAINTS';
  END LOOP;
END;
/

CREATE TABLE individuals
(individual_id            INTEGER             NOT NULL
,first_name               VARCHAR2(30 CHAR)   NOT NULL
,middle_initial           VARCHAR2(1 CHAR)
,last_name                VARCHAR2(30 CHAR)   NOT NULL
,CONSTRAINT individual_pk PRIMARY KEY (individual_id));

BEGIN
  FOR i IN (SELECT null
            FROM user_tables
            WHERE table_name = 'ADDRESSES') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE addresses CASCADE CONSTRAINTS';
  END LOOP;
END;
/

BEGIN

  -- Loop if a row is found and delete it.
  FOR i IN (SELECT   null
            FROM     user_types
            WHERE    type_name = 'ADDRESS_VARRAY') LOOP

    EXECUTE IMMEDIATE 'DROP TYPE address_varray';
    COMMIT;

  END LOOP;

END;
/

CREATE OR REPLACE TYPE address_table
  AS TABLE OF VARCHAR2(30 CHAR) NOT NULL;
/

CREATE TABLE addresses
(address_id               INTEGER             NOT NULL
,individual_id            INTEGER             NOT NULL
,street_address           ADDRESS_TABLE
,city                     VARCHAR2(20 CHAR)   NOT NULL
,state                    VARCHAR2(20 CHAR)   NOT NULL
,postal_code              VARCHAR2(20 CHAR)   NOT NULL
,country_code             VARCHAR2(10 CHAR)   NOT NULL
,CONSTRAINT address_pk    PRIMARY KEY (address_id)
,CONSTRAINT addr_indiv_fk FOREIGN KEY (individual_id)
 REFERENCES individuals  (individual_id))
NESTED TABLE street_address
STORE AS nested_street_address;

BEGIN
  FOR i IN (SELECT null
            FROM user_tables
            WHERE table_name = 'TELEPHONES') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE telephones';
  END LOOP;
END;
/

CREATE TABLE telephones
(telephone_id             INTEGER             NOT NULL
,individual_id            INTEGER             NOT NULL
,address_id               INTEGER
,telephone_type           VARCHAR2(4 CHAR)    NOT NULL
,telephone_number         VARCHAR2(20 CHAR)   NOT NULL
,CONSTRAINT telephone_pk  PRIMARY KEY (telephone_id)
,CONSTRAINT tele_indiv_fk FOREIGN KEY (individual_id)
 REFERENCES individuals  (individual_id)
,CONSTRAINT tele_addr_fk  FOREIGN KEY (address_id)
 REFERENCES addresses    (address_id));