Oracle PL/SQL/Object Oriented Database/Type Combination
Create an object table and insert value by using the object constructor
<source lang="sql">
SQL> SQL> create or replace type address_type
2 as object 3 ( city varchar2(30), 4 street varchar2(30), 5 state varchar2(2), 6 zip number 7 ) 8 /
Type created. SQL> create or replace type person_type
2 as object 3 ( name varchar2(30), 4 dob date, 5 home_address address_type, 6 work_address address_type 7 ) 8 /
Type created. SQL> SQL> SQL> create table people1 of person_type
2 /
Table created. SQL> SQL> SQL> insert into people1 values ( "Tom", "15-mar-1965",
2 address_type( "R", "123 Main Street", "Va", "45678" ), 3 address_type( "R", "1 Oracle Way", "Ca", "23456" ) );
1 row created. SQL> SQL> select * from people1; NAME DOB
--------------------
HOME_ADDRESS(CITY, STREET, STATE, ZIP)
WORK_ADDRESS(CITY, STREET, STATE, ZIP)
Tom 15-MAR-1965 00:00:00 ADDRESS_TYPE("R", "123 Main Street", "Va", 45678) ADDRESS_TYPE("R", "1 Oracle Way", "Ca", 23456)
1 row selected. SQL> SQL> drop table people1; Table dropped. SQL> drop type person_type; Type dropped. SQL> drop type address_type; Type dropped. SQL> --
</source>
Create instance of nested user-defined type
<source lang="sql">
SQL> SQL> create or replace
2 type person as object( 3 first_name varchar2(100), 4 last_name varchar2(100) ) 5 /
Type created. SQL> SQL> SQL> SQL> alter type person
2 add attribute dob date 3 cascade not including table data 4 /
Type altered. SQL> SQL> create or replace
2 type employee as object( 3 name person, 4 empno number, 5 hiredate date, 6 sal number, 7 commission number, 8 order member function match ( p_employee employee ) return integer ) 9 /
Type created. SQL> SQL> create or replace
2 type body employee as 3 order member function match ( p_employee employee ) return integer is 4 begin 5 if self.empno > p_employee.empno then 6 return 1; 7 elsif self.empno < p_employee.empno then 8 return -1; 9 else 10 return 0; 11 end if; 12 end; 13 end; 14 /
Type body created. SQL> SQL> declare
2 l_emp employee; 3 begin 4 l_emp := employee( person( "Tom", "K", sysdate ), 12345, "01-JAN-01",1,1); 5 dbms_output.put_line( "Empno: " || l_emp.empno ); 6 dbms_output.put_line( "First Name: " || l_emp.name.first_name ); 7 end; 8 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> drop type employee; Type dropped. SQL> SQL> drop type person; Type dropped. SQL> SQL>
</source>
Use user-defined object as attribute for new object type
<source lang="sql">
SQL> set echo on SQL> SQL> SQL> CREATE OR REPLACE TYPE address AS OBJECT (
2 street_1 VARCHAR2(40), 3 street_2 VARCHAR2(40), 4 city VARCHAR2(40), 5 state_abbr VARCHAR2(2), 6 zip_code VARCHAR2(5), 7 phone_number VARCHAR2(10), 8 MEMBER PROCEDURE ChangeAddress ( 9 st_1 IN VARCHAR2, st_2 IN VARCHAR2, cty IN VARCHAR2, 10 state IN VARCHAR2, zip IN VARCHAR2), 11 MEMBER FUNCTION getStreet (line_no IN number) RETURN VARCHAR2, 12 MEMBER FUNCTION getCity RETURN VARCHAR2, 13 MEMBER FUNCTION getStateAbbr RETURN VARCHAR2, 14 MEMBER FUNCTION getPostalCode RETURN VARCHAR2, 15 MEMBER FUNCTION getPhone RETURN VARCHAR2, 16 MEMBER PROCEDURE setPhone (newPhone IN VARCHAR2) 17 ); 18 /
Type created. SQL> SQL> SQL> CREATE OR REPLACE TYPE BODY address AS
2 MEMBER PROCEDURE ChangeAddress ( 3 st_1 IN VARCHAR2, st_2 IN VARCHAR2, cty IN VARCHAR2, 4 state IN VARCHAR2, zip IN VARCHAR2) IS 5 BEGIN 6 IF (st_1 IS NULL) THEN 18 RAISE_application_error(-20001,"The new Address is invalid."); 19 ELSE 20 street_1 := st_1; 21 street_2 := st_2; 22 city := cty; 23 state_abbr := upper(state); 24 zip_code := zip; 25 END IF; 26 END; 27 28 MEMBER FUNCTION getStreet (line_no IN number) 29 RETURN VARCHAR2 IS 30 BEGIN 31 IF line_no = 1 THEN 32 RETURN street_1; 33 ELSIF line_no = 2 THEN 34 RETURN street_2; 35 ELSE 36 RETURN " "; 37 END IF; 38 END; 39 40 MEMBER FUNCTION getCity RETURN VARCHAR2 IS 41 BEGIN 42 RETURN city; 43 END; 44 45 MEMBER FUNCTION getStateAbbr RETURN VARCHAR2 IS 46 BEGIN 47 RETURN state_abbr; 48 END; 49 50 MEMBER FUNCTION getPostalCode RETURN VARCHAR2 IS 51 BEGIN 52 RETURN zip_code; 53 END; 54 55 MEMBER FUNCTION getPhone RETURN VARCHAR2 IS 56 BEGIN 57 RETURN phone_number; 58 END; 59 60 MEMBER PROCEDURE setPhone (newPhone IN VARCHAR2) IS 61 BEGIN 62 phone_number := newPhone; 63 END; 64 END; 65 /
Type body created. SQL> SQL> CREATE OR REPLACE TYPE appartment AS OBJECT (
2 BldgName VARCHAR2(40), 3 BldgAddress address, 4 BldgMgr INTEGER, 5 MEMBER PROCEDURE ChangeMgr (NewMgr IN INTEGER), 6 ORDER MEMBER FUNCTION Compare (OtherAppartment IN appartment) 7 RETURN INTEGER 8 ); 9 /
Type created. SQL> SQL> CREATE OR REPLACE TYPE BODY appartment AS
2 MEMBER PROCEDURE ChangeMgr(NewMgr IN INTEGER) IS 3 BEGIN 4 BldgMgr := NewMgr; 5 END; 6 7 ORDER MEMBER FUNCTION Compare (OtherAppartment IN appartment) 8 RETURN INTEGER IS 9 BldgName1 VARCHAR2(40); 10 BldgName2 VARCHAR2(40); 11 BEGIN 12 BldgName1 := upper(ltrim(rtrim(BldgName))); 13 BldgName2 := upper(ltrim(rtrim(OtherAppartment.BldgName))); 14 15 IF BldgName1 = BldgName2 THEN 16 RETURN 0; 17 ELSIF BldgName1 < BldgName2 THEN 18 RETURN -1; 19 ELSE 20 RETURN 1; 21 END IF; 22 END; 23 END; 24 /
Type body created. SQL> SQL> CREATE TABLE appartments OF appartment; Table created. SQL> SQL> set echo on SQL> SQL> INSERT INTO appartments values (appartment("Building",address("2 Square"," ","Lansing","MI","48823"," "),597)); 1 row created. SQL> INSERT INTO appartments values (appartment("East Storage Shed",address("1 Rd","","Lansing","MI","48823",""),598)); 1 row created. SQL> INSERT INTO appartments values (appartment("Headquarters Building",address("1 Jeff","","Detroit","MI","48226",""),599)); 1 row created. SQL> SQL> SELECT * from appartments; BLDGNAME
BLDGADDRESS(STREET_1, STREET_2, CITY, STATE_ABBR,
BLDGMGR
Building ADDRESS("2 Square", " ", "Lansing", "MI", "48823",
" ")
597
East Storage Shed ADDRESS("1 Rd", NULL, "Lansing", "MI", "48823", NU LL)
598
Headquarters Building ADDRESS("1 Jeff", NULL, "Detroit", "MI", "48226", NULL)
599
3 rows selected. SQL> SQL> drop table appartments; Table dropped. SQL> SQL> drop type appartment; Type dropped. SQL> --
</source>
