Oracle PL/SQL - Populate Object Type

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. Nested Tables

Description

Populate Object Type

Demo

SQL>
SQL> drop table emp;

Table dropped.-- w  ww  .  j  ava 2 s.  c o  m
SQL>
SQL> create table emp(
  2    empno    number(4,0),
  3    ename    varchar2(10),
  4    job      varchar2(9),
  5    mgr      number(4,0),
  6    hiredate date,
  7    sal      number(7,2),
  8    comm     number(7,2),
  9    deptno   number(2,0)
 10  );

Table created.
SQL>
SQL>
SQL> insert into emp values(7369, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10);
SQL> insert into emp values(7499, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30);
SQL>
SQL> create or replace type emp2_oty is object (empNo    NUMBER,
  2                                             eName    VARCHAR2(10),
  3                                             deptNo   NUMBER );
  4  /
SQL> create or replace type emp2_nt is table of emp2_oty;
  2  /

Type created.
SQL> create or replace function f_getEmps_nt
  2   (i_deptNo number, i_hireDate DATE)
  3  return emp2_nt
  4  is
  5        v_emp2_nt emp2_nt:=emp2_nt();
  6        cursor c_emp is select * from emp;
  7  begin
  8     for r_emp in c_emp
  9     loop
 10        if i_deptNo is null then
 11           if i_hireDate is null
 12           or to_char(i_hireDate,'mm')= to_char(r_emp.hireDate,'mm')
 13           then
 14              v_emp2_nt.extend;
 15              v_emp2_nt(v_emp2_nt.last):=emp2_oty(r_emp.empNo, r_emp.eName,r_emp.deptno);
 16         end if;
 17       elsif i_deptNo=r_emp.deptNo then
 18         v_emp2_nt.extend;
 19         v_emp2_nt(v_emp2_nt.last):=emp2_oty(r_emp.empno, r_emp.ename,r_emp.deptno);
 20       end if;
 21    end loop;
 22    return v_emp2_nt;
 23  end;
 24  /

Function created.
SQL>
SQL>
SQL> declare
  2         v_temp_nt emp2_nt;
  3  begin
  4         v_temp_nt:=f_getEmps_nt(20,null);
  5         DBMS_OUTPUT.put_line('Received:'||v_temp_nt.count);
  6         v_temp_nt:=f_getEmps_nt(null,sysdate);
  7         DBMS_OUTPUT.put_line('Received:'||v_temp_nt.count);
  8         v_temp_nt:=f_getEmps_nt(null,null);
  9         DBMS_OUTPUT.put_line('Received:'||v_temp_nt.count);
 10  end;
 11  /
Received:0
Received:0
Received:2

PL/SQL procedure successfully completed.
SQL>
SQL> --Or
SQL>
SQL> select F_GETEMPS_NT (10,null) from dual
  2  /

F_GETEMPS_NT(10,NULL)(EMPNO, ENAME, DEPTNO)
--------------------------------------------------------------------------------
EMP2_NT(EMP2_OTY(7369, 'KING', 10))
SQL>
SQL> SQL>

Related Topic