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.
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; /
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
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 :
- 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