Home arrow Oracle arrow Database Interaction with PL/SQL, Working with TABLE in Sub-programs, Parameter Modes

Database Interaction with PL/SQL, Working with TABLE in Sub-programs, Parameter Modes

This is part 15 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, we looked at several examples that covered the use of sub-programs. In this article we will work with PL/SQL TABLE types in between sub-programs. We will also discuss IN, OUT and IN OUT types of parameters in this article.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL, Working with TABLE in Sub-programs, Parameter Modes
  2. Returning PL/SQL TABLE from a FUNCTION to a PROCEDURE
  3. What are IN types of parameters?
  4. What are OUT types of parameters?
  5. What is an IN OUT type of parameter?
By: Jagadish Chatarji
Rating: starstarstarstarstar / 23
August 30, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Please note that all of the examples in this series have been tested only with Oracle 10g.  I didn’t really test them with all the previous versions of Oracle.  I suggest you refer to the documentation of the respective version you are using, if any of the programs failed to execute.

Passing PL/SQL TABLE to PROCEDURE

In my previous article, I stopped with a simple example of working on a PL/SQL TABLE together with sub programs.  Now, this will be a continuation of the same.  In this section, we will pass a PL/SQL TABLE itself as an argument to the procedure.  Let us consider the following program:

declare

 
  type t_emptbl is table of emp%rowtype;
  v_emptbl          t_emptbl;
 
  procedure dispEmp(p_emptbl t_emptbl) is
 
  begin
 
    for i in p_emptbl.first .. p_emptbl.last
    loop
        dbms_output.put_line(p_emptbl(i).ename ||
        ' earns ' || p_emptbl(i).sal);
    end loop;
 
  end;
 
  BEGIN
 
    select * bulk collect into v_emptbl from emp;
    dispEmp(v_emptbl);
 
  END;

Let me explain the above program part by part.

type t_emptbl is table of emp%rowtype;

The above statement defines a new data type ‘t_emptbl’ as an in-memory table representation.  This means it can store any number of rows (just like a table) from the table ‘emp’ (as we included ‘emp%rowtype’) in memory (without having any relation to the physical database).  Make sure that it is only a data type.  If you know the C language, you can think of it as a structured array.

v_emptbl          t_emptbl;

The above statement declares a variable ‘v_emptbl’ which is authorized to store information based on the table type defined by ‘t_emptbl’.  The above declarations can be used globally in the entire program, including sub-programs.

select * bulk collect into v_emptbl from emp;

This is the most important statement; it does all the operations.  If you remove BULK COLLECT, it would raise an error, because as you cannot work with more than one row at a time.  The BULK COLLECT lets you fetch any number of rows based on the SELECT statement issued, and store all of them in a TABLE typed variable.  From the above statement, it is quite clear that we are retrieving all rows from the table ‘emp’ and collecting them into the TABLE typed variable ‘v_emptbl’. 

Now the next issue is to fetch all those rows of values and display them back on the screen.  In general, each and every row in the TABLE typed variable is indexed starting from one (just like an array).  We retrieve each and every row with that index by using a FOR loop.

            procedure dispEmp(p_emptbl t_emptbl) is

         begin

             for i in p_emptbl.first .. p_emptbl.last

                 loop

                     dbms_output.put_line(p_emptbl(i).ename ||
                     ' earns ' || p_emptbl(i).sal);

                 end loop;

         end;

The above part is a procedure named ‘dispEmp’ containing a FOR loop, which starts from the first index of the TABLE typed variable ‘p_emptbl’ and ends at the last index of the same.  An entire table gets copied from the main program to the parameter ‘p_emptbl’ (which is actually TABLE type).  Here ‘first’ and ‘last’ are keywords which can be used with any TABLE typed variables.  We retrieve each and every field of information by using the notation TABLE(INDEX).FIELDNAME (which is implemented using DBMS_OUTPUT statement).



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