In the previous section, I used the parameter with a simple data type (or with %TYPE). We can also use parameters with %ROWTYPE. Let us consider the following example:
cursor c_emp is
select * from emp;
procedure dispEmp(p_emp emp%rowtype) as
dbms_output.put_line('No : ' || p_emp.empno);
dbms_output.put_line('Name : ' || p_emp.ename);
dbms_output.put_line('Salary : ' || p_emp.sal);
dbms_output.put_line('Deptno : ' || p_emp.deptno);
for r_emp in c_emp
If you clearly observe the above program, I am using the procedure ‘dispEmp’ to display the information of an employee line by line. I am using CURSOR outside the procedure. That means the cursor is accessible to both the main program and procedure ‘dispEmp’ as well. But my intention for ‘dispEmp’ is just to display the information of a given employee (actually passed through parameter).
In our main program, for each iteration of the FOR loop, I am passing an entire row of an employee to the parameter ‘p_emp’ of the procedure ‘dispEmp’. The variable ‘p_emp’ is declared as of type ‘emp%rowtype’. So, it can hold an entire row from the table ‘emp’ (which is the same as CURSOR).
The output of the above program will be something like printing labels for all employees.
blog comments powered by Disqus