Oracle PL/SQL/Object Oriented Database/Constructor

Call object constructor in an insert statement

   
SQL>
SQL>
SQL> create or replace
  2  type person 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 person 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 or replace
  2  type new_employee under person (
  3    empno number,
  4    hiredate date,
  5    work_phone varchar2(100),
  6    overriding member function get_phone_number return varchar2,
  7    member function get_home_phone_number return varchar2 )
  8  not final
  9  /
Type created.
SQL>
SQL> create or replace
  2  type body new_employee as
  3    overriding member function get_phone_number return varchar2 is
  4    begin
  5      return self.work_phone;
  6    end;
  7    member function get_home_phone_number return varchar2 is
  8    begin
  9      return self.phone;
 10    end;
 11  end;
 12  /
Type body created.
SQL>
SQL>
SQL>
SQL>
SQL> create table person_table( p person );
Table created.
SQL>
SQL>
SQL>
SQL> insert into person_table values
  2  ( person( null, "Kyte", null, "703.555.5555" ) )
  3  /
1 row created.
SQL>
SQL> insert into person_table values
  2  ( new_employee(  null, "Beck", null, "703.555.1111", 1234, null, "703.555.2222" ) )
  3  /
1 row created.
SQL>
SQL> insert into person_table values
  2  ( new_employee(  null, "Viper", null, "703.555.3333", 5678, null, "703.555.4444" ) )
  3  /
1 row created.
SQL>
SQL> select * from person_table;

P(FIRST_NAME, LAST_NAME, DOB, PHONE)
--------------------------------------------------------------------------------
person(NULL, "Kyte", NULL, "703.555.5555")
NEW_EMPLOYEE(NULL, "Beck", NULL, "703.555.1111", 1234, NULL, "703.555.2222")
NEW_EMPLOYEE(NULL, "Viper", NULL, "703.555.3333", 5678, NULL, "703.555.4444")
SQL> drop table person_table;
Table dropped.
SQL>
SQL> drop type new_employee;
Type dropped.
SQL>
SQL> drop type person;
Type dropped.
SQL>
SQL>

Demonstrates object initialization.

    
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE BookType AS OBJECT (
  2     rebate   NUMBER (10, 4),
  3     price           NUMBER (10, 2),
  4     CONSTRUCTOR FUNCTION BookType (price NUMBER)
  5        RETURN SELF AS RESULT
  6  )
  7  INSTANTIABLE FINAL;
  8  /
SP2-0816: Type created with compilation warnings
SQL>
SQL> CREATE OR REPLACE TYPE BODY BookType
  2  AS
  3     CONSTRUCTOR FUNCTION BookType (price NUMBER)
  4        RETURN SELF AS RESULT
  5     AS
  6     BEGIN
  7        SELF.price := price * .9;
  8        RETURN;
  9     END BookType;
 10  END;
 11  /
SP2-0818: Type Body created with compilation warnings
SQL>
SQL>
SQL> DECLARE
  2     v_price   BookType;
  3  BEGIN
  4     v_price.price := 75;
  5     DBMS_OUTPUT.put_line (v_price.price);
  6  END;
  7  /
75
PL/SQL procedure successfully completed.

Insert value with constructor method

   
SQL>
SQL> create or replace
  2  type person as object (
  3   first_name varchar2(100),
  4   last_name varchar2(100),
  5   member function get_last_name return varchar2,
  6   member function get_phone_number return varchar2 )
  7  not final
  8  /
Type created.
SQL>
SQL>
SQL> create or replace
  2  type body person 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  /
Warning: Type Body created with compilation errors.
SQL>
SQL>
SQL> create table person_table( p person );
Table created.
SQL>
SQL>
SQL> insert into person_table values ( person( "Sean", "Viper" ));
1 row created.
SQL>
SQL> select * from person_table;

P(FIRST_NAME, LAST_NAME)
--------------------------------------------------------------------------------
person("Sean", "Viper")
SQL>
SQL> drop table person_table;
Table dropped.
SQL>
SQL>
SQL> drop type person;
Type dropped.

This script builds a sample object type with constructor.

   
SQL> CREATE OR REPLACE TYPE myType
  2  AUTHID CURRENT_USER IS OBJECT
  3  ( fname VARCHAR2(20 CHAR)
  4  , lname  VARCHAR2(20 CHAR)
  5  , CONSTRUCTOR FUNCTION myType RETURN SELF AS RESULT
  6  , CONSTRUCTOR FUNCTION myType ( fname VARCHAR2, lname  VARCHAR2 )RETURN SELF AS RESULT
  7  , MEMBER PROCEDURE print_instance_variable
  8  , ORDER MEMBER FUNCTION equals( my_class myType ) RETURN NUMBER )INSTANTIABLE NOT FINAL;
  9  /
