Oracle PL/SQL - Fetching Same Explicit Cursor into Different Variables

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. Explicit Cursors

Introduction

The following code fetches the first five rows of a result set into five records, using five FETCH statements.

Each of which fetches into a different record variable.

The record variables are declared with %ROWTYPE.

Demo

SQL>
SQL> drop table emp;

Table dropped.--   w  w  w. j  av a2 s . co  m

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> drop TABLE jobs;
SQL>
SQL> CREATE TABLE jobs(
  2  job_id VARCHAR2(10),
  3  job_title VARCHAR2(35),
  4  min_salary NUMBER(6),
  5  max_salary NUMBER(6)) ;
SQL>
SQL>
SQL> INSERT INTO jobs VALUES ( 'CODER', 'President', 20000, 40000);
SQL> INSERT INTO jobs VALUES ( 'TESTER', 'Administration Vice President', 15000, 30000);
SQL>
SQL> DECLARE
  2    CURSOR c IS
  3      SELECT e.job_id, j.job_title
  4      FROM emp e, jobs j
  5      WHERE e.job_id = j.job_id AND e.manager_id = 100
  6      ORDER BY last_name;
  7
  8    -- Record variables for rows of cursor result set:
  9
 10    job1 c%ROWTYPE;
 11    job2 c%ROWTYPE;
 12
 13  BEGIN
 14    OPEN c;
 15    FETCH c INTO job1;  -- fetches first row
 16    FETCH c INTO job2;  -- fetches second row
 17    CLOSE c;
 18    DBMS_OUTPUT.PUT_LINE(job1.job_title || ' (' || job1.job_id || ')');
 19    DBMS_OUTPUT.PUT_LINE(job2.job_title || ' (' || job2.job_id || ')');
 20  END;
 21  /
()
()

PL/SQL procedure successfully completed.

SQL>

Related Topic