collection_schema.sql

oracle 11 源代码

-- This script does the following:
--   1. Creates collection_user
--   2. Creates the collection types and database tables
--   3. Populates the database tables with sample data
--   4. Creates the PL/SQL code

-- attempt to drop the user (this will generate an error
-- if the user does not yet exist; do not worry about this
-- error); this statement is included so that you do not have
-- to manually run the DROP before recreating the schema
DROP USER collection_user CASCADE;

-- create collection_user
CREATE USER collection_user IDENTIFIED BY collection_password;

-- allow collection_user to connect and create database objects
GRANT connect, resource TO collection_user;

-- connect as collection_user
CONNECT collection_user/collection_password;

-- create the types, tables, and insert sample data
CREATE TYPE t_varray_address AS VARRAY(3) OF VARCHAR2(50);
/

CREATE TABLE customers_with_varray (
  id         INTEGER PRIMARY KEY,
  first_name VARCHAR2(10),
  last_name  VARCHAR2(10),
  addresses  t_varray_address
);

INSERT INTO customers_with_varray VALUES (
  1, 'Steve', 'Brown',
  t_varray_address(
    '2 State Street, Beantown, MA, 12345',
    '4 Hill Street, Lost Town, CA, 54321'
  )
);

INSERT INTO customers_with_varray VALUES (
  2, 'John', 'Smith',
  t_varray_address(
    '1 High Street, Newtown, CA, 12347',
    '3 New Street, Anytown, MI, 54323',
    '7 Market Street, Main Town, MA, 54323'
  )
);

CREATE TYPE t_address AS OBJECT (
  street VARCHAR2(15),
  city   VARCHAR2(15),
  state  CHAR(2),
  zip    VARCHAR2(5)
);
/

CREATE TYPE t_nested_table_address AS TABLE OF t_address;
/

CREATE TABLE customers_with_nested_table (
  id         INTEGER PRIMARY KEY,
  first_name VARCHAR2(10),
  last_name  VARCHAR2(10),
  addresses  t_nested_table_address
)
NESTED TABLE
  addresses
STORE AS
  nested_addresses;

INSERT INTO customers_with_nested_table VALUES (
  1, 'Steve', 'Brown',
  t_nested_table_address(
    t_address('2 State Street', 'Beantown', 'MA', '12345'),
    t_address('4 Hill Street', 'Lost Town', 'CA', '54321')
  )
);

INSERT INTO customers_with_nested_table VALUES (
  2, 'John', 'Smith',
  t_nested_table_address(
    t_address('1 High Street', 'Newtown', 'CA', '12347'),
    t_address('3 New Street', 'Anytown', 'MI', '54323'),
    t_address('7 Market Street', 'Main Town', 'MA', '54323')
  )
);

CREATE TYPE t_address2 AS OBJECT (
  street VARCHAR2(15),
  city   VARCHAR2(15),
  state  CHAR(2),
  zip    VARCHAR2(5),

  -- declare the get_string() map function,
  -- which returns a VARCHAR2 string
  MAP MEMBER FUNCTION get_string RETURN VARCHAR2
);
/

CREATE TYPE BODY t_address2 AS
  -- define the get_string() map function
  MAP MEMBER FUNCTION get_string RETURN VARCHAR2 IS
  BEGIN
    -- return a concatenated string containing the
    -- zip, state, city, and street attributes 
    RETURN zip || ' ' || state || ' ' || city || ' ' || street;
  END get_string;
END;
/

CREATE TYPE t_nested_table_address2 AS TABLE OF t_address2;
/

CREATE TABLE customers_with_nested_table2 (
  id         INTEGER PRIMARY KEY,
  first_name VARCHAR2(10),
  last_name  VARCHAR2(10),
  addresses  t_nested_table_address2
)
NESTED TABLE
  addresses
STORE AS
  nested_addresses2;

INSERT INTO customers_with_nested_table2 VALUES (
  1, 'Steve', 'Brown',
  t_nested_table_address2(
    t_address2('2 State Street', 'Beantown', 'MA', '12345'),
    t_address2('4 Hill Street', 'Lost Town', 'CA', '54321')
  )
);

