Oracle PL/SQL Tutorial/Object Oriented/Member Function

A sample object type with a MAP member function

   <source lang="sql">

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.</source>

A sample object type with an ORDER member function.

   <source lang="sql">

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.</source>

Call Object member function

   <source lang="sql">

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></source>

Compare two type object instances

   <source lang="sql">

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></source>

This script builds a sample object type with constructor.

   <source lang="sql">

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></source>

This script demonstrates the static method.

   <source lang="sql">

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></source>

Type with member function

   <source lang="sql">

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></source>

Type with toString and mapping functions

   <source lang="sql">

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.</source>