HomeOracle Page 4 - Database Interaction with PL/SQL, User-defined Packages
TYPE declarations in package specification - Oracle
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.
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.