forked from ashishjohn1908/Java
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathobject_user_9i.sql
More file actions
179 lines (138 loc) · 3.92 KB
/
Copy pathobject_user_9i.sql
File metadata and controls
179 lines (138 loc) · 3.92 KB
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
-- The SQL*Plus script object_user_9i.sql performs the following:
-- 1. Creates object_user_9i
-- 2. Creates the database object types
-- 3. Populates the database tables with example data
-- This script should be run by the system user (or the DBA)
CONNECT system/London92;
-- drop object_user_9i
DROP USER object_user_9i CASCADE;
-- create object_user_9i
CREATE USER object_user_9i IDENTIFIED BY object_password;
-- allow object_user_9i to connect and create database objects
GRANT connect, resource TO object_user_9i;
GRANT UNLIMITED TABLESPACE TO object_user_9i;
-- connect as object_user_9i
CONNECT object_user_9i/object_password;
-- create the object types
CREATE TYPE address_typ AS OBJECT (
street VARCHAR2(15),
city VARCHAR2(15),
state CHAR(2),
zip VARCHAR2(5)
);
/
CREATE TYPE person_typ AS OBJECT (
id NUMBER,
first_name VARCHAR2(10),
last_name VARCHAR2(10),
dob DATE,
phone VARCHAR2(12),
address address_typ
) NOT FINAL;
/
CREATE TYPE business_person_typ UNDER person_typ (
title VARCHAR2(20),
company VARCHAR2(20)
);
/
CREATE TYPE product_typ AS OBJECT (
id NUMBER,
name VARCHAR2(10),
description VARCHAR2(22),
price NUMBER(5, 2),
days_valid NUMBER,
-- declare the get_sell_by_date() member function,
-- get_sell_by_date() returns the date by which the
-- product must be sold
MEMBER FUNCTION get_sell_by_date RETURN DATE
);
/
CREATE TYPE BODY product_typ AS
-- define the get_sell_by_date() member function,
-- get_sell_by_date() returns the date by which the
-- product must be sold
MEMBER FUNCTION get_sell_by_date RETURN DATE IS
sell_by_date DATE;
BEGIN
-- calculate the sell by date by adding the days_valid attribute
-- to the current date (sysdate)
SELECT
days_valid + sysdate
INTO
sell_by_date
FROM
dual;
-- return the sell by date
RETURN sell_by_date;
END;
END;
/
-- create the tables
CREATE TABLE products (
product product_typ,
quantity_in_stock NUMBER
);
CREATE TABLE object_products OF product_typ;
CREATE TABLE object_customers OF person_typ;
CREATE TABLE object_business_customers OF business_person_typ;
CREATE TABLE purchases (
id NUMBER PRIMARY KEY,
customer REF person_typ SCOPE IS object_customers,
product REF product_typ SCOPE IS object_products
);
-- insert sample data into products table
INSERT INTO products (
product,
quantity_in_stock
) VALUES (
product_typ(1, 'Pasta', '20 oz bag of pasta', 3.95, 10),
50
);
INSERT INTO products (
product,
quantity_in_stock
) VALUES (
product_typ(2, 'Sardines', '12 oz box of sardines', 2.99, 5),
25
);
-- insert sample data into object_products table
INSERT INTO object_products VALUES (
product_typ(1, 'Pasta', '20 oz bag of pasta', 3.95, 10)
);
INSERT INTO object_products (
id, name, description, price, days_valid
) VALUES (
2, 'Sardines', '12 oz box of sardines', 2.99, 5
);
-- insert sample data into object_customers table
INSERT INTO object_customers VALUES (
person_typ(1, 'John', 'Brown', '01-FEB-1955', '800-555-1211',
address_typ('2 State Street', 'Beantown', 'MA', '12345')
)
);
INSERT INTO object_customers (
id, first_name, last_name, dob, phone,
address
) VALUES (
2, 'Cynthia', 'Green', '05-FEB-1968', '800-555-1212',
address_typ('3 Free Street', 'Middle Town', 'CA', '12345')
);
-- insert sample data into object_business_customers table
INSERT INTO object_business_customers VALUES (
business_person_typ(1, 'John', 'Brown', '01-FEB-1955', '800-555-1211',
address_typ('2 State Street', 'Beantown', 'MA', '12345'),
'Manager', 'XYZ Corp'
)
);
-- insert sample data into purchases table
INSERT INTO purchases (
id,
customer,
product
) VALUES (
1,
(SELECT REF(oc) FROM object_customers oc WHERE oc.id = 1),
(SELECT REF(op) FROM object_products op WHERE op.id = 1)
);
-- commit the transaction
COMMIT;