PL/SQL Cursor Exercises: Show the uses of fetch one record at a time using fetch statement inside the loop

PL/SQL Cursor: Exercise-25 with Solution

Write a program in PL/SQL to show the uses of fetch one record at a time using fetch statement inside the loop.

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

PL/SQL Code:

DECLARE
    CURSOR cur1 IS
      SELECT first_name,
             last_name,
             job_id
      FROM   employees
      WHERE  Regexp_like (job_id, 'S[TA]_MAN')
      ORDER  BY last_name;
    emp_firstname employees.first_name%TYPE; 
    emp_lastname  employees.last_name%TYPE; 
    emp_jobid     employees.job_id%TYPE; 
    CURSOR cur2 IS
      SELECT *
      FROM   employees
      WHERE  Regexp_like (job_id, '[ACADFIMKSA]_M[ANGR]')
      ORDER  BY job_id;
    all_employees employees%ROWTYPE; 
BEGIN
    OPEN cur1;
    LOOP -- Fetches 2 columns into variables
        FETCH cur1 INTO emp_firstname, emp_lastname, emp_jobid;
        EXIT WHEN cur1%NOTFOUND;
        dbms_output.Put_line(Rpad(emp_firstname, 25, ' ')
                             ||Rpad(emp_lastname, 25, ' ')
                             || emp_jobid);
    END LOOP;
    CLOSE cur1;
    dbms_output.Put_line('-------------------------------------');
    OPEN cur2;
    LOOP -- Fetches entire row into the v_employees record
        FETCH cur2 INTO all_employees;
        EXIT WHEN cur2%NOTFOUND;
        dbms_output.Put_line(Rpad(all_employees.first_name, 25, ' ')
                             ||Rpad(all_employees.last_name, 25, ' ')
                             || all_employees.job_id);
    END LOOP;
    CLOSE cur2;
END;
/

Sample Output:

SQL> /
Gerald                   Cambrault                SA_MAN
Alberto                  Errazuriz                SA_MAN
Adam                     Fripp                    ST_MAN
Payam                    Kaufling                 ST_MAN
Kevin                    Mourgos                  ST_MAN
Karen                    Partners                 SA_MAN
John                     Russell                  SA_MAN
Shanta                   Vollman                  ST_MAN
Matthew                  Weiss                    ST_MAN
Eleni                    Zlotkey                  SA_MAN
-------------------------------------
Shelley                  Higgins                  AC_MGR
Nancy                    Greenberg                FI_MGR
Michael                  Hartstein                MK_MAN
John                     Russell                  SA_MAN
Karen                    Partners                 SA_MAN
Alberto                  Errazuriz                SA_MAN
Gerald                   Cambrault                SA_MAN
Eleni                    Zlotkey                  SA_MAN

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to create a cursor displays the name and salary of each employee in the EMPLOYEES table whose salary is less than that specified by a passed-in parameter value.
Next: 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.