Oracle PL/SQL/Object Oriented Database/Object Method

Access member variable in member function in a type

   <source lang="sql">
   

SQL> SQL> SQL> create or replace

 2  type employee as object(
 3    name varchar2(100),
 4    empno number,
 5    hiredate date,
 6    vacation_used number,
 7    final member procedure vacation( p_days number ),
 8    not instantiable member procedure give_raise( p_increase number ),
 9    not instantiable member function yearly_compensation return number
10  )
11  not instantiable
12  not final
13  /

Type created. SQL> SQL> create or replace

 2  type body employee as
 3    final member procedure vacation( p_days number ) is
 4    begin
 5      if p_days + self.vacation_used <= 10 then
 6        self.vacation_used := self.vacation_used + p_days;
 7      else
 8        raise_application_error(
 9          -20001,
10          "You are " || to_char(p_days + self.vacation_used - 10) ||
11          " days over your vacation limit." );
12      end if;
13    end;
14  end;
15  /

Type body created. SQL> SQL> SQL> create or replace

 2  type sales_rep
 3  under employee(
 4    salary number,
 5    commission number,
 6    overriding member procedure give_raise( p_increase number ),
 7    member procedure give_commission( p_increase number ),
 8    overriding member function yearly_compensation return number
 9  )
10  /

Type created. SQL> create or replace

 2  type body sales_rep as
 3   overriding member procedure give_raise( p_increase number ) is
 4   begin
 5     self.salary := self.salary + (self.salary * (p_increase/100));
 6   end;
 7   member procedure give_commission( p_increase number ) is
 8   begin
 9     self.rumission := self.rumission + p_increase;
10   end;
11   overriding member function yearly_compensation return number is
12   begin
13     return self.salary + self.rumission;
14   end;
15  end;
16  /

Type body created. SQL> SQL> drop type sales_rep; Type dropped. SQL> drop type employee; Type dropped. SQL> SQL>

 </source>
   
  

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>
   
  

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>
   
  

Creating User-defined Functions for Column Objects

   <source lang="sql">
  

SQL> -- Creating User-defined Functions for Column Objects SQL> SQL> CREATE OR REPLACE TYPE aobj AS object (

 2                    state CHAR(2),
 3                    amt NUMBER(5),
 4
 5                    MEMBER FUNCTION mult (times in number) RETURN number,
 6                    PRAGMA RESTRICT_REFERENCES(mult, WNDS));
 7  /

Type created. SQL> SQL> CREATE OR REPLACE TYPE BODY aobj AS

 2    MEMBER FUNCTION mult (times in number) RETURN number
 3    IS
 4    BEGIN
 5      RETURN times * self.amt; /* SEE BELOW */
 6    END;
 7  END;
 8  /

Type body created. SQL> SQL> CREATE TABLE aobjtable (arow aobj); Table created. SQL> SQL> SQL> DESC aobjtable;

Name                                                                                                  Null?    Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
AROW                                                                                                   AOBJ

SQL> SQL> drop table aobjtable; Table dropped. SQL> SQL>

 </source>
   
  

Demonstrates the member method.

   <source lang="sql">
   

SQL> SQL> SQL> CREATE OR REPLACE TYPE BookType AS OBJECT (

 2     rebate   NUMBER (10, 4),
 3     price           NUMBER (10, 2),
 4     MEMBER FUNCTION discount_price
 5        RETURN NUMBER
 6  )
 7  INSTANTIABLE FINAL;
 8  /

Type created. SQL> SQL> CREATE OR REPLACE TYPE BODY BookType

 2  AS
 3     MEMBER FUNCTION discount_price
 4        RETURN NUMBER
 5     IS
 6     BEGIN
 7        RETURN (SELF.price * (1 - SELF.rebate));
 8     END discount_price;
 9  END;
10  /

Type body created. SQL> SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> DECLARE

 2      v_price BookType := BookType(.1, 75.00);
 3   BEGIN
 4      dbms_output.put_line(v_price.discount_price);
 5   END;
 6   /

67.5 PL/SQL procedure successfully completed. SQL>

 </source>
   
  

This script builds a sample object type with member variables and functions

   <source lang="sql">
  

