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>