A simple example
package specification
SQL> create or replace package calculator
2 as
3 procedure addition(n1 number,n2 number,add OUT number);
4 procedure mul(no1 number,no2 number,product OUT number);
5 end calculator;
6 /
Package created.
package body
SQL> create or replace package body calculator
2 is
3 Procedure addition(n1 number,n2 number,add OUT number)
4 as
5 begin
6 add := n1+n2;
7 dbms_output.put_line(add);
8 end addition;
9 Procedure mul(no1 number,no2 number,product OUT number)
10 as
11 begin
12 product := no1*no2;
13 dbms_output.put_line(product);
14 end mul;
15 end calculator;
16 /
Package body created.
SQL> var jose number;
SQL> exec calculator.addition(23,6,:jose);
29
PL/SQL procedure successfully completed.
Removing packages
Drop package package_name;
Drop package body package_name;
Bodiless package
SQL> CREATE OR REPLACE PACKAGE CONSTANTS
2 IS
3 PI NUMBER := 3.14;
4 END CONSTANTS;
5 /
SQL> EXEC DBMS_OUTPUT.PUT_LINE(CONSTANTS.PI);
3.14
To execute a package memeber
EXEC PACKAGE_NAME.function()[or procedure() or constant]
Method overloading possible inside package
1 create procedure B(a number)
2 is
3 begin
4 dbms_output.put_line(a);
5* end;
SQL> /
Procedure created.
SQL> create procedure B(a number,b number)
2 is
3 begin
4 dbms_output.put_line(a||b);
5 end;
6 /
create procedure B(a number,b number)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
But by using packages we can overload the above procedures...
Misc
SQL> create package pp
2 as
3 procedure pop(v varchar2);
4 procedure pop(x varchar2);
5 end;
6 /
Package created.
To execute above use
exec pp.pop(x=>’kar’);
exec.pp.pop(v=>’sel’):
One time only procedure
It has no package body with in the package specification itself procedure definition is coded...Refer book
Advantages:
1.Security (Granting privileges)
2.Global access (Sharable objects)
3.Modularity (Grouping related objects)
4.Min disk space
points to remember:
• The variables that are declared inside package specification are called public scope variables and those with in package body are called private.
• Variables declared inside the package specification have NULL value if not initialized.
• The public variables can be used or re initialized inside a package or in the exec statement.
• We can invoke a function from a procedure inside the same package just by calling its name and appropriate parameter passing.
• Package within another package not allowed.
• Cursor variables cannot be declared inside package specification it can be used only inside pl-sql block.
Pragma restrict_references(funname,wnds,wnps,rnds,rnps)
