 |
|
Oracle Tips by Burleson |
Using
PL/SQL Packages
A PL/SQL package is a grouping of related
procedures, functions, definitions, and cursors. PL/SQL packages
have these characteristics:
-
The code placed in a package can see and use the
other code inside the package.
-
A
cursor, variable, or type definition can be defined once and used
anywhere in the package.
-
Like the ADA
programming language, PL/SQL
packages have a specification and a body declaration.
-
The code objects, definitions, procedures and
functions defined in the package specification are accessible from
outside the package
-
The code objects defined in the package body,
but not in the specification, can not be seen or accessed outside
the package. This allows a developer to hide code that he does not
want the user to see.
A PL/SQL packages
specification follows the format shown in the example below.
SQL> create
or replace package myapp is
2 procedure update_book_date
3 (v_book_key in book.book_key%type,
4 v_pub_date in book.book_date_published%type);
5 function get_author
6 (v_book_key in book.book_key%type)
return varchar2;
7 end myapp;
8 /
Package
created.
The example specification above defines a package
called myapp that contains one procedure and one function.
Notice that the procedure and function body are not included. Only
the name and passed parameters (or the signature) is included in the
specification. The specification defines what is included the package
that is exposed for use outside the package. To call a package
object, include the package name using the dot (.) notation.
begin
myapp.update_book_date('B101',SYSDATE);
-- all the other suff
So where is the code for myapp’s objects?
The code is in the package body, which is a separate database object.
Any code object in the package specification is accessible from
outside of the package. Any additional code objects defined in the
package body (but not in the specification) are hidden and are not
accessible from outside the package. Any code object in the package
body can access any other code object in the package body.
In the example below, the procedure and function
specified in the previous example is defined on lines 9 through 28.
SQL> create
or replace package body myapp is
2
3 type number_var is varray(1000) of number;
4
5 cursor c1 is
6 select order_number,quantity
7 from sales;
8
9 procedure update_book_date
10 (v_book_key in book.book_key%type,
11 v_pub_date in
book.book_date_published%type)
12 is
13 begin
14 update book
15 set book_date_published = v_pub_date
16 where book_key = v_book_key;
17 end;
18
19 function get_author
20 (v_book_key in book.book_key%type)
return varchar2
21 is
22 auth_store author.author_last_name%type;
23 begin
24 select author_last_name into auth_store
25 from author join book_author
using (author_key)
26 where book_key = v_book_key;
27 return auth_store;
28 end;
29
30 end myapp;
31 /
Package body created.
Notice that the example defines a VARRAY type on
line 3 and a cursor on line 5. These objects can be used anywhere in
the package but can not be accessed outside the package. Since code
objects defined in the package can be shared, you do not have to
redefine them when used. Here is a partial listing of the package
body for the example package specification.
The entire listing is in the code depot.
SQL> create
or replace package body myapp is
2
3 type number_var is varray(1000) of number;
4 type string_var is varray(1000)
of varchar2(80);
5
6 cursor c1(v_key varchar2) is
7 select order_number,quantity
8 from sales where store_key = v_key;
9
10
11 function get_average_sales
12 (v_store_key in varchar2) return number
13 is
14 a_order string_var;
15 a_qty number_var;
16 n_avg number;
17 begin
18 open c1(v_store_key);
19 fetch c1 bulk collect into a_order, a_qty;
20 for indx in
a_order.first..a_order.last loop
21 n_avg := n_avg + a_qty(indx);
22 end loop;
23 return n_avg/a_order.count;
24 end;
25
procedure update_book_date
… -- code continues
Two VARRAYs are defined on lines 3 and 4 and they
are used in the function defined on lines 14 and 15. The function
does not have to redefine the types. The cursor defined on line 6 is
again used on line 18 without having to redefine it inside the
function.
We have been using packages throughout this book,
mostly the dbms_output package supplied by Oracle. We have
made extensive use of the put_line procedure inside of that
package, but that is not the only procedure in the package.
But how do you determine the code objects that a
package exposes in the package specification? Just like a database
table, you describe it:
SQL> desc
dbms_output
PROCEDURE
DISABLE
PROCEDURE
ENABLE
Argument Name Type In/Out
Default?
------------------------------ ----------------------- ------
--------
BUFFER_SIZE NUMBER(38) IN DEFAULT
PROCEDURE
GET_LINE
Argument Name Type In/Out
Default?
------------------------------ ----------------------- ------
-------- LINE VARCHAR2 OUT
STATUS NUMBER(38) OUT
PROCEDURE
GET_LINES
Argument Name Type In/Out
Default?
------------------------------ ----------------------- ------
--------
LINES TABLE OF VARCHAR2(32767) OUT
NUMLINES NUMBER(38) IN/OUT
PROCEDURE
GET_LINES
Argument Name Type In/Out
Default?
------------------------------ ----------------------- ------
--------
LINES DBMSOUTPUT_LINESARRAY OUT
NUMLINES NUMBER(38) IN/OUT
PROCEDURE
NEW_LINE
PROCEDURE PUT
Argument Name Type In/Out
Default?
------------------------------ ----------------------- ------
--------
A VARCHAR2 IN
PROCEDURE
PUT_LINE
Argument Name Type In/Out
Default?
------------------------------ ----------------------- ------
--------
A VARCHAR2 IN
There are eight procedures in the dbms_output
package. Notice that three of the procedures have the same name.
This is called overloading and in PL/SQL can only exist in a package.
Outside of a package a procedure or function is defined by a name. No
two objects can have the same name. But inside a package, objects are
defined by their signature, which consist of the name and the passed
parameters.
If you look closely at the three get_lines
procedures you will see that each one requires a different number
and/or type of parameters. Thus, the three procedure names are the
same but the three signatures are distinct. In the myapp
package defined in the previous example, there is a function that
returns the author’s last name when passed a book_key (the
author of the book). This same function can be used if passed an
author_key. To overload the function, simply define the new
function passing the new variables as in the partial listing below.
36
function get_author
37 (v_book_key in book.book_key%type)
return varchar2
38 is
39 auth_store author.author_last_name%type;
40 begin
41 select author_last_name into auth_store
42 from author join book_author
using (author_key)
43 where book_key = v_book_key;
44 return auth_store;
45 end;
46
47 function get_author
48 (v_auth_key in author.author_key%type)
return varchar2
49 is
50 auth_store author.author_last_name%type;
51 begin
52 select author_last_name into auth_store
53 from author
54 where author_key = v_auth_key;
55 return auth_store;
56 end;
57 end myapp;
58 /
Package body created.
The function get_author is now overloaded
inside the myapp package. Before anyone can access the
overloaded function, it must be added to the specification.
SQL> create
or replace package myapp is
2 procedure update_book_date
3 (v_book_key in book.book_key%type,
4 v_pub_date in
book.book_date_published%type);
5 function get_author
6 (v_book_key in book.book_key%type)
return varchar2;
7 function get_author
8 (v_auth_key in author.author_key%type)
9 return varchar2;
10 end myapp;
11 /
The new function can now be accessed from outside
the package. When packages are used, it is important to use
descriptive names for passed parameters. When the package is
described, these names are exposed.
SQL> desc
myapp
FUNCTION
GET_AUTHOR RETURNS VARCHAR2
Argument Name Type In/Out
Default?
------------------------------ ----------------------- ------
-------- V_BOOK_KEY VARCHAR2(6) IN
FUNCTION
GET_AUTHOR RETURNS VARCHAR2
Argument Name Type In/Out
Default?
------------------------------ ----------------------- ------
--------
V_AUTH_KEY VARCHAR2(11) IN
PROCEDURE
UPDATE_BOOK_DATE
Argument Name Type In/Out
Default?
------------------------------ ----------------------- ------
-------- V_BOOK_KEY VARCHAR2(6) IN
V_PUB_DATE DATE IN
Many times the example above is all a user who is
attempting to use this package will see. A descriptive parameter name
is important in helping the user know what variable to pass as a
parameter. Notice that the %type parameter datatypes
are displayed as the actual variable’s datatype (varchar2(x) in this
example), so the name becomes important in displaying to the
programmer the makeup of the parameters.
So, when should you use packages? Always! You
should write the application code in packages. Small applications can
be placed in a single package. It is easier to write the code in a
package than to write and test it as a stand alone object and then
move it into a package. Code written outside of packages and then
moved into packages rarely takes advantage of the package’s code,
definition sharing, and overloading. Also, each object will have to
be tested and validated again once it is in the package.
Using packages will also allow the database to
more efficiently process the code. When the first code object in a
package is called, the database loads the entire package into memory.
So by placing the application into packages, the code will be loaded
and maintained in memory, resulting in faster execution and more
efficient use of the database resources. In fact, the DBA can pin the
package into the library cache on database startup so that it is
loaded even before the first user logs on.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
