object_user_schema.sql 5.64 KB
Newer Older
O'Reilly Media, Inc. committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244
-- The SQL*Plus script object_user_schema.sql performs the following:
--   1. Creates the object_user.
--   2. Creates the database objects.
--   3. Populates the database tables with example data.

-- This script should be run by the system user (or the DBA).
CONNECT system/manager;

-- drop object_user
DROP USER object_user CASCADE;

-- create object_user
CREATE USER object_user IDENTIFIED BY object_password;

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

-- connect as the object_user
CONNECT object_user/object_password;

-- create the user defined types
CREATE TYPE t_address AS OBJECT (
  street VARCHAR2(15),
  city   VARCHAR2(15),
  state  CHAR(2),
  zip    VARCHAR2(9)
);
/

CREATE OR REPLACE TYPE t_customer AS OBJECT (
  id         NUMBER,
  first_name VARCHAR2(10),
  last_name  VARCHAR2(10),
  dob        DATE,
  phone      VARCHAR2(15),
  address    t_address,

  -- the function get_age() returns the age of the customer in years
  MEMBER FUNCTION get_age RETURN INTEGER
);
/

CREATE OR REPLACE TYPE BODY t_customer AS

  -- the function get_age() returns the age of the customer in years
  MEMBER FUNCTION get_age RETURN INTEGER IS

    age INTEGER;

  BEGIN

    -- calculate the age in years
    SELECT ROUND(((sysdate - dob) / 365), 0) INTO age FROM dual;

    RETURN age;

  END;

END;
/

CREATE TYPE t_address2 AS VARRAY(3) OF VARCHAR2(50);
/

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

CREATE TYPE t_address4 AS TABLE OF VARCHAR2(50);
/

CREATE TYPE t_product AS OBJECT (
  id          NUMBER,
  name        VARCHAR2(50),
  description VARCHAR2(500),
  price       NUMBER(10, 2)
);
/


-- create the tables
CREATE TABLE customers (
  customer t_customer
);

CREATE TABLE customers2 OF t_customer;

CREATE TABLE customers3 (
  id         NUMBER CONSTRAINT customers3_pk PRIMARY KEY,
  first_name VARCHAR2(10),
  last_name  VARCHAR2(10),
  addresses  t_address2
);

CREATE TABLE customers4 (
  id         NUMBER CONSTRAINT customers4_pk PRIMARY KEY,
  first_name VARCHAR2(10),
  last_name  VARCHAR2(10),
  addresses  t_address3
);

CREATE TABLE customers5 (
  id         NUMBER CONSTRAINT customers5_pk PRIMARY KEY,
  first_name VARCHAR2(10),
  last_name  VARCHAR2(10),
  addresses  t_address4
)
NESTED TABLE
  addresses
STORE AS
  nested_addresses;

CREATE TABLE products OF t_product;

CREATE TABLE purchases (
  id           NUMBER CONSTRAINT purchases_pk PRIMARY KEY,
  purchased_by REF t_customer SCOPE IS customers2,
  product      REF t_product  SCOPE IS products,
  quantity     NUMBER
);

-- insert sample data into customers table

INSERT INTO customers VALUES (
  t_customer(1, 'John', 'Smith', '01-JAN-1965', '650-555-1212',
    t_address('1 Anystreet', 'Anytown', 'CA', '12345')
  )
);

INSERT INTO customers VALUES (
  t_customer(2, 'Fred', 'Gilroy', '04-JUL-1958', '650-555-1212',
    t_address('2 Main Street', 'Middle Town', 'CA', '12345')
  )
);

-- insert sample data into customers2 table

INSERT INTO customers2 VALUES (
  t_customer(1, 'John', 'Smith', '01-JAN-1965', '650-555-1212',
    t_address('1 Anystreet', 'Anytown', 'CA', '12345')
  )
);

INSERT INTO customers2 VALUES (
  t_customer(2, 'Fred', 'Gilroy', '04-JUL-1958', '650-555-1212',
    t_address('2 Main Street', 'Middle Town', 'CA', '12345')
  )
);

-- insert sample data into customers3 table

INSERT INTO customers3 VALUES (
  1, 'John', 'Smith',
  t_address2(
    '1 Anystreet, Anytown, CA, 12345',
    '2 Main Street, Big Town, NY, 54321'
  )
);

INSERT INTO customers3 VALUES (
  2, 'Fred', 'Gilroy',
  t_address2(
    '3 Maple Street, Small Town, CA, 12345',
    '9 Winpole Avenue, Middle Town, NY, 54321'
  )
);

-- insert sample data into customers4 table

INSERT INTO customers4 VALUES (
  1, 'John', 'Smith',
  t_address3(
    t_address('1 Anystreet', 'Anytown', 'CA', '12345'),
    t_address('2 Main Street', 'Big Town', 'NY', '54321')
  )
);

INSERT INTO customers4 VALUES (
  2, 'Fred', 'Gilroy',
  t_address3(
    t_address('3 Maple Street', 'Small Town', 'CA', '12345'),
    t_address('9 Winpole Ave', 'Middle Town', 'NY', '54321')
  )
);

-- insert sample data into customers5 table

INSERT INTO customers5 VALUES (
  1, 'John', 'Smith',
  t_address4(
    '1 Anystreet, Anytown, CA, 12345',
    '2 Main Street, Big Town, NY, 54321'
  )
);

INSERT INTO customers5 VALUES (
  2, 'Fred', 'Gilroy',
  t_address4(
    '3 Maple Street, Small Town, CA, 12345',
    '9 Winpole Avenue, Middle Town, NY, 54321'
  )
);

-- insert sample data into products table

INSERT INTO products (id, name, description, price)
VALUES (1, 'Beyond Understanding', 'The frontiers of human knowledge', 19.95);

INSERT INTO products (id, name, description, price)
VALUES (2, 'Physics', 'Fundamental understanding of the natural world', 30.00);

INSERT INTO products (id, name, description, price)
VALUES (3, 'Star Travellers', 'The adventures of futuristic astronauts', 25.99);

INSERT INTO products (id, name, description, price)
VALUES (4, 'Seventh Sense', 'Supernatural tale of the unknown', 13.95);

-- insert sample data into purchases table

INSERT INTO purchases (
  id,
  purchased_by,
  product,
  quantity
) VALUES (
  1,
  (SELECT REF(c) FROM customers2 c WHERE c.id = 1),
  (SELECT REF(p) FROM products p WHERE p.id = 1),
  1
);

INSERT INTO purchases (
  id,
  purchased_by,
  product,
  quantity
) VALUES (
  2,
  (SELECT REF(c) FROM customers2 c WHERE c.id = 1),
  (SELECT REF(p) FROM products p WHERE p.id = 2),
  2
);

-- commit the transaction
COMMIT;