Oracle PL/SQL - Fetching Data with Explicit Cursors

Introduction

After opening an explicit cursor, you can fetch the rows of the query result set with the FETCH statement.

The basic syntax of a FETCH statement that returns one row is:

FETCH cursor_name INTO into_clause

The into_clause can be a list of variables or a single record variable.

For each column returned, the variable list or record must have a corresponding type-compatible variable or field.

You can define variable in %TYPE or %ROWTYPE for using them in FETCH statements.

The FETCH statement retrieves the current row, stores it into the variables or record, and advances the cursor to the next row.

The following code fetches the result sets of two explicit cursors one row at a time, using FETCH and %NOTFOUND inside LOOP statements.

The first FETCH statement retrieves column values into variables.

The second FETCH statement retrieves column values into a record.

The variables and record are declared with %TYPE and %ROWTYPE, respectively.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- w  w w.  j a  v  a2 s .com

SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25),
  5  email VARCHAR2(25),
  6  phone_number VARCHAR2(20),
  7  hire_date DATE,
  8  job_id VARCHAR2(10),
  9  salary NUMBER(8,2),
 10  commission_pct NUMBER(2,2),
 11  manager_id NUMBER(6),
 12  department_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-1987', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 90);
SQL> INSERT INTO emp VALUES( 200, 'Joe', 'Lee', 'abc', '123.123.9999', TO_DATE('17-JUN-1980', 'dd-MON-yyyy'), 'TESTER', 25000, NULL, NULL, 80);
SQL>
SQL>
SQL>
SQL> DECLARE
  2    CURSOR c1 IS
  3      SELECT last_name, job_id FROM emp
  4      ORDER BY last_name;
  5
  6    v_lastname  emp.last_name%TYPE;  -- variable for last_name
  7    v_jobid     emp.job_id%TYPE;     -- variable for job_id
  8
  9    CURSOR c2 IS
 10      SELECT * FROM emp
 11      WHERE REGEXP_LIKE (job_id, 'CODER')
 12      ORDER BY job_id;
 13
 14    v_emp emp%ROWTYPE;  -- record variable for row of table
 15
 16  BEGIN
 17    OPEN c1;
 18    LOOP  -- Fetches 2 columns into variables
 19
 20      FETCH c1 INTO v_lastname, v_jobid;
 21      EXIT WHEN c1%NOTFOUND;
 22      DBMS_OUTPUT.PUT_LINE( RPAD(v_lastname, 25, ' ') || v_jobid );
 23    END LOOP;
 24    CLOSE c1;
 25
 26    OPEN c2;
 27    LOOP  -- Fetches entire row into the v_emp record
 28      FETCH c2 INTO v_emp;
 29      EXIT WHEN c2%NOTFOUND;
 30      DBMS_OUTPUT.PUT_LINE( RPAD(v_emp.last_name, 25, ' ') ||
 31               v_emp.job_id );
 32    END LOOP;
 33    CLOSE c2;
 34  END;
 35  /
King                     CODER
Lee                      TESTER
King                     CODER

PL/SQL procedure successfully completed.

SQL>

Oracle PL/SQL - Fetching Data with Explicit Cursors

Introduction

Demo

Related Topic