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

Rispondi