Mega Code Archive
Object Relational tables
<![CDATA[
SQL>
SQL> CREATE OR REPLACE TYPE address AS OBJECT
2 (line1 VARCHAR2(20),
3 line2 VARCHAR2(20),
4 city VARCHAR2(20),
5 state_code VARCHAR2(2),
6 zip VARCHAR2(13),
7 MEMBER FUNCTION get_address RETURN VARCHAR2,
8 MEMBER PROCEDURE set_address
9 (addressLine1 VARCHAR2,
10 addressLine2 VARCHAR2,
11 address_city VARCHAR2,
12 address_state VARCHAR2,
13 address_zip VARCHAR2)
14 );
15 /
Type created.
SQL> CREATE OR REPLACE TYPE BODY address AS
2 MEMBER FUNCTION get_address RETURN VARCHAR2
3 IS
4 BEGIN
5 RETURN (SELF.line1||' '||SELF.line2||' '||SELF.city||', '||
6 SELF.state_code||' '||SELF.zip);
7 END get_address;
8 MEMBER PROCEDURE set_address (addressLine1 VARCHAR2,
9 addressLine2 VARCHAR2,
10 address_city VARCHAR2,
11 address_state VARCHAR2,
12 address_zip VARCHAR2)
13 IS
14 BEGIN
15 line1 :=addressLine1;
16
17 line2 :=addressLine2;
18
19 city :=address_city;
20
21 state_code :=address_state;
22
23 zip :=address_zip;
24
25 END set_address;
26 END;
27 /
Type body created.
SQL>
SQL> CREATE TABLE employee
2 (empid number(10)PRIMARY KEY,
3 lastname varchar2(30)NOT NULL,
4 firstname varchar2(30)NOT NULL,
5 middle_initial varchar2(2),
6 emp_address address);
Table created.
SQL>
SQL> INSERT INTO employee VALUES (101,'L','BULUSU',null,
2 address('50 UNION SQUARE','SUITE 101','NEW YORK','NY','10020'));
1 row created.
SQL>
SQL> select * from employee;
EMPID LASTNAME FIRSTNAME
------ ------------------------------ ------------------------------
MI
--
EMP_ADDRESS(LINE1, LINE2, CITY, STATE_CODE, ZIP)
----------------------------------------------------------------------
101 L BULUSU
nu
ll
ADDRESS('50 UNION SQUARE', 'SUITE 101', 'NEW YORK', 'NY', '10020')
1 row selected.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
]]>