Oracle PL/SQL Tutorial/Object Oriented/Member Function

A sample object type with a MAP member function

SQL>
SQL> CREATE OR REPLACE TYPE myType AUTHID CURRENT_USER IS OBJECT
  2  ( my_number NUMBER
  3  , CONSTRUCTOR FUNCTION myType RETURN SELF AS RESULT
  4  , CONSTRUCTOR FUNCTION myType( my_number NUMBER )RETURN SELF AS RESULT
  5  , MEMBER PROCEDURE print_instance_variable
  6  , MAP MEMBER FUNCTION equals RETURN NUMBER )INSTANTIABLE NOT FINAL;
  7  /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY myType AS
  2    CONSTRUCTOR FUNCTION myType
  3    RETURN SELF AS RESULT IS
  4      my_instance_number NUMBER := 0;
  5    BEGIN
  6      SELF.my_number := my_instance_number;
  7      RETURN;
  8    END;
  9    CONSTRUCTOR FUNCTION myType( my_number NUMBER )
 10    RETURN SELF AS RESULT IS
 11    BEGIN
 12      SELF.my_number := my_number;
 13      RETURN;
 14    END;
 15    MEMBER PROCEDURE print_instance_variable IS
 16    BEGIN
 17      DBMS_OUTPUT.PUT_LINE("Instance Variable ["||SELF.my_number||"]");
 18    END;
 19    MAP MEMBER FUNCTION equals
 20    RETURN NUMBER IS
 21    BEGIN
 22      RETURN SELF.my_number;
 23
 24    END;
 25
 26  END;
 27  /
Type body created.
SQL>
SQL>
SQL> DECLARE
  2    obj1 myType := myType;
  3    obj2 myType := myType(1);
  4
  5  BEGIN
  6    obj1.print_instance_variable;
  7
  8    obj2.print_instance_variable;
  9
 10    IF obj1 = obj2 THEN
 11      DBMS_OUTPUT.PUT_LINE("equal.");
 12    ELSE
 13      DBMS_OUTPUT.PUT_LINE("unequal.");
 14    END IF;
 15
 16  END;
 17  /
Instance Variable [0]
Instance Variable [1]
unequal.
PL/SQL procedure successfully completed.

A sample object type with an ORDER member function.

SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE myType AUTHID CURRENT_USER IS OBJECT
  2  ( my_number NUMBER
  3  , my_name   VARCHAR2(20 CHAR)
  4  , CONSTRUCTOR FUNCTION myType RETURN SELF AS RESULT
  5  , CONSTRUCTOR FUNCTION myType ( my_number NUMBER, my_name   VARCHAR2 )RETURN SELF AS RESULT
  6  , MEMBER PROCEDURE print_instance_variable
  7  , ORDER MEMBER FUNCTION equals( my_class myType ) RETURN NUMBER )INSTANTIABLE NOT FINAL;
  8  /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY myType AS
  2    CONSTRUCTOR FUNCTION myType
  3    RETURN SELF AS RESULT IS
  4      my_instance_number NUMBER := 0;
  5      my_instance_name   VARCHAR2(20 CHAR) := "";
  6
  7    BEGIN
  8      SELF.my_number := my_instance_number;
  9      SELF.my_name := my_instance_name;
 10      RETURN;
 11
 12    END;
 13
 14    CONSTRUCTOR FUNCTION myType( my_number NUMBER , my_name   VARCHAR2 )
 15    RETURN SELF AS RESULT IS
 16    BEGIN
 17      SELF.my_number := my_number;
 18      SELF.my_name := my_name;
 19      RETURN;
 20    END;
 21
 22    MEMBER PROCEDURE print_instance_variable IS
 23    BEGIN
 24      DBMS_OUTPUT.PUT_LINE("Number:"||SELF.my_number);
 25      DBMS_OUTPUT.PUT_LINE("Name  :"||SELF.my_name);
 26    END;
 27    ORDER MEMBER FUNCTION equals( my_class myType )RETURN NUMBER IS
 28      false_value NUMBER := 0;
 29      true_value  NUMBER := 1;
 30    BEGIN
 31      IF SELF.my_number = my_class.my_number AND SELF.my_name = my_class.my_name THEN
 32        RETURN true_value;
 33      ELSE
 34        RETURN false_value;
 35      END IF;
 36    END;
 37  END;
 38  /
