Mega Code Archive

 
Categories / Oracle PLSQL Tutorial / Object Oriented
 

Accessing the object table by reference

The built-in function ref returns a pointer to the object. The variable you need to declare is not of type employeeType, but ref employeeType. SQL> SQL> create type employeeType is object (   2      empNo    NUMBER,   3      eName    VARCHAR2(10),   4      job      VARCHAR2(9),   5      mgr      NUMBER,   6      hireDate DATE,   7      sal      NUMBER,   8      comm     NUMBER,   9      deptNo   NUMBER,  10      member procedure p_changeName (i_newName_tx VARCHAR2),  11      member function  f_getIncome_nr  return VARCHAR2  12  )  13  / SP2-0816: Type created with compilation warnings SQL> SQL> create or replace type body employeeType as   2      member function f_getIncome_nr return VARCHAR2 is   3      begin   4          return sal+comm;   5      end f_getIncome_nr;   6      member procedure p_changeName(i_newName_tx VARCHAR2) is   7      begin   8          eName:=i_newName_tx;   9      end p_changeName;  10  end;  11  / SP2-0818: Type Body created with compilation warnings SQL> SQL> create table t_emp of employeeType; Table created. SQL> SQL> declare   2      v_emp_oref ref employeeType;   3      v_employeeType employeeType;   4   5  begin   6      v_employeeType:=employeeType(100,'TestEmp',null,null,sysdate,1000,500,10);   7      insert into t_emp values v_employeeType;   8   9  10      select ref(t) into v_emp_oref from t_emp t where empNo=100;  11  12      update t_emp t set sal=sal+100 where ref(t)=v_emp_oref;  13  end;  14  / PL/SQL procedure successfully completed. SQL> SQL> select * from t_emp;      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ----------        100 TestEmp                         09-JUN-07       1100        500         10 SQL> SQL> drop table t_emp; Table dropped. SQL> drop type employeeType; Type dropped. SQL>