CREATE TYPE t_varray_address2 AS VARRAY(3) OF t_address;
/

CREATE TABLE customers_with_varray2 (
  id         INTEGER PRIMARY KEY,
  first_name VARCHAR2(10),
  last_name  VARCHAR2(10),
  addresses  t_varray_address2
);

INSERT INTO customers_with_varray2 VALUES (
  1, 'Jason', 'Bond',
  t_varray_address2(
    t_address('9 Newton Drive', 'Sometown', 'WY', '22123'),
    t_address('6 Spring Street', 'New City', 'CA', '77712')
  )
);


-- create the PL/SQL code
CREATE PACKAGE varray_package AS
  TYPE t_ref_cursor IS REF CURSOR;
  FUNCTION get_customers RETURN t_ref_cursor;
  PROCEDURE insert_customer(
    p_id         IN customers_with_varray.id%TYPE,
    p_first_name IN customers_with_varray.first_name%TYPE,
    p_last_name  IN customers_with_varray.last_name%TYPE,
    p_addresses  IN customers_with_varray.addresses%TYPE
  );
END varray_package;
/

CREATE PACKAGE BODY varray_package AS
  -- get_customers() function returns a REF CURSOR
  -- that points to the rows in customers_with_varray
  FUNCTION get_customers
  RETURN t_ref_cursor IS
    --declare the REF CURSOR object
    v_customers_ref_cursor t_ref_cursor;
  BEGIN
    -- get the REF CURSOR
    OPEN v_customers_ref_cursor FOR
      SELECT *
      FROM customers_with_varray;
    -- return the REF CURSOR
    RETURN v_customers_ref_cursor;
  END get_customers;

  -- insert_customer() procedure adds a row to
  -- customers_with_varray
  PROCEDURE insert_customer(
    p_id         IN customers_with_varray.id%TYPE,
    p_first_name IN customers_with_varray.first_name%TYPE,
    p_last_name  IN customers_with_varray.last_name%TYPE,
    p_addresses  IN customers_with_varray.addresses%TYPE
  ) IS
  BEGIN
    INSERT INTO customers_with_varray
    VALUES (p_id, p_first_name, p_last_name, p_addresses);
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
  END insert_customer;
END varray_package;
/

CREATE PACKAGE nested_table_package AS
  TYPE t_ref_cursor IS REF CURSOR;
  FUNCTION get_customers RETURN t_ref_cursor;
  PROCEDURE insert_customer(
    p_id         IN customers_with_nested_table.id%TYPE,
    p_first_name IN customers_with_nested_table.first_name%TYPE,
    p_last_name  IN customers_with_nested_table.last_name%TYPE,
    p_addresses  IN customers_with_nested_table.addresses%TYPE
  );
END nested_table_package;
/

CREATE PACKAGE BODY nested_table_package AS
  -- get_customers() function returns a REF CURSOR
  -- that points to the rows in customers_with_nested_table
  FUNCTION get_customers
  RETURN t_ref_cursor IS
    -- declare the REF CURSOR object
    v_customers_ref_cursor t_ref_cursor;
  BEGIN
    -- get the REF CURSOR
    OPEN v_customers_ref_cursor FOR
      SELECT *
      FROM customers_with_nested_table;
    -- return the REF CURSOR
    RETURN v_customers_ref_cursor;
  END get_customers;

  -- insert_customer() procedure adds a row to
  -- customers_with_nested_table
  PROCEDURE insert_customer(
    p_id         IN customers_with_nested_table.id%TYPE,
    p_first_name IN customers_with_nested_table.first_name%TYPE,
    p_last_name  IN customers_with_nested_table.last_name%TYPE,
    p_addresses  IN customers_with_nested_table.addresses%TYPE
  ) IS
  BEGIN
    INSERT INTO customers_with_nested_table
    VALUES (p_id, p_first_name, p_last_name, p_addresses);
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
  END insert_customer;
END nested_table_package;