Database Interaction with PL/SQL, User-defined Packages

This article is part of a series focusing on database interactions with Oracle PL/SQL. In my previous article, we examined named notation, default values of parameters, stored procedures, stored functions and finally took our first look at package and package body. In this article, we will focus completely on package and package body. Before reading this article I suggest you to go through my last three articles in this series thoroughly.

Please note that all 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 for the respective version you are using, if any of the programs failed to execute.

Implementing a PACKAGE

In this section, we will look into the implementation of package in Oracle database. First of all, let us have a very simple example as follows:

create or replace package SamplePkg as
    procedure dispSquare (a IN number);
    procedure dispCube (a IN number);
end SamplePkg;
/

The above code actually creates only a package specification (not the body yet).  Consider the following code:

create or replace package body SamplePkg as

    procedure dispSquare(a IN number) is
    begin
        dbms_output.put_line (‘Square = ‘ || (a*a) );
    end;

    procedure dispCube(a IN number) is
    begin
        dbms_output.put_line (‘Cube = ‘ || (a*a*a));
    end;
end SamplePkg;
/

The above code creates the package body for the previous package specification.  All the members in the package body should match with all the declarations within the package specification.  Make sure that both package specification and package body gets stored in the database.

Now the question is, how do we execute a package?  If it is a stored procedure, we just execute by using an ‘execute’ command (part 16) followed by the procedure name.  Even here, the concept is almost similar. The only issue is that we need to use the command ‘execute’ followed by the “packagename.sub-programname” convention.  The following is a demonstration for the above package to execute from SQL prompt:

Execute SamplePkg.dispSquare(10);
Execute SamplePkg.dispCube(10);

{mospagebreak title=Database interaction using a PACKAGE}

There would be nothing new in this section apart from the syntax of package.  All the procedures and functions being implemented inside the package have been thoroughly discussed in my previous articles.  So, let us have a good example of using a package interacting with a database.

create or replace package SamplePkg as
    procedure dispEmp;
    procedure dispDept;
end SamplePkg;
/

create or replace package body SamplePkg as

    procedure dispEmp as
        cursor c_emp is
            select ename, sal from emp;
    begin
        for r_emp in c_emp
        loop
            dbms_output.put_line (r_emp.ename || ‘,’ || r_emp.sal);
        end loop;
    end;

    procedure dispDept as
        cursor c_dept is
            select deptno,dname from dept;
    begin
        for r_dept in c_dept
            loop
                dbms_output.put_line (r_dept.deptno || ‘,’ ||
                r_dept.dname);
            end loop;
    end;

end SamplePkg;
/

To execute each of those procedures separately, you can use the following commands:

execute SamplePkg.dispEmp;
execute SamplePkg.dispDept;

{mospagebreak title=Overloading sub-programs in a PACKAGE}

Those who are familiar with Object Oriented Programming (OOP) can easily understand the concept of overloading.  Briefly, having the same name for different sub-programs with different parameters can be called overloading sub-programs.  The following example gives you an illustration:

create or replace package SamplePkg as
    procedure dispEmp;
    procedure dispEmp(p_deptno dept.deptno%type);
end SamplePkg;
/

create or replace package body SamplePkg as
     procedure dispEmp as
         cursor c_emp is
             select ename, sal from emp;
     begin
         for r_emp in c_emp
         loop
             dbms_output.put_line (r_emp.ename || ‘,’ || r_emp.sal);
         end loop;
      end;

      procedure dispEmp(p_deptno dept.deptno%type) as
          cursor c_emp is
              select ename, sal from emp
              where deptno = p_deptno;
      begin
          for r_emp in c_emp
          loop
          dbms_output.put_line (r_emp.ename || ‘,’ || r_emp.sal);
          end loop;
      end;

end SamplePkg;
/

Within the above package, we declared two sub-programs as having the same name.  But you should observe that the first sub-program doesn’t have any parameters, whereas the second sub-program does.  If you execute with the following statement:

Execute SamplePkg.dispEmp;

It would automatically call the first sub-program (because you didn’t provide any parameters).  If you execute with the following statement:

Execute SamplePkg.dispEmp(10);

It would automatically choose the second sub-program (because we provided a parameter).

So, the selection of the respective sub-program will be automatically chosen by PL/SQL runtime based on the parameters we send.  In that way, we can write as many sub-programs as possible with the same name, but with some differences in parameters.

{mospagebreak title=TYPE declarations in package specification}

