Oracle PL/SQL - Retrieve object table as a whole

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

Description

Retrieve object table as a whole

Demo

SQL>
SQL> create or replace type emp_oty is object (
  2        empNo    NUMBER,
  3        eName    VARCHAR2(10),--   w  ww .j  a v a 2  s  .co m
  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_oty emp_oty;
  3       v_out_tx VARCHAR2(2000);
  4  begin
  5       v_emp_oty:=emp_oty
  6            (100,'TestEmp',null,null,sysdate,1000,500,10);
  7       insert into t_emp
  8          values v_emp_oty;
  9
 10       update t_emp
 11          set sal=sal+500
 12       where empno=100;
 13
 14       select 'Income:'||t.f_getIncome_nr()
 15          into v_out_tx
 16          from t_emp t
 17       where t.empno=100;
 18  end;
 19  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
SQL>
SQL> declare
  2       v_emp_oty emp_oty;
  3  begin
  4        select value(t)
  5          into   v_emp_oty
  6          from t_emp t
  7        where empNo=100;
  8
  9        DBMS_OUTPUT.put_line('Name: '||v_emp_oty.eName);
 10  end;
 11  /
Name: TestEmp

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
SQL>

Related Topic