FETCH_ROWS procedure - Retrieve a row from a cursor
The FETCH_ROWS function retrieves a row from a cursor
Syntax
Parameters
- c
- An input argument of type INTEGER that specifies the cursor ID of the cursor from which to fetch a row.
- ret
- An output argument of type INTEGER that returns 1 if a row was fetched successfully, 0 if there are no rows to fetch.
Authorization
EXECUTE privilege on the DBMS_SQL module.
Usage notes
This procedure can be invoked using function invocation syntax in a PL/SQL assignment statement.
Examples
Example 1: The following
examples fetches the rows from the "EMP" table and displays the results.
SET SERVEROUTPUT ON@
CREATE TABLE emp (
empno DECIMAL(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename VARCHAR(10),
job VARCHAR(9),
mgr DECIMAL(4),
hiredate TIMESTAMP(0),
sal DECIMAL(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
comm DECIMAL(7,2) )@
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL)@
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300)@
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500)@
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL)@
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400)@
BEGIN
DECLARE curid INTEGER;
DECLARE v_empno DECIMAL(4);
DECLARE v_ename VARCHAR(10);
DECLARE v_hiredate DATE;
DECLARE v_sal DECIMAL(7, 2);
DECLARE v_comm DECIMAL(7, 2);
DECLARE v_sql VARCHAR(50);
DECLARE v_status INTEGER;
DECLARE v_rowcount INTEGER;
SET v_sql = 'SELECT empno, ename, hiredate, sal, ' || 'comm FROM emp';
CALL DBMS_SQL.OPEN_CURSOR(curid);
CALL DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
CALL DBMS_SQL.DEFINE_COLUMN_NUMBER(curid, 1, v_empno);
CALL DBMS_SQL.DEFINE_COLUMN_VARCHAR(curid, 2, v_ename, 10);
CALL DBMS_SQL.DEFINE_COLUMN_DATE(curid, 3, v_hiredate);
CALL DBMS_SQL.DEFINE_COLUMN_NUMBER(curid, 4, v_sal);
CALL DBMS_SQL.DEFINE_COLUMN_NUMBER(curid, 5, v_comm);
CALL DBMS_SQL.EXECUTE(curid, v_status);
CALL DBMS_OUTPUT.PUT_LINE('EMPNO ENAME HIREDATE SAL
COMM');
CALL DBMS_OUTPUT.PUT_LINE('----- ---------- ---------- --------
' || '--------');
FETCH_LOOP: LOOP
CALL DBMS_SQL.FETCH_ROWS(curid, v_status);
IF v_status = 0 THEN
LEAVE FETCH_LOOP;
END IF;
CALL DBMS_SQL.COLUMN_VALUE_NUMBER(curid, 1, v_empno);
CALL DBMS_SQL.COLUMN_VALUE_VARCHAR(curid, 2, v_ename);
CALL DBMS_SQL.COLUMN_VALUE_DATE(curid, 3, v_hiredate);
CALL DBMS_SQL.COLUMN_VALUE_NUMBER(curid, 4, v_sal);
CALL DBMS_SQL.COLUMN_VALUE_NUMBER(curid, 5, v_comm);
CALL 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 FETCH_LOOP;
CALL DBMS_SQL.CLOSE_CURSOR(curid);
END@This example results in the following output:
SET SERVEROUTPUT ON
DB20000I The SET SERVEROUTPUT command completed successfully.
CREATE TABLE emp (empno DECIMAL(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename VARCHAR(10), job VARCHAR(9), mgr DECIMAL(4),
hiredate TIMESTAMP(0),
sal DECIMAL(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
comm DECIMAL(7,2) )
DB20000I The SQL command completed successfully.
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL)
DB20000I The SQL command completed successfully.
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300)
DB20000I The SQL command completed successfully.
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500)
DB20000I The SQL command completed successfully.
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL)
DB20000I The SQL command completed successfully.
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400)
DB20000I The SQL command completed successfully.
BEGIN
DECLARE curid INTEGER;
DECLARE v_empno DECIMAL(4);
DECLARE v_ename VARCHAR(10);
DECLARE v_hiredate DATE;
DECLARE v_sal DECIMAL(7, 2);
DECLARE v_comm DECIMAL(7, 2);
DECLARE v_sql VARCHAR(50);
DECLARE v_status INTEGER;
DECLARE v_rowcount INTEGER;
SET v_sql = 'SELECT empno, ename, hiredate, sal, ' || 'comm FROM emp';
CALL DBMS_SQL.OPEN_CURSOR(curid);
CALL DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
CALL DBMS_SQL.DEFINE_COLUMN_NUMBER(curid, 1, v_empno);
CALL DBMS_SQL.DEFINE_COLUMN_VARCHAR(curid, 2, v_ename, 10);
CALL DBMS_SQL.DEFINE_COLUMN_DATE(curid, 3, v_hiredate);
CALL DBMS_SQL.DEFINE_COLUMN_NUMBER(curid, 4, v_sal);
CALL DBMS_SQL.DEFINE_COLUMN_NUMBER(curid, 5, v_comm);
CALL DBMS_SQL.EXECUTE(curid, v_status);
CALL DBMS_OUTPUT.PUT_LINE('EMPNO ENAME HIREDATE SAL
COMM');
CALL DBMS_OUTPUT.PUT_LINE('----- ---------- ---------- --------
' || '--------');
FETCH_LOOP: LOOP
CALL DBMS_SQL.FETCH_ROWS(curid, v_status);
IF v_status = 0 THEN
LEAVE FETCH_LOOP;
END IF;
CALL DBMS_SQL.COLUMN_VALUE_NUMBER(curid, 1, v_empno);
CALL DBMS_SQL.COLUMN_VALUE_VARCHAR(curid, 2, v_ename);
CALL DBMS_SQL.COLUMN_VALUE_DATE(curid, 3, v_hiredate);
CALL DBMS_SQL.COLUMN_VALUE_NUMBER(curid, 4, v_sal);
CALL DBMS_SQL.COLUMN_VALUE_NUMBER(curid, 5, v_comm);
CALL 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 FETCH_LOOP;
CALL DBMS_SQL.CLOSE_CURSOR(curid);
END
DB20000I The SQL command completed successfully.
EMPNO ENAME HIREDATE SAL COMM
----- ---------- ---------- -------- --------
7369 SMITH 1980-12-17 800.00 0.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 0.00
7654 MARTIN 1981-09-28 1,250.00 1,400.00