SQL> SQL> CREATE OR REPLACE TYPE myType AUTHID CURRENT_USER IS OBJECT

 2  ( fname VARCHAR2(20 CHAR)
 3  , lname  VARCHAR2(20 CHAR)
 4  , CONSTRUCTOR FUNCTION myType RETURN SELF AS RESULT
 5  , CONSTRUCTOR FUNCTION myType ( fname VARCHAR2, lname  VARCHAR2 )RETURN SELF AS RESULT
 6  , MEMBER PROCEDURE print_instance_variable
 7  , ORDER MEMBER FUNCTION equals( my_class myType ) RETURN NUMBER )
 8  INSTANTIABLE NOT FINAL;
 9  /

Type created. SQL> SQL> CREATE OR REPLACE TYPE BODY myType AS

 2    CONSTRUCTOR FUNCTION myType 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 myType(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 myType )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> DECLARE

 2    obj1 myType := myType;
 3    obj2 myType := myType("A","B");
 4  BEGIN
 5
 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  END;
16  /

Name [, ] Name [A, B] unequal. PL/SQL procedure successfully completed. SQL> SQL> SQL>

 </source>
   
  

This script demonstrates the order method.

   <source lang="sql">
  

SQL> CREATE OR REPLACE TYPE book_obj AS OBJECT (

 2     isbn        CHAR (10),
 3     title       VARCHAR2 (100),
 4     num_pages   NUMBER,
 5     ORDER MEMBER FUNCTION compare_book (p_isbn IN BOOK_OBJ)
 6        RETURN NUMBER
 7  );
 8  /

Type created. SQL> SQL> CREATE OR REPLACE TYPE BODY book_obj

 2  AS
 3     ORDER MEMBER FUNCTION compare_book (p_isbn IN BOOK_OBJ)
 4        RETURN NUMBER
 5     IS
 6     BEGIN
 7        IF p_isbn.isbn < SELF.isbn
 8        THEN
 9           RETURN 1;
10        ELSIF p_isbn.isbn > SELF.isbn
11        THEN
12           RETURN -1;
13        ELSE
14           RETURN 0;
15        END IF;
16     END compare_book;
17  END;
18  /

Type body created. 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 procedure

   <source lang="sql">
   

SQL> SQL> CREATE TABLE emp

 2     (id         VARCHAR2(10) NOT NULL,
 3      course     VARCHAR2(10),
 4      year       VARCHAR2(4),
 5      PRIMARY KEY (id));

Table created. SQL> CREATE OR REPLACE TYPE empType AS OBJECT

 2     (id                     VARCHAR2(10),
 3      course                 VARCHAR2(20),
 4      year                   VARCHAR2(4),
 5      MEMBER PROCEDURE       Delete_emp )
 6  /

Type created. SQL> SQL> CREATE OR REPLACE TYPE BODY empType AS

 2     MEMBER PROCEDURE
 3     Delete_emp IS
 4     BEGIN
 5           DELETE FROM emp
 6           WHERE emp.id = self.id;
 7     END Delete_emp;
 8  END;
 9  /

Type body created. SQL> drop table emp; Table dropped.

 </source>
   
  

Use method from object in select command

   <source lang="sql">
  

SQL> CREATE OR REPLACE TYPE aobj AS object (

 2                    state CHAR(2),
 3                    amt NUMBER(5),
 4
 5                    MEMBER FUNCTION mult (times in number) RETURN number,
 6                    PRAGMA RESTRICT_REFERENCES(mult, WNDS));
 7  /

Type created. SQL> SQL> SQL> CREATE OR REPLACE TYPE BODY aobj AS

 2    MEMBER FUNCTION mult (times in number) RETURN number
 3    IS
 4    BEGIN
 5      RETURN times * self.amt; /* SEE BELOW */
 6    END;
 7  END;
 8  /

Type body created. SQL> SQL> CREATE TABLE aobjtable (arow aobj); Table created. SQL> SQL> SQL> SQL> INSERT INTO aobjtable VALUES (aobj("FL",25)); 1 row created. SQL> INSERT INTO aobjtable VALUES (aobj("AL",35)); 1 row created. SQL> INSERT INTO aobjtable VALUES (aobj("OH",15)); 1 row created. SQL> SQL> select * from aobjtable; AROW(STATE, AMT)

---

AOBJ("FL", 25) AOBJ("AL", 35) AOBJ("OH", 15) SQL> SQL> DESC aobjtable;

Name                                                                                                  Null?    Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
AROW                                                                                                   AOBJ

SQL> SQL> drop table aobjtable; Table dropped. SQL> SQL>

 </source>