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