Type body created.
SQL>
SQL> DECLARE
  2    obj1 myType := myType;
  3    obj2 myType := myType(1,"My Object");
  4
  5  BEGIN
  6    obj1.print_instance_variable;
  7
  8    obj2.print_instance_variable;
  9
 10    IF obj1.equals(obj2) = 1 THEN
 11      DBMS_OUTPUT.PUT_LINE("equal.");
 12    ELSE
 13      DBMS_OUTPUT.PUT_LINE("unequal.");
 14    END IF;
 15
 16  END;
 17  /
Number:0
Name  :
Number:1
Name  :My Object
unequal.
PL/SQL procedure successfully completed.

Call Object member function

SQL>
SQL> CREATE Or Replace TYPE ProductType AS OBJECT (
  2    id          NUMBER,
  3    name        VARCHAR2(15),
  4    description VARCHAR2(22),
  5    price       NUMBER(5, 2),
  6    days_valid  NUMBER,
  7
  8    MEMBER FUNCTION getByDate RETURN DATE
  9  );
 10  /
Type created.
SQL>
SQL> CREATE or replace TYPE BODY ProductType AS
  2   MEMBER FUNCTION getByDate RETURN DATE IS
  3      v_by_date DATE;
  4    BEGIN
  5     SELECT SYSDATE
  6      INTO v_by_date
  7      FROM dual;
  8
  9     RETURN v_by_date;
 10    END;
 11  END;
 12  /
Type body created.
SQL>
SQL> CREATE TABLE products (
  2    product           ProductType,
  3    count NUMBER
  4  );
Table created.
SQL>
SQL>  created.
SQL>
SQL>
SQL> INSERT INTO products (product,count) VALUES (
  2            ProductType(1, "AA", "BBB", 3.95, 10),50
  3  );
1 row created.
SQL>
SQL> INSERT INTO products (product,count) VALUES (
  2            ProductType(2, "CC", "DDDD", 2.99, 5),25
  3  );
1 row created.
SQL>
SQL> SELECT p.product.getByDate()
  2  FROM products p;
P.PRODUCT
---------
05-JUN-07
05-JUN-07
SQL>
SQL> drop table products;
Table dropped.
SQL>
SQL>

Compare two type object instances

SQL> CREATE OR REPLACE TYPE empName AUTHID CURRENT_USER IS OBJECT
  2   ( fname VARCHAR2(20 CHAR)
  3   , lname  VARCHAR2(20 CHAR)
  4   , CONSTRUCTOR FUNCTION empName RETURN SELF AS RESULT
  5   , CONSTRUCTOR FUNCTION empName ( fname VARCHAR2, lname  VARCHAR2 )RETURN SELF AS RESULT
  6   , MEMBER PROCEDURE print_instance_variable
  7   , ORDER MEMBER FUNCTION equals( my_class empName ) RETURN NUMBER )
  8   INSTANTIABLE NOT FINAL;
  9   /
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE BODY empName AS
  2     CONSTRUCTOR FUNCTION empName RETURN SELF AS RESULT IS
  3       fname VARCHAR2(20 CHAR) := NULL;
  4       lname  VARCHAR2(20 CHAR) := NULL;
  5     BEGIN
  6       SELF.fname := fname;
  7       SELF.lname := lname;
  8       RETURN;
  9     END;
 10     CONSTRUCTOR FUNCTION empName(fname VARCHAR2, lname VARCHAR2)RETURN SELF AS RESULT IS
 11     BEGIN
 12       SELF.fname := fname;
 13       SELF.lname := lname;
 14        RETURN;
 15     END;
 16     MEMBER PROCEDURE print_instance_variable IS
 17     BEGIN
 18       DBMS_OUTPUT.PUT_LINE("Name ["||SELF.fname||", "||SELF.lname||"]");
 19     END;
 20
 21     ORDER MEMBER FUNCTION equals( my_class empName )RETURN NUMBER IS
 22        false_value NUMBER := 0;
 23       true_value  NUMBER := 1;
 24      BEGIN
 25       IF SELF.fname = my_class.fname AND SELF.lname = my_class.lname   THEN
 26         RETURN true_value;
 27       ELSE
 28         RETURN false_value;
 29       END IF;
 30     END;
 31   END;
 32    /
