PL/SQL Cursor Exercises: Fetch the first three rows of a result set into three records using Same explicit cursor into different variables

PL/SQL Cursor: Exercise-26 with Solution

Write a program in PL/SQL to fetch the first three rows of a result set into three records using Same explicit cursor into different variables.

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

job_id				varchar(25)
job_title			varchar(50)
min_salary			integer
max_salary			integer

PL/SQL Code:

DECLARE
  CURSOR empcur IS
    SELECT e.first_name,e.job_id, j.job_title
    FROM employees e, jobs j
    WHERE e.job_id = j.job_id AND e.manager_id = 148
    ORDER BY last_name;
 
  emp_job1 empcur%ROWTYPE;
  emp_job2 empcur%ROWTYPE;
  emp_job3 empcur%ROWTYPE;
 
BEGIN
  OPEN empcur;
  FETCH empcur INTO emp_job1; 
  FETCH empcur INTO emp_job2;  
  FETCH empcur INTO emp_job3; 

  CLOSE empcur;
 
  DBMS_OUTPUT.PUT_LINE(Rpad(emp_job1.first_name, 25, ' ')||emp_job1.job_title || ' (' || emp_job1.job_id || ')');
  DBMS_OUTPUT.PUT_LINE(Rpad(emp_job2.first_name, 25, ' ')||emp_job2.job_title || ' (' || emp_job2.job_id || ')');
  DBMS_OUTPUT.PUT_LINE(Rpad(emp_job3.first_name, 25, ' ')||emp_job3.job_title || ' (' || emp_job3.job_id || ')');
END;
/

Sample Output:

SQL> /
Elizabeth                Sales Representative (SA_REP)
Harrison                 Sales Representative (SA_REP)
Tayler                   Sales Representative (SA_REP)

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to show the uses of fetch one record at a time using fetch statement inside the loop.
Next: Write a PL/SQL block to show the uses of a variable in explicit cursor query, and no result set is affected despite that value of the variable is incremented after every fetch.