Oracle PL/SQL: Populate table from cursor

 
SET serveroutput ON
 
DECLARE
  -- Declare the PL/SQL table
  TYPE deptarr IS TABLE OF dept%ROWTYPE
       INDEX BY BINARY_INTEGER;
  d_arr deptarr;
 
  -- Declare cursor
  TYPE d_cur IS REF CURSOR RETURN dept%ROWTYPE;
  c1 d_cur;
 
  i NUMBER := 1;
BEGIN
  -- Populate the PL/SQL table from the cursor
  OPEN c1 FOR SELECT * FROM dept;
  LOOP
    EXIT WHEN c1%NOTFOUND;
    FETCH c1 INTO d_arr(i);
    i := i+1;
  END LOOP;
  CLOSE c1;
 
  -- Display the entire PL/SQL table on screen
  FOR i IN 1..d_arr.LAST LOOP
    DBMS_OUTPUT.put_line('DEPTNO : '||d_arr(i).deptno );
    DBMS_OUTPUT.put_line('DNAME  : '||d_arr(i).dname  );
    DBMS_OUTPUT.put_line('LOC    : '||d_arr(i).loc    );
    DBMS_OUTPUT.put_line('---------------------------');
  END LOOP;
END;
/

Fonte: http://psoug.org/snippet/Populate-table-from-cursor_535.htm

Leave a Reply