Oracle PL/SQL/Object Oriented Database/Attribute

Reference type attribute through column name

  
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>

This script demonstrates attribute chaining.

   
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>