w3resource

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:

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:

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

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.

What is the difficulty level of this exercise?



Inviting useful, relevant, well-written and unique guest posts