PL/SQL Cursor Exercises: Declare a record datatype with same datatype of tables using %TYPE attribute

PL/SQL Cursor: Exercise-11 with Solution

Write a program in PL/SQL to declare a record datatype with same datatype of tables using %TYPE attribute.

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 cur_emp_detail IS
      SELECT employee_id,
             first_name,
             last_name,
             salary
      FROM   employees;
    TYPE type_record_type IS RECORD (
      emp_id employees.employee_id%TYPE,
      emp_f_name employees.first_name%TYPE,
      emp_l_name employees.last_name%TYPE,
      emp_s_salary employees.salary%TYPE );
    emp_rec_type type_record_type; 
BEGIN
    OPEN cur_emp_detail;
    LOOP
        FETCH cur_emp_detail INTO emp_rec_type;
        EXIT WHEN cur_emp_detail%NOTFOUND;
        dbms_output.Put_line('Employees Information::  '
                             ||'  ID: '
                             ||emp_rec_type.emp_id
                             ||'|   Name: '
                             ||emp_rec_type.emp_f_name
                             ||' '
                             ||emp_rec_type.emp_l_name
                             ||'|   Salary: '
                             ||emp_rec_type.emp_s_salary);
    END LOOP;
    dbms_output.Put_line('Total number of Employees : '
                         ||cur_emp_detail%rowcount);
    CLOSE cur_emp_detail;
END;
/

Sample Output:

SQL> /
Employees Information::    ID: 100|   Name: Steven King|   Salary: 24000
Employees Information::    ID: 101|   Name: Neena Kochhar|   Salary: 17000
Employees Information::    ID: 102|   Name: Lex De Haan|   Salary: 17000
Employees Information::    ID: 103|   Name: Alexander Hunold|   Salary: 9000
Employees Information::    ID: 104|   Name: Bruce Ernst|   Salary: 6000
Employees Information::    ID: 105|   Name: David Austin|   Salary: 4800
Employees Information::    ID: 106|   Name: Valli Pataballa|   Salary: 4800
Employees Information::    ID: 107|   Name: Diana Lorentz|   Salary: 4200
Employees Information::    ID: 108|   Name: Nancy Greenberg|   Salary: 12008
Employees Information::    ID: 109|   Name: Daniel Faviet|   Salary: 9000
Employees Information::    ID: 110|   Name: John Chen|   Salary: 8200
Employees Information::    ID: 111|   Name: Ismael Sciarra|   Salary: 7700
...

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to retriev the records from the employees table and display them using cursors.
Next: Write a program in PL/SQL to create an implicit cursor with for loop.