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:

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
Table: jobs
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:

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?



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/plsql-exercises/cursor/plsql-cursor-exercise-26.php