Home arrow Oracle arrow Page 4 - Database Interaction with PL/SQL: Sub-programs in Depth

Using %ROWTYPE in parameter declarations - Oracle

This is part 13 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, we concentrated on procedures and discussed some issues of variable scoping with respect to sub-programs. In this article we proceed with some tips on using procedures together with the introduction to functions.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL: Sub-programs in Depth
  2. Sub-programs interacting with an Oracle database
  3. Procedures with parameters
  4. Using %ROWTYPE in parameter declarations
  5. Introduction to FUNCTION
By: Jagadish Chatarji
Rating: starstarstarstarstar / 12
August 16, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: