Oracle PL/SQL - BULK COLLECT command

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Statement
  4. SELECT INTO

Description

BULK COLLECT command

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from w  ww. j  a  va  2  s .co  m
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> 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_getEmpDept_nt
  2         (i_deptNo NUMBER)
  3  return emp2_nt is
  4         v_emp2_nt emp2_nt:=emp2_nt();
  5  begin
  6         select emp2_oty(empNo, eName, deptNo)
  7           bulk collect into v_emp2_nt
  8           from emp
  9        where deptNo=i_deptNo;
 10         return v_emp2_nt;
 11  end;
 12  /

Function created.
SQL>
SQL>

To get the same result by using explicit cursors.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from ww w .jav a  2  s .co  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> 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> create or replace type emp2_oty is object (empNo    NUMBER,
  2                                             eName    VARCHAR2(10),
  3                                             deptNo   NUMBER );
  4  /

Elapsed: 00:00:00.00
SQL> create or replace type emp2_nt is table of emp2_oty;
  2  /

Type created.
SQL> create or replace function f_getEmpDept_nt
  2        (i_deptNo NUMBER)
  3  return emp2_nt is
  4       v_emp2_nt emp2_nt:=emp2_nt();
  5       cursor c_emp is
  6       select emp2_oty(empNo, eName, deptNo)
  7       from emp
  8       where deptNo=i_deptNo;
  9  begin
 10       open c_emp;
 11        fetch c_emp bulk collect into v_emp2_nt;
 12       close c_emp;
 13       return v_emp2_nt;
 14  end;
 15  /

Function created.

Elapsed: 00:00:00.04
SQL>

Related Topic