PL/SQL Cursor Exercises: Create a table-based record using the %ROWTYPE attribute

PL/SQL Cursor: Exercise-7 with Solution

Write a program in PL/SQL to create a table-based record using the %ROWTYPE 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
    vr_employee employees%ROWTYPE;
	z_emp_id NUMBER:=&employee_id;
BEGIN
    SELECT *
    INTO   vr_employee
    FROM   employees
    WHERE  employee_id = z_emp_id;

    dbms_output.Put_line (vr_employee.first_name
                          ||' '
                          ||vr_employee.last_name
                          ||' has an ID of '||z_emp_id);
EXCEPTION
    WHEN no_data_found THEN
      Raise_application_error(-20001, 'The Employee '
                                     || 'is not in the database');
END; 
/

Sample Output:

SQL> /
Enter value for employee_id: 159
old   3:        z_emp_id NUMBER:=&employee_id;
new   3:        z_emp_id NUMBER:=159;
Lindsey Smith has an ID of 159

PL/SQL procedure successfully completed.

SQL> /
Enter value for employee_id: 459
old   3:        z_emp_id NUMBER:=&employee_id;
new   3:        z_emp_id NUMBER:=459;
DECLARE
*
ERROR at line 1:
ORA-20001: The Employee is not in the database
ORA-06512: at line 16

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to show the uses of SQL%NOTFOUND to determine if a UPDATE statement affected any rows.
Next: Write a program in PL/SQL to display a table based detail information for the employee of ID 149 from the employees table.