Oracle PL/SQL - Adding a limit to BULK COLLECT

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

Introduction

With explicit cursors you can select a value somewhere in between.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- w w w. j  ava2 s .c o 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, 10);
SQL>
SQL> declare
  2       type text_nt is table of VARCHAR2(256);
  3       v_ename_nt text_nt;
  4       cursor c_emp is
  5          select eName
  6          from emp
  7         where deptNo=10;
  8       procedure p_print_row is
  9       begin
 10            if v_eName_nt.count=2 then
 11                DBMS_OUTPUT.put_line
 12                (v_eName_nt(1)||' '||v_eName_nt(2));
 13            elsif v_eName_nt.count=1 then
 14                DBMS_OUTPUT.put_line(v_eName_nt(1));
 15            end if;
 16       end;
 17  begin
 18       open c_emp;
 19       loop
 20            fetch c_emp bulk collect into v_eName_nt limit 2;
 21            p_print_row;
 22            exit when c_emp%NOTFOUND;
 23       end loop;
 24       close c_emp;
 25  end;
 26  /
KING BLAKE

PL/SQL procedure successfully completed.
SQL>

Related Topic