PL/SQL Cursor Exercises: FETCH multiple records and more than one columns from different tables

PL/SQL Cursor: Exercise-18 with Solution

Write a program in PL/SQL to FETCH multiple records and more than one columns from different tables.

Below example we are trying to fetch department names and employee names.

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: departments

department_id			integer
department_name			varchar(25)
manager_id			integer
location_id			integer

PL/SQL Code:

DECLARE
    CURSOR cur_emp_name IS
      SELECT first_name,
             last_name,
             department_name
      FROM   employees e,
             departments d
      WHERE  d.department_id = e.department_id;
    v_emp_rec cur_emp_name%ROWTYPE;
BEGIN
    OPEN cur_emp_name;
    LOOP
        FETCH cur_emp_name INTO v_emp_rec;
        exit WHEN cur_emp_name%NOTFOUND;
        dbms_output.Put_line('Name:  '
                             || v_emp_rec.first_name
                             || '  '
                             ||v_emp_rec.last_name
                             || '   ::   department: '
                             || v_emp_rec.department_name);
    END LOOP;
    CLOSE cur_emp_name;
END; 
 /

Sample Output:

SQL> /
Name:  Jennifer  Whalen   ::   department: Administration
Name:  Pat  Fay   ::   department: Marketing
Name:  Michael  Hartstein   ::   department: Marketing
Name:  Sigal  Tobias   ::   department: Purchasing
Name:  Karen  Colmenares   ::   department: Purchasing
Name:  Shelli  Baida   ::   department: Purchasing
Name:  Den  Raphaely   ::   department: Purchasing
Name:  Alexander  Khoo   ::   department: Purchasing
Name:  Guy  Himuro   ::   department: Purchasing
Name:  Susan  Mavris   ::   department: Human Resources
Name:  Kevin  Feeney   ::   department: Shipping
Name:  Jean  Fleaur   ::   department: Shipping
Name:  Adam  Fripp   ::   department: Shipping
...

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to FETCH multiple records and more than one columns from the same table.
Next: Write a program in PL/SQL to FETCH multiple records with the uses of nested cursor.