Type created.
SQL>
SQL> SHOW ERRORS
No errors.
SQL>
SQL> CREATE OR REPLACE TYPE BODY myType AS
  2    CONSTRUCTOR FUNCTION myType
  3    RETURN SELF AS RESULT IS
  4      fname VARCHAR2(20 CHAR) := NULL;
  5      lname  VARCHAR2(20 CHAR) := NULL;
  6    BEGIN
  7      SELF.fname := fname;
  8      SELF.lname := lname;
  9      RETURN;
 10    END;
 11
 12    CONSTRUCTOR FUNCTION myType( fname VARCHAR2, lname  VARCHAR2 )
 13    RETURN SELF AS RESULT IS
 14    BEGIN
 15      SELF.fname := fname;
 16      SELF.lname := lname;
 17      RETURN;
 18    END;
 19    MEMBER PROCEDURE print_instance_variable IS
 20    BEGIN
 21      DBMS_OUTPUT.PUT_LINE("Name:"||SELF.fname||", "||SELF.lname);
 22
 23    END;
 24
 25    ORDER MEMBER FUNCTION equals( my_class myType )
 26    RETURN NUMBER IS
 27      false_value NUMBER := 0;
 28      true_value  NUMBER := 1;
 29    BEGIN
 30      IF SELF.fname = my_class.fname AND
 31         SELF.lname = my_class.lname   THEN
 32        RETURN true_value;
 33      ELSE
 34        RETURN false_value;
 35
 36      END IF;
 37
 38    END;
 39
 40  END;
 41  /
Type body created.
SQL>
SQL> SHOW ERRORS
No errors.
SQL>
SQL>

This script demonstrates the user-defined constructor method.

   
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE PriceType AS OBJECT (
  2     discount_rate   NUMBER (10, 4),
  3     price           NUMBER (10, 2),
  4     CONSTRUCTOR FUNCTION PriceType (price NUMBER)
  5        RETURN SELF AS RESULT
  6  )
  7  INSTANTIABLE FINAL;
  8  /

SQL>
SQL> CREATE OR REPLACE TYPE BODY PriceType
  2  AS
  3     CONSTRUCTOR FUNCTION PriceType (price NUMBER)
  4        RETURN SELF AS RESULT
  5     AS
  6     BEGIN
  7        SELF.price := price * .9;
  8        RETURN;
  9     END PriceType;
 10  END;
 11  /
Warning: Type Body created with compilation errors.
SQL> show errors
Errors for TYPE BODY PRICETYPE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/25     PLS-00539: subprogram "PRICETYPE" is declared in an object type
         body and must be defined in the object type specification
3/25     PLW-07203: parameter "SELF" may benefit from use of the NOCOPY
         compiler hint
5/20     PLS-00538: subprogram or cursor "DISCOUNT_PRICE" is declared in
         an object type specification and must be defined in the object
         type body
SQL>
SQL>
SQL>
SQL>  SET SERVEROUTPUT ON SIZE 1000000
SQL>  DECLARE
  2      v_price   PriceType := PriceType (75);
  3   BEGIN
  4      DBMS_OUTPUT.put_line (v_price.price);
  5   END;
  6   /

SQL>

Use constructor to create new objects

   
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE BookType AS OBJECT (
  2     rebate   NUMBER (10, 4),
  3     price           NUMBER (10, 2),
  4     CONSTRUCTOR FUNCTION BookType (price NUMBER)
  5        RETURN SELF AS RESULT
  6  )
  7  INSTANTIABLE FINAL;
  8  /
SP2-0816: Type created with compilation warnings
SQL>
SQL> CREATE OR REPLACE TYPE BODY BookType
  2  AS
  3     CONSTRUCTOR FUNCTION BookType (price NUMBER)
  4        RETURN SELF AS RESULT
  5     AS
  6     BEGIN
  7        SELF.price := price * .9;
  8        RETURN;
  9     END BookType;
 10  END;
 11  /
SP2-0818: Type Body created with compilation warnings
SQL>
SQL> DECLARE
  2     v_price   BookType := BookType (NULL, NULL);
  3  BEGIN
  4     v_price.price := 75;
  5     DBMS_OUTPUT.put_line (v_price.price);
  6  END;
  7  /
75
PL/SQL procedure successfully completed.
SQL>
SQL>

Use type Constructor to insert data to object table

   
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> desc people1
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 NAME                                                                                         VARCHAR2(30)
 DOB                                                                                          DATE
 HOME_ADDRESS                                                                                 ADDRESS_TYPE
 WORK_ADDRESS                                                                                 ADDRESS_TYPE
SQL>
SQL>
SQL> insert into people1 values ( "Tom", "15-mar-1965",
  2    address_type( "Reston", "123 Main Street", "Va", "45678" ),
  3    address_type( "Redwood", "1 Oracle Way", "Ca", "23456" ) );
1 row created.
SQL> /
1 row created.
SQL>
SQL>
SQL> select name, p.home_address.city from people1 p;
NAME                 HOME_ADDRESS.CITY
-------------------- ------------------------------
Tom                  Reston
Tom                  Reston
2 rows selected.
SQL>
SQL> drop table people1;
Table dropped.
SQL> drop type person_type;
Type dropped.
SQL> drop type address_type;
Type dropped.
SQL> --