Home arrow Oracle arrow Page 3 - Database Interaction with PL/SQL, RECORD and TABLE in Sub-programs

Using RECORD with sub-programs - Oracle

This is part 14 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, we concentrated on some tips for using procedures together with the introduction to functions. In this article we will look through several examples that cover using sub-programs in a very effective manner. I suggest you to go through part two in order to follow this article properly.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL, RECORD and TABLE in Sub-programs
  2. Mixing PROCEDURE and FUNCTION
  3. Using RECORD with sub-programs
  4. Returning a RECORD from FUNCTION
  5. Working with PL/SQL TABLE and sub-programs
By: Jagadish Chatarji
Rating: starstarstarstarstar / 18
August 23, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

In part two, I introduced the concept of RECORD. Now I would like to use RECORD together with sub-programs. Let us start with an example:

declare

TYPE t_emprec IS RECORD

(

name emp.ename%type,

salary emp.sal%type,

job emp.job%type

);

CURSOR c_emp is

select ename,sal,job from emp;

r_emp t_emprec;

procedure dispEmp(v_emprec t_emprec) is

begin

dbms_output.put_line ('Name : ' || v_emprec.name);

dbms_output.put_line ('Salary : ' || v_emprec.salary);

dbms_output.put_line ('Job : ' || v_emprec.job);

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

end;

begin

for r_emp in c_emp

loop

dispEmp(r_emp);

end loop;

end;

Let me explain this program part by part. The first part could be:

TYPE t_emprec IS RECORD

(

name emp.ename%type,

salary emp.sal%type,

job emp.job%type

);

The above defines a new data type named ‘t_emprec’ (just like %ROWTYPE with limited specified fields) which can hold three fields, namely ‘name’, `salary’ and ‘job’.

CURSOR c_emp is

select ename,sal,job from emp;

The above statement defined a cursor ‘c_emp’ based on SELECT statement provided.

r_emp t_emprec;

The above statement declares a variable ‘r_emp’ based on the datatype ‘t_emprec’. This means ‘r_emp’ internally contains the fields ‘name’, `salary’ and ‘job’. All the above declarations are available at the main program level. So, according to logic, we can use them throughout our program (including sub-programs).

procedure dispEmp(v_emprec t_emprec) is

begin

dbms_output.put_line ('Name : ' || v_emprec.name);

dbms_output.put_line ('Salary : ' || v_emprec.salary);

dbms_output.put_line ('Job : ' || v_emprec.job);

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

end;

The above is a procedure named ‘dispEmp’ which accepts a RECORD of type ‘t_emprec’ as a parameter and displays all the values available within the variable ‘v_emprec’ (of type ‘t_emprec’).

for r_emp in c_emp

loop

dispEmp(r_emp);

end loop;

By using the above loop, we iterate through all employees. For each iteration, we are sending the information to procedure ‘dispEmp’ (as a parameter value) to get them displayed on the screen.



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