Type body created.
SQL>
SQL> CREATE OR REPLACE TYPE empType AUTHID CURRENT_USER IS OBJECT
  2   ( ename   empName
  3   , address VARCHAR2(20 CHAR)
  4   , city           VARCHAR2(14 CHAR)
  5   , state          VARCHAR2(2 CHAR)
  6   , postal_code    VARCHAR2(5 CHAR)
  7   , CONSTRUCTOR FUNCTION empType RETURN SELF AS RESULT
  8   , CONSTRUCTOR FUNCTION empType(ename empName,address VARCHAR2,city VARCHAR2,state VARCHAR2,postal_code VARCHAR2 )RETURN SELF AS RESULT
  9   , CONSTRUCTOR FUNCTION empType(fname VARCHAR2, lname VARCHAR2, address VARCHAR2,city VARCHAR2,state VARCHAR2,postal_code VARCHAR2 )RETURN SELF AS RESULT
 10   , MEMBER PROCEDURE print_instance_variable, ORDER MEMBER FUNCTION equals( my_class empType ) RETURN NUMBER )
 11   INSTANTIABLE NOT FINAL;
 12   /
Type created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE BODY empType AS CONSTRUCTOR FUNCTION empType  RETURN SELF AS RESULT IS
  2       fname     VARCHAR2(20 CHAR) := NULL;
  3       lname      VARCHAR2(20 CHAR) := NULL;
  4       address VARCHAR2(20 CHAR) := NULL;
  5       city           VARCHAR2(14 CHAR) := NULL;
  6       state          VARCHAR2(2 CHAR)  := NULL;
  7       postal_code    VARCHAR2(5 CHAR)  := NULL;
  8     BEGIN
  9       SELF.ename := empName(fname,lname);
 10       SELF.address := address;
 11       SELF.city := city;
 12       SELF.state := state;
 13       SELF.postal_code := postal_code;
 14        RETURN;
 15     END;
 16     CONSTRUCTOR FUNCTION empType(ename empName, address VARCHAR2, city VARCHAR2, state VARCHAR2, postal_code VARCHAR2 )
 17     RETURN SELF AS RESULT IS
 18     BEGIN
 19       SELF.ename := ename;
 20       SELF.address := address;
 21       SELF.city := city;
 22        SELF.state := state;
 23       SELF.postal_code := postal_code;
 24        RETURN;
 25
 26     END;
 27
 28     CONSTRUCTOR FUNCTION empType(fname VARCHAR2,lname VARCHAR2,address VARCHAR2,city VARCHAR2,state VARCHAR2,postal_code VARCHAR2 )
 29     RETURN SELF AS RESULT IS
 30     BEGIN
 31       SELF.ename := empName(fname,lname);
 32        SELF.address := address;
 33       SELF.city := city;
 34        SELF.state := state;
 35       SELF.postal_code := postal_code;
 36       RETURN;
 37
 38     END;
 39     MEMBER PROCEDURE print_instance_variable IS
 40      BEGIN
 41        SELF.ename.print_instance_variable;
 42        DBMS_OUTPUT.PUT_LINE("First Name    : "||SELF.ename.fname);
 43        DBMS_OUTPUT.PUT_LINE("Last Name     : "||SELF.ename.lname);
 44        DBMS_OUTPUT.PUT_LINE("Street Address: "||SELF.address);
 45       DBMS_OUTPUT.PUT_LINE("City          : "||SELF.city);
 46       DBMS_OUTPUT.PUT_LINE("State         : "||SELF.state);
 47       DBMS_OUTPUT.PUT_LINE("Postal Code   : "||SELF.postal_code);
 48
 49     END;
 50
 51     ORDER MEMBER FUNCTION equals( my_class empType)RETURN NUMBER IS
 52       false_value NUMBER := 0;
 53       true_value  NUMBER := 1;
 54     BEGIN
 55       IF SELF.ename.equals(my_class.ename) = true_value AND SELF.address = my_class.address AND SELF.city = my_class.city AND SELF.state = my_class.state AND
 56          SELF.postal_code = my_class.postal_code THEN
 57         RETURN true_value;
 58       ELSE
 59         RETURN false_value;
 60
 61       END IF;
 62
 63     END;
 64
 65   END;
 66   /
Type body created.
SQL>
SQL>
SQL>
SQL>
SQL> DECLARE
  2     obj1 empType := empType("A","B","1 str","CA","E","111");
  3     obj2 empType := empType(empName("A","B"),"1 rd","city","TX","222");
  4   BEGIN
  5     obj1.print_instance_variable;
  6
  7     obj2.print_instance_variable;
  8
  9     IF obj1.equals(obj2) = 1 THEN
 10       DBMS_OUTPUT.PUT_LINE("equal.");
 11     ELSE
 12       DBMS_OUTPUT.PUT_LINE("unequal.");
 13     END IF;
 14    END;
 15   /
Name [A, B]
First Name    : A
Last Name     : B
Street Address: 1 str
City          : CA
State         : E
Postal Code   : 111
Name [A, B]
First Name    : A
Last Name     : B
Street Address: 1 rd
City          : city
State         : TX
Postal Code   : 222
unequal.
PL/SQL procedure successfully completed.
SQL>

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 static method.