A package specification can not only contain sub-programs, it can even contain TYPE declarations as well.  What is the use of declaring TYPE declarations at the level of package specification?  Why don’t we declare the same within the sub-programs (as we usually do)?

If we declare any TYPE declaration within the package specification, it will be available to the public.  The word ‘public’ has several meanings here.  Any data structure declared in the specification of a package is a global, public data structure. This means that any program outside of the package can access the data structure. Let us see some of the issues of “public” declarations within package specification.

  • A public declaration is available to every sub-program within the same package.
  • It also acts like a stored data type (indirectly).
  • Other stored procedures (or even stored functions) can use the same TYPE any number of times.
  • PL/SQL programs can also use those types declared as public.
  • Apart from all of the above, even external sources can identify these public types and follow accordingly.

This makes life easier in almost all aspects without remembering and re-declaring the same TYPE again and again.  You can also create a package of constants which are used throughout all of your programs. Then all developers will reference the packaged constants instead of hardcoding the values into their programs. It also proves the concept of single definition at single location, reused several  times at several locations.

Let’s see an example for the above theory:

create or replace package SamplePkg as
    TYPE t_emprec is RECORD
    (
        name    emp.ename%TYPE,
        salary  emp.sal%type
     );
     procedure dispEmp;
     procedure dispEmp(p_deptno dept.deptno%type);
end SamplePkg;
/

create or replace package body SamplePkg as
    procedure dispEmp as
        cursor c_emp is
            select ename, sal from emp;
        r_emp t_emprec;
    begin
        open c_emp;
        loop
            fetch c_emp into r_emp;
            exit when c_emp%notfound;
            dbms_output.put_line(r_emp.name || ‘,’ || r_emp.salary);
        end loop;
        close c_emp;
     end;

     procedure dispEmp(p_deptno dept.deptno%type) as
         cursor c_emp is
             select ename, sal from emp
             where deptno = p_deptno;
         r_emp t_emprec;
     begin
         open c_emp;
         loop
             fetch c_emp into r_emp;
             exit when c_emp%notfound;
             dbms_output.put_line (r_emp.name || ‘,’ || r_emp.salary);          
          end loop;
          close c_emp;
      end;

end SamplePkg;
/

My next section would help you to understand the above package.

{mospagebreak title=How the above package works}

I gave a lot of theory and a large example in the previous section.  Let me explain the above package bit by bit.  So, first of all consider the following bit:

TYPE t_emprec is RECORD
    (
        name    emp.ename%TYPE,
        salary  emp.sal%type
     );

I declared a RECORD type named ‘t_emprec’ within the package specification.  That means it can be used in every sub-program within that package (the minimum consideration).

 procedure dispEmp;
 procedure dispEmp(p_deptno dept.deptno%type);

The above two are overloaded sub-programs.  One sub-program has a single parameter and the other does not have any parameters.  That’s the end of the package specification.  Now, let us see about the package body.

procedure dispEmp as
         cursor c_emp is
              select ename, sal from emp;
          r_emp t_emprec;
      begin
          open c_emp;
          loop
              fetch c_emp into r_emp;
              exit when c_emp%notfound;
              dbms_output.put_line(r_emp.name || ‘,’ ||   r_emp.salary);          
          end loop;
          close c_emp;
       end;

The above procedure is very similar to the one given in the third section of the article.  It just displays all employee names and salaries.  The only difference is that it fetches the information into a record based variable of type ‘t_emprec’ which was declared in the package specification.

procedure dispEmp(p_deptno dept.deptno%type) as
         cursor c_emp is
             select ename, sal from emp
             where deptno = p_deptno;
         r_emp t_emprec;
    begin
         open c_emp;
         loop
              fetch c_emp into r_emp;
              exit when c_emp%notfound;
              dbms_output.put_line (r_emp.name || ‘,’ || r_emp.salary);          
          end loop;
          close c_emp;
    end;

I hope you can understand the above sub-program as it is quite similar except in the case of parameter declaration.

Other extensions to package development

One more point to understand is that you can also have some declarations within the package body straight away.  That means you can have the declarations at the top of package body (without having any relation to the sub-programs).  Once you have such declarations, those are called private declarations within the package.  That means you can use all of those declarations in each of the sub-programs available. 

Such types of package body level variable declarations are generally used to implement security within the package context (hiding it from outside world).

Even though I didn’t use any functions in any of the above packages, it doesn’t mean that you shouldn’t use them.  You can use them as freely as you wish.  And another great feature is that you can call another sub-program within the same sub-program without specifying the package name.  Of course you are also allowed to call the sub-programs which are outside the package as well.

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

chat