PL/SQL Cursor Exercises: FETCH multiple records and more than one columns from the same table

PL/SQL Cursor: Exercise-17 with Solution

Write a program in PL/SQL to FETCH multiple records and more than one columns from the same table.

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
    v_emp_rec employees%ROWTYPE;
    CURSOR cur_emp_name IS
      SELECT *
      FROM   employees;
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
                             || '  ::   Salary: '
                             || v_emp_rec.salary);
    END LOOP;
    CLOSE cur_emp_name;
END; 
/

Sample Output:

SQL> /
Name: Steven  ::   Salary: 24000
Name: Neena  ::   Salary: 17000
Name: Lex  ::   Salary: 17000
Name: Alexander  ::   Salary: 90
Name: Bruce  ::   Salary: 6000
Name: David  ::   Salary: 4800
Name: Valli  ::   Salary: 4800
Name: Diana  ::   Salary: 4200
Name: Nancy  ::   Salary: 12008
Name: Daniel  ::   Salary: 9000
...

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to FETCH more than one record and single column from a table.
Next: Write a program in PL/SQL to FETCH multiple records and more than one columns from different tables.