Oracle - using the package
I have read several PL SQL programming books and they recommend that I group procedures, functions, cursors, etc. in the package. Packages provide modularity and information hiding, which is OO design. However, I am simply familiar with the individual procedures. Would anyone kindly provide some code examples and how to call a package from the client? I am currently using ODP.NET as data access in a client application. Thank.
source to share
You put code for procedures, functions, etc. to the package body along with the declarations for the private variables. You put the declarations of these procedures and functions, as well as the public variables in the package. Only the latter are available from outside the package, and for that you need to prefix their names with the package name and period.
You can also define data types for this package: if public procedures use them as inputs or outputs, they must be made public; if not, then you can choose.
The example code is from memory and not tested, so it may contain errors:
create or replace package foo as
a number;
function test1(s1 in varchar2) return varchar2;
procedure test2(i1 in integer);
end;
/
create or replace package body foo as
b number; -- internal only
function internalfunc(s in varchar2) return varchar2;
function test1(s1 in varchar2) return varchar2 is
s varchar2(32000);
-- variables ...
begin
-- code ...
return internalfunc(s);
end;
procedure test2(i1 in integer) is
-- variables ...
begin
-- code ...
end;
function internalfunc(s in varchar2) return varchar2 is
begin
return INITCAP(LOWER(s));
end;
end;
/
Use foo.a
, foo.test1
etc. to use these functions and variables. You cannot access internal functions from outside the package. They only need to be declared at the beginning of the package body if called higher than their implementation.
Packages have an additional advantage over regular functions and procedures: you can overload their names, have several different procedures with the same name, but with different types of parameters in the same package, and the corresponding function will be called depending on what types of parameters you are using. In contrast, the name of a stand-alone function or procedure must be unique.
NTN.
source to share
To track, you can have a separate one, for example
create or replace procedure foo (i_something in varchar2) as
begin
-- do some stuff;
end foo;
which you call with "foo (" bar ");"
This will become a package and package body as
create or replace package my_package as
procedure foo (i_something in varchar2);
end;
create or replace package body my_package as
procedure foo (i_something in varchar2);
begin
-- do some stuff;
end foo;
end my_package;
which you call with "my_package.foo ('bar');"
For a single procedure, using a package may not provide more clarity. But if you're dealing with a lot of functions and procedures, this is much cleaner.
source to share
It is still good practice to use packages instead of simply declaring globally scoped functions.
If you have packages, it can provide extensibility if you want to add functions with the same context later, and it can make your procedures clearer as it specifies which library (package) the function is called from.
source to share