Examples:
The following procedure displays employees by defining a cursor with two columns and, after fetching a row, calls COLUMN_VALUE to retrieve both column values:
CREATE OR REPLACE PROCEDURE showemps (where_in IN VARCHAR2 := NULL)
IS
cur INTEGER := DBMS_SQL.OPEN_CURSOR;
rec emp%ROWTYPE;
fdbk INTEGER;
BEGIN
DBMS_SQL.PARSE
(cur, 'SELECT empno, ename FROM emp ' ||
' WHERE ' || NVL (where_in, '1=1'),
DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN (cur, 1, rec.empno);
DBMS_SQL.DEFINE_COLUMN (cur, 2, rec.ename, 30);
fdbk := DBMS_SQL.EXECUTE (cur);
LOOP
/* Fetch next row. Exit when done. */
EXIT WHEN DBMS_SQL.FETCH_ROWS (cur) = 0;
DBMS_SQL.COLUMN_VALUE (cur, 1, rec.empno);
DBMS_SQL.COLUMN_VALUE (cur, 2, rec.ename);
DBMS_OUTPUT.PUT_LINE (TO_CHAR (rec.empno) || '=' || rec.ename);
END LOOP;
DBMS_SQL.CLOSE_CURSOR (cur);
END;
/
This next PL/SQL8 block fetches the hiredate and employee ID for all rows in the emp table and deposits values into two separate PL/SQL tables. Notice that since we know there are just 14 rows in the emp table, only one call is needed to the EXECUTE_AND_FETCH function to fetch all rows.
CREATE OR REPLACE PROCEDURE showall
IS
cur INTEGER := DBMS_SQL.OPEN_CURSOR;
fdbk INTEGER;
empno_tab DBMS_SQL.NUMBER_TABLE;
hiredate_tab DBMS_SQL.DATE_TABLE;
BEGIN
DBMS_SQL.PARSE
(cur, 'SELECT empno, hiredate FROM emp', DBMS_SQL.NATIVE);
/* Allow fetching of up to 100 rows. */
DBMS_SQL.DEFINE_ARRAY (cur, 1, empno_tab, 100, 1);
DBMS_SQL.DEFINE_ARRAY (cur, 2, hiredate_tab, 100, 1);
fdbk := DBMS_SQL.EXECUTE_AND_FETCH (cur);
/* This will show total numbers of rows fetched. */
DBMS_OUTPUT.PUT_LINE (fdbk);
/* Get values for all rows in one call. */
DBMS_SQL.COLUMN_VALUE (cur, 1, empno_tab);
DBMS_SQL.COLUMN_VALUE (cur, 2, hiredate_tab);
FOR rowind IN empno_tab.FIRST .. empno_tab.LAST
LOOP
DBMS_OUTPUT.PUT_LINE (empno_tab(rowind));
DBMS_OUTPUT.PUT_LINE (hiredate_tab(rowind));
END LOOP;
DBMS_SQL.CLOSE_CURSOR (cur);
END;
/ |
