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.

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

{mospagebreak title=Returning PL/SQL TABLE from a FUNCTION to a PROCEDURE}

In the previous section of this article, I used a PL/SQL TABLE as a parameter for the PROCEDURE.  Now, I shall revise the above stated program in such a way that I will use both FUNCTION and PROCEDURE together, communicating with PL/SQL TABLEs.  Let us consider the following example:

declare

    type t_emptbl is table of emp%rowtype;
    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;

    function getEmployeeList return t_emptbl is

        v_emptbl          t_emptbl;                    

        begin

            select * bulk collect into v_emptbl from emp;

            return v_emptbl;

        end;

     BEGIN

        dispEmp(getEmployeeList);

     END;

 

Let us examine the two sub-programs separately.

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;

It just receives PL/SQL TABLE as parameter and displays all the information available in it.

function getEmployeeList return t_emptbl is

         v_emptbl          t_emptbl;                     

    begin

         select * bulk collect into v_emptbl from emp;
         return v_emptbl;

     end;

The above function ‘getEmployeeList’ executes the SELECT statement and the output is gathered into ‘v_emptbl’, which is returned back (or pushed out).

dispEmp(getEmployeeList);

The above is the most important statement in the program.  It looks very simple, but a lot of logic is embedded in it.  First of all, it executes the FUNCTION ‘getEmployeeList’.  That FUNCTION returns a PL/SQL TABLE, which will be sent as a parameter to the procedure ‘dispEmp’ automatically.  We need not actually store the PL/SQL TABLE returned by the FUNCTION.

{mospagebreak title=What are IN types of parameters?}

Let us consider the following program:

declare

   procedure dispValue(a number) is

        b          number;

   begin   

        a := a + 10;
       
b := a * a;
       
dbms_output.put_line(‘Value : ‘ || b);

    end;

begin

    dispValue(10);

end;

/

Is the above program syntactically wrong?  Is it logically wrong?  But, the program raises an error!  The error message would be “expression ‘A’ cannot be used as an assignment target.”  Try to remove the statement “a := a + 10” and execute the program again.  No doubt, it gets executed successfully.  What is the problem?

Actually, we can rewrite the same program with the specification of IN as the following (which is actually the default):

declare

    procedure dispValue(a IN number) is

          b          number;

    begin   

          b := a * a;
         
dbms_output.put_line(‘Value : ‘ || b);

    end;

begin

    dispValue(10);

end;

If you don’t specify anything to the parameter, IN becomes the default behind the screens.  That means it just behaves like an “IN”PUT to the procedure.  But it could not be modified within the sub-program.  The value present in a variable which is defined with “IN” can never be modified within the sub-program.  Now I hope you can understand why you received the error.  It not only affects ordinary types of variables, it could even be related to %TYPE or %ROWTYPE or RECORD or even TABLE.

That is why, to remove the confusion, it is always suggested that you use the type of parameter along with the parameter which could be either IN or OUT or even both IN OUT together (dealt with later).

{mospagebreak title=What are OUT types of parameters?}

This is a bit different (of course quite opposite) from the case we just finished discussing.  The IN parameter could never be modified (but only accessed).  The OUT parameter could never be accessed (but modified and indirectly returned back).  Let us consider the following example:

declare

    b          number;

    procedure doSquare(a IN number,
             
z OUT number) is
   
begin   

          z := a * a;

    end;
begin
   
doSquare(10,b);
   
dbms_output.put_line(‘Square : ‘ || b);
end;

/

Even though the above is a very simple program, it gives us a lot to discuss. First of all, you should observe that ‘b’ never gets initialized within the program.  It was just declared as being of type number and we are directly displaying it.  And we received a value of 100.  So, before displaying the value of ‘b’, we are calling a procedure named ‘doSquare’, which performs all of the magic.

The statement ‘doSquare(10,b)’ calls the procedure ‘doSquare’ by sending 10 into the parameter ‘a’.  Just like 10, we are passing ‘b’ to ‘z’ (but as being of type OUT parameter).  Since ‘z’ is an OUT type of parameter, it will not expect any value from ‘b’.  Instead, it in turn reflects back its own value (value of ‘z’) to ‘b’.  That means the parameter ‘z’ returns back its own value directly into ‘b’.  It is another way of returning a value from a procedure (but indirectly, because we cannot use a RETURN statement with a procedure).  So, within the procedure ‘doSquare’, whenever ‘z’ gets modified, the variable ‘b’ also gets modified with the same value, and thus ‘z’ is called an OUT type of parameter.

{mospagebreak title=What is an IN OUT type of parameter?}

The IN OUT type of parameter is special type of parameter which can be accessed and modified.  Both operations can be done using an IN OUT parameter.  We can rewrite the above program as follows with the concept of an IN OUT parameter:

declare

   b          number := 10;
   
procedure doSquare(z IN OUT number) is
   
begin

      z := z * z;

   end;

begin

    doSquare(b);
   
dbms_output.put_line(‘Square : ‘ || b);
end;

/

The above program is completely different from all of the earlier programs.  Let us examine the life of the variable of ‘b’ in the above program.  We can conclude something like the following:

‘b’ is declared as of type number.

‘b’ is immediately initialized to 10.

‘doSquare’ method is called by passing the value of ‘b’ (which is 10 at this moment).

‘z’ receives 10 (which has got permission for both access and modify).

‘z’ gets updated to ‘z * z’ (which is 100 at this moment).

As ‘z’ got modified, automatically ‘b’ in main program gets modified (at this moment ‘b’ is 100 as well).

We are displaying back the value in ‘b’, which is 100.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye