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:
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.
What is the difficulty level of this exercise?
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework