Oracle PL/SQL - Access object table by reference,

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. Object types

Description

Access object table by reference,

Demo

SQL>
SQL> create or replace type emp_oty is object (
  2        empNo    NUMBER,
  3        eName    VARCHAR2(10),-- from w  w  w.ja  v a  2  s. c  om
  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  /
SQL> create or replace type body emp_oty as
  2        member function f_getIncome_nr return VARCHAR2
  3        is
  4        begin
  5              return sal+comm;
  6        end f_getIncome_nr;
  7        member procedure p_changeName
  8             (i_newName_tx VARCHAR2)
  9        is
 10        begin
 11              eName:=i_newName_tx;
 12        end p_changeName;
 13  end;
 14  /

Type body created.
SQL> drop table t_emp;

Table dropped.

SQL>
SQL> create table t_emp of emp_oty;

Table created.

Elapsed: 00:00:00.02
SQL>
SQL>
SQL> declare
  2        v_emp_oref ref emp_oty;
  3  begin
  4        select ref(t)
  5           into   v_emp_oref
  6           from t_emp t
  7        where empNo=100;
  8
  9        update t_emp t
 10        set sal=sal+100
 11        where ref(t)=v_emp_oref;
 12  end;
 13  /

Elapsed: 00:00:00.02
SQL>

Related Topic