Database Interaction with PL/SQL: Sub-programs in Depth - Using %ROWTYPE in parameter declarations

(Page 4 of 5 )

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:

declare

            cursor c_emp is

                        select * from emp;

            procedure dispEmp(p_emp emp%rowtype) as

            begin

                        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);

                        dbms_output.put_line('-----------------------');

            end;

BEGIN

            for r_emp in c_emp

            loop

                        dispEmp(r_emp);

            end loop;

END;

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.

Next: Introduction to FUNCTION >>
 

More Oracle Articles
More By Jagadish Chatarji

Comments On: Database Interaction with PL/SQL: Sub-programs in Depth

· Hello guys, this is my in-depth article on working with sub-programs in PL/SQL. You...   >>> Post your comment now!