FETCH_ROWS v16
The FETCH_ROWS
function retrieves a row from a cursor.
<status> INTEGER FETCH_ROWS(<c> NUMBER)
Parameters
c
Cursor ID of the cursor from which to fetch a row.
status
Returns 1
if a row was successfully fetched, 0
if no more rows to fetch.
Examples
These examples fetch the rows from the emp
table and display the results.
DECLARE curid NUMBER; v_empno NUMBER(4); v_ename VARCHAR2(10); v_hiredate DATE; v_sal NUMBER(7,2); v_comm NUMBER(7,2); v_sql VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' || 'comm FROM emp'; v_status INTEGER; BEGIN curid := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native); DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno); DBMS_SQL.DEFINE_COLUMN(curid,2,v_ename,10); DBMS_SQL.DEFINE_COLUMN(curid,3,v_hiredate); DBMS_SQL.DEFINE_COLUMN(curid,4,v_sal); DBMS_SQL.DEFINE_COLUMN(curid,5,v_comm); v_status := DBMS_SQL.EXECUTE(curid); DBMS_OUTPUT.PUT_LINE('EMPNO ENAME HIREDATE SAL COMM'); DBMS_OUTPUT.PUT_LINE('----- ---------- ---------- -------- ' || '--------'); LOOP v_status := DBMS_SQL.FETCH_ROWS(curid); EXIT WHEN v_status = 0; DBMS_SQL.COLUMN_VALUE(curid,1,v_empno); DBMS_SQL.COLUMN_VALUE(curid,2,v_ename); DBMS_SQL.COLUMN_VALUE(curid,3,v_hiredate); DBMS_SQL.COLUMN_VALUE(curid,4,v_sal); DBMS_SQL.COLUMN_VALUE(curid,4,v_sal); DBMS_SQL.COLUMN_VALUE(curid,5,v_comm); DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename,10) || ' ' || TO_CHAR(v_hiredate,'yyyy-mm-dd') || ' ' || TO_CHAR(v_sal,'9,999.99') || ' ' || TO_CHAR(NVL(v_comm,0),'9,999.99')); END LOOP; DBMS_SQL.CLOSE_CURSOR(curid); END; EMPNO ENAME HIREDATE SAL COMM ----- ---------- ---------- -------- -------- 7369 SMITH 1980-12-17 800.00 .00 7499 ALLEN 1981-02-20 1,600.00 300.00 7521 WARD 1981-02-22 1,250.00 500.00 7566 JONES 1981-04-02 2,975.00 .00 7654 MARTIN 1981-09-28 1,250.00 1,400.00 7698 BLAKE 1981-05-01 2,850.00 .00 7782 CLARK 1981-06-09 2,450.00 .00 7788 SCOTT 1987-04-19 3,000.00 .00 7839 KING 1981-11-17 5,000.00 .00 7844 TURNER 1981-09-08 1,500.00 .00 7876 ADAMS 1987-05-23 1,100.00 .00 7900 JAMES 1981-12-03 950.00 .00 7902 FORD 1981-12-03 3,000.00 .00 7934 MILLER 1982-01-23 1,300.00 .00
- On this page
- Parameters
- Examples