SQL>
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE PriceType AS OBJECT (
  2     discount_rate   NUMBER (10, 4),
  3     price           NUMBER (10, 2),
  4     STATIC FUNCTION new_price (p_price IN NUMBER,rebate IN NUMBER DEFAULT .1)
  5        RETURN NUMBER
  6  )
  7  INSTANTIABLE FINAL;
  8  /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY PriceType
  2  AS
  3     STATIC FUNCTION new_price(p_price IN NUMBER,rebate IN NUMBER DEFAULT .1)
  4        RETURN NUMBER
  5     IS
  6     BEGIN
  7        RETURN (p_price * (1 - rebate));
  8     END new_price;
  9  END;
 10  /
Type body created.
SQL>
SQL> -- exec DBMS_OUTPUT.PUT_LINE(PriceType.new_price(75));
SQL>

Type with member function

SQL>
SQL>
SQL> CREATE TYPE cd_keyword_tab_t AS TABLE OF VARCHAR2 (60);
  2  /
Type created.
SQL>
SQL> CREATE TYPE cd_t AS OBJECT(
  2     id                            INTEGER,
  3     title                         VARCHAR2 (60),
  4     artist                        VARCHAR2 (60),
  5     label                         VARCHAR2 (60),
  6     keywords                      cd_keyword_tab_t,
  7     MAP MEMBER FUNCTION compare   RETURN VARCHAR2
  8  );
  9  /
Type created.
SQL> drop type cd_t;
Type dropped.
SQL> drop type cd_keyword_tab_t;
Type dropped.
SQL>

Type with toString and mapping functions

SQL>
SQL> create or replace type Address_Type
  2  as object
  3  (  street_addr1   varchar2(25),
  4     street_addr2   varchar2(25),
  5     city           varchar2(30),
  6     state          varchar2(2),
  7     zip_code       number
  8  )
  9  /
Type created.
SQL>
SQL> alter type Address_Type
  2  REPLACE
  3  as object
  4  (  street_addr1   varchar2(25),
  5     street_addr2   varchar2(25),
  6     city           varchar2(30),
  7     state          varchar2(2),
  8     zip_code       number,
  9     member function toString return varchar2,
 10     map member function mapping_function return varchar2
 11  )
 12  /
Type altered.
SQL>
SQL> create or replace type body Address_Type
  2  as
  3      member function toString return varchar2
  4      is
  5      begin
  6          if ( street_addr2 is not NULL )
  7          then
  8              return street_addr1 || " " ||
  9                     street_addr2 || " " ||
 10                     city || ", " || state || " " || zip_code;
 11          else
 12              return street_addr1 || " " ||
 13                     city || ", " || state || " " || zip_code;
 14          end if;
 15      end;
 16
 17      map member function mapping_function return varchar2
 18      is
 19      begin
 20          return to_char( nvl(zip_code,0), "fm00000" ) ||
 21                 lpad( nvl(city," "), 30 ) ||
 22                 lpad( nvl(street_addr1," "), 25 ) ||
 23                 lpad( nvl(street_addr2," "), 25 );
 24      end;
 25  end;
 26  /
Type body created.
SQL>
SQL>
SQL> create table people
  2  ( name           varchar2(10),
  3    home_address   address_type,
  4    work_address   address_type
  5  )
  6  /
Table created.
SQL>
SQL>
SQL> create or replace type Address_Array_Type
  2  as varray(50) of Address_Type
  3  /
Type created.
SQL>
SQL> alter table people add previous_addresses Address_Array_Type
  2  /
Table altered.
SQL> set echo on
SQL>
SQL> declare
  2      l_prev_addresses   address_Array_Type;
  3    begin
  4            select p.previous_addresses into l_prev_addresses from people p
  5             where p.name = "Tom Kyte";
  6
  7            l_prev_addresses.extend;
  8            l_prev_addresses(l_prev_addresses.count) := Address_Type( "1 Street", null,"Reston", "VA", 45678 );
  9
 10            update people set previous_addresses = l_prev_addresses where name = "Tom Kyte";
 11    end;
 12  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

SQL>
SQL>
SQL> select name, prev.city, prev.state, prev.zip_code from people p, table( p.previous_addresses ) prev
  2  /
no rows selected
SQL>
SQL> drop table people;
Table dropped.
SQL> drop type Address_Array_Type;
Type dropped.
SQL> drop type address_type;
Type dropped.