Oracle PL/SQL/Object Oriented Database/Attribute
Reference type attribute through column name
<source lang="sql">
SQL> SQL> create or replace
2 type people as object ( 3 first_name varchar2(100), 4 last_name varchar2(100), 5 dob date, 6 phone varchar2(100), 7 member function get_last_name return varchar2, 8 member function get_phone_number return varchar2 ) 9 not final 10 /
Type created. SQL> SQL> SQL> create or replace
2 type body people as 3 member function get_last_name return varchar2 is 4 begin 5 return self.last_name; 6 end; 7 member function get_phone_number return varchar2 is 8 begin 9 return self.phone; 10 end; 11 end; 12 /
Type body created. SQL> SQL> create table people_table( p people ); Table created. SQL> SQL> SQL> select x.p.last_name
2 from people_table x 3 /
no rows selected SQL> SQL> drop table people_table; Table dropped. SQL> SQL> drop type people; Type dropped. SQL> SQL>
</source>
This script demonstrates attribute chaining.
<source lang="sql">
SQL> SQL> CREATE OR REPLACE TYPE addressType AS OBJECT (
2 address1 VARCHAR2 (30 CHAR), 3 address2 VARCHAR2 (30 CHAR), 4 city VARCHAR2 (30 CHAR), 5 state CHAR (2 CHAR) 6 ) 7 INSTANTIABLE FINAL; 8 /
Type created. SQL> SQL> CREATE OR REPLACE TYPE personType AS OBJECT (
2 fname VARCHAR2 (20), 3 lname VARCHAR2 (20) 4 ) 5 INSTANTIABLE FINAL; 6 /
SQL> SQL> CREATE OR REPLACE TYPE contactType AS OBJECT (
2 NAME personType, 3 address addressType, 4 phone NUMBER (10) 5 ) 6 INSTANTIABLE FINAL; 7 /
Type created. SQL> SQL> CREATE OR REPLACE TYPE publisher_obj AS OBJECT (
2 pub_name VARCHAR2 (30), 3 contact_info contactType, 4 MEMBER PROCEDURE show_contact 5 ) 6 INSTANTIABLE FINAL; 7 /
SP2-0816: Type created with compilation warnings SQL> SQL> CREATE OR REPLACE TYPE BODY publisher_obj
2 AS 3 MEMBER PROCEDURE show_contact 4 IS 5 BEGIN 6 DBMS_OUTPUT.put_line (SELF.pub_name); 7 DBMS_OUTPUT.put_line (SELF.contact_info.NAME.fname|| " "|| SELF.contact_info.NAME.lname); 8 DBMS_OUTPUT.put_line (SELF.contact_info.address.address1); 9 DBMS_OUTPUT.put_line (SELF.contact_info.address.city); 10 DBMS_OUTPUT.put_line (SELF.contact_info.address.state); 11 DBMS_OUTPUT.put_line (SELF.contact_info.phone); 12 RETURN; 13 END show_contact; 14 END; 15 /
SP2-0818: Type Body created with compilation warnings SQL> SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> DECLARE
2 v_person personType := personType ("R", "A"); 3 v_address addressType := addressType ("1 Way", NULL, "Springs", "CO"); 4 v_contact contactType := contactType (v_person, v_address, 5); 5 v_publisher publisher_obj := publisher_obj ("Press", v_contact); 6 BEGIN 7 v_publisher.show_contact; 8 END; 9 /
Press R A 1 Way Springs CO 5 PL/SQL procedure successfully completed. SQL>
</source>