Oracle PL/SQL/Object Oriented Database/Self

Use self reference

   <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> -- "self" 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;
 6    END;
 7  END;
 8  /

Type body created. SQL> 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> -- Use the function we created: use the table alias in our SELECT as well as the qualifier, arow: SQL> SQL> SELECT x.arow.state, x.arow.amt, x.arow.mult(2)

 2  FROM aobjtable x;

AR AROW.AMT X.AROW.MULT(2) -- ---------- -------------- FL 25 50 AL 35 70 OH 15 30 SQL> SQL> SQL> DESC aobjtable;

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

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

      </source>
   
  

Use self to reference member variable

   <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) ||" days over your vacation limit." );
11      end if;
12    end;
13  end;
14  /

Type body created. SQL> SQL> SQL> create or replace type consultant under employee(

 2    hourly_rate number,
 3    overriding member procedure give_raise( p_increase number ),
 4    overriding member function yearly_compensation return number
 5  )
 6  /

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

 2  type body consultant as
 3    overriding member procedure give_raise( p_increase number ) is
 4    begin
 5      self.hourly_rate := self.hourly_rate + p_increase;
 6    end;
 7    overriding member function yearly_compensation return number is
 8    begin
 9      return self.hourly_rate * 40 * 52;
10    end;
11  end;
12  /

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

</source>