Packages:-
· It is a container/program unit area which is useful to store
related things at one place.
· It provides modularity, scalability, encapsulation, data
security, portability, code analysation, debugging the code, tracing the code,
profiling the code, location monitoring and so on…
Alters/decreases the redundancy.
Note:- Packages
won’t allow parameters, nesting, and calling.
Packages consist of two
parts:
1.
Package specification(PS)
2.
Package body
Package specification:-
·
It is a prototype for package body program.
· In specification we declare variables, cursors, exceptions,
procedures, functions and so on…
·
This is for information purpose.
·
It can exist without body.
·
Declaring cursor variables is not possible; defining ref cursor
data type is possible.
·
A package body can’t exist without package specification but
reverse is not so…
·
Package specification and body stores in different memory areas.
·
PL /SQL objects defined in package specifications are considered
as global objects, won’t allow coding part.
Package body:-
·
It consists of program coding.
·
A package body can optionally has executable section.
· Variables and programs which are defined in package body without
specifying I package specification are called as local variables, local
programs.
· We can drop a package body without dropping package
specification
o Package
specification drops.
Syntax for package
specification:-
Create or replace package is variables,
cursor,exceptions,datatypes……
Declaring
procedure,functions…….
End packagename;
Pragma Autonomy Transaction
(Tx):-
It is an independent Tx happens individually irrespective of
parent Tx.
Limitations:-
·
Package specification won’t allow pragma; we can apply for
packeged procedures and packages.
Create or replace
procedure p is pragma autonomius_transaction;
Begin
Insert into nestab
values(10);
End p;
Create table
nestab(sno number(5));
Select * form nestab;
Calling environment:
Begin
Insert into nestab
values(11);
P;
Insert into nestab
values(12);
Rollback;
End;
Output:
10
·
While using pragma autonomus Tx we need to mention commit,
rollback as mandatory.
·
DDl- autocommit commands
·
DML- non-autocommit.
Create
or replace procedure p is pragma autonomius_transaction;
Begin
Insert
into nestab values(10);
Rollback;
End
p;
Begin
Insert
into nestab values(11);
P;
Insert
into nestab values(12);
Commit;
End;
Select
* from nestab;
Out put:-
11
10
12
Create
or replace procedure p is
Begin
insert into nestab values(10);
Rollback;
End
p;
Begin
insert into nestab values(11);
P;
Insert
into nestab values(12);
Commit;’
End
p;
Out put:
12
Note:- We avoiding mutating
error using pragma.atonomous_transaction.
Pragma inline:- (11g)
·
Which is used to including the programs(11g).
·
Pragma inlining will enhance the performance.
Deff:
In
lining a program means replacing the procedure call with actual executable code
copy of a program.
Note:-
Programs
which are having static code and frequently used programs (or) subject to the
pragma inline(preferrable).
Syntax:-
Pragma
inline(‘procedure name’,{‘yes/no’});
Declare
Stime
integer;
Etime
integer;
V
number;
Function
f(x number) return number is
Begin
Return
x;
End
f;
Begin
Pragma
inline(‘f’,’yes’);
Stime
:=dbms_utility.get_time;
For
I in 1..10000 loop
V:=f(i);
End
loop;
Etime:=dbms_utility.get_time;
DOPL(etime-stime);
Pragma
inline(‘f’,’no’);
Stime:=dbms_utility.get_time;
For
I in 1..10000 loop
V:=f(i);
End
loop;
Etime:=dbms_utility.get_time;
DOPL(etime-stime);
End;
Packages:-
·
It is a container/program unit area which is useful to store
related things at one place.
·
It provides modularity, scalability, encapsulation, data
security, portability, code analysation, debugging the code, tracing the code,
profiling the code, location monitoring and so on…
·
Alters/decreases the redundancy.
Note:- Packages won’t allow
parameters, nesting, and calling.
Some built-in packages:-
Dbms-lob-handles lob
related values // cannot
call package in package
Dbms-fga(11g)-handles
fine grain editing. //no
parameters
Dbms-lock-to provide
locks and latches
Dbms-describe-display
the information regarding overload, level and arguments
Dbms-profile-profiling
the code
Dbms-monitor-for
application monitoring
Dbms-sql-to handle DDl
commands in PL/SQL
Dbms-job-for defining
and scheduling the jobs
Dbms-DDL
Dbms-transaction-to
handle the transactions
Dbms-trace(11g)-tracing
the code
Dbms-hprof(11g)-for
hierarchical profiling
Dbmssession-session
related data
Dbms-metadata-handles
the metadata(data to the data)
Utl-file-for file
handling
Dbms-utility-for
miscellaneous
Dbms-rls-for row level
security
Dbms-dependency-handles
dependency objects
Dbms-revalidation-
Utl-tcp-
Dbms-output-
Dbms-result-cache
(11g)-for data caching
Dbms-pipe-handles
inter related session
dbms-sechdule-
dbms-warning-
dbms-debug-
Packages consist of two
parts:
·
Package specification(PS)
·
Package body
Package specification:-
·
It is a prototype for package body program.
·
In specification we declare variables, cursors, exceptions,
procedures, functions and so on…
·
This is for information purpose.
·
It can exist without body.
·
Declaring cursor variables is not possible; defining ref cursor
data type is possible.
·
A package body can’t exist without package specification but
reverse is not so…
·
Package specification and body stores in different memory areas.
·
PL /SQL objects defined in package specifications are considered
as global objects, won’t allow coding part.
Package body:-
·
It consists of program coding.
·
A package body can optionally has executable section.
·
Variables and programs which are defined in package body without
specifying I package specification are called as local variables, local
programs.
·
We can drop a package body without dropping package
specification
·
Package specification drops.
Syntax for package specification:-
Create or replace
package is variables, cursor,exceptions,datatypes……
Declaring procedure,
functions…….
End packagename;
Sysntax for package body:-
Create or replace package body is
variables, cursor….
Defining subprograms
Optional executable
section;
End package_name;
Eg:
Crate
or replace package pack is v number(5):=400;
Ex
exception ;
Cursor
c is select * from emp;
Procedure
p(X inout number);
Function
f(y in out number);
Return
number;
End
pack;
Create or replace package body pack is L number(5):=500;
Function lf (Z in number )return number is lv number(5);
Begin
Lv:=Z+L;
Return lv;
End lf;
Procedure p (X inout
number)is pv number(5);
Begin
Pv:=2000+lf(X);
X:=pv+5000;
End p;
Function f(Y inout
number)return number is
Begin
Y:=Y+lf(v);
V:=Y+L;
Return v;
End f;
If you want to define package body first we need to define
package specification.
SQL*PLUS:
SQL> var a number;
Exec
:a:=1000;
Exec pack.p(:a);
Print
a;
BLOCKS:
Declare
a
number(5):=2000;
begin
pack.p(a);
DOPL(a);
End;
FUNCTIONS:
SQL*PLUS
Var a
number;
Var b
number;
Exec
:b:=4000;
Exec
:a:=pack.f(:b);
BLOCKS:
Declare
A
number(5);
B
number(5):=1000;
Begin
A:=pack.f(:b);
DOPL(a||’
‘||b);
End;
Example for Packaged Crusors:
declare
vrow emp%rowtype;
begin
open pack.c;
loop
fetch pack.c into vrow;
DOPL(vrow.ename);
If vrow.ename=’scott’ then
Raise pack.ex;
End if;
End loop;
Exception
When pack.ex then
DOPL(‘packex’)
End;
Declare
Vrow emp%rowtype;
Begin
Fetch pack.c into vrow;
DOPL(vrow.ename);
End;
Out Put: Ford
NOTE:-
·
If you won’t close the packaged (pack’s) cursor in any program
then that is last for entire session.
·
We can call the packaged procedures and functions outside of
package which are specified in package specification(global accessing).
·
Attempting to call packaged local sub programs to call outside
of package throws an error.
Using cursor variable as a
parameter value:
Create
or replace package pack is type rec is ref cursor;
Procedure
p(X rec);
End
pack;
Create
or replace package body pack is Procedure p(X rec)is vrow emp%rowtype;
Begin
Fetch
X into vrow ;
DOPL(vrow.ename);
Exit
when X%not found;
End
loop;
End
p;
End
pack;
BLOCK:
Declare
Type
recl is ref cursor;
Vrec
recl;
Begin
Open
vrec for select * from emp;
Pack.p(vrec); //calling
End;
Note: Defining cursor
variable in package specification will not be allowed but defining ref cursor
is possible.
We can use cursor variable as an ‘out’ parameter:
Create or replace package p(X out sys_refcursor) is vrec
sys_refcursor;
Begin
Open vrec for select * from emp;
X:=vrec;
End p;
SQL*PLUS:
Var v refcursor;
Exec p(:v);
Using
cursor variable in return statement of a function:
Create or replace function f return sys_refcursor is vrec sys_refcursor;
Begin
Open vrec for select * from emp,dept;
Return vrec;
End f;
Exec :v:=f;
Print v;
Note: If procedure and functions is present in specification then we
go for package body otherwise no need.
Packaged Cursors:
Create or replace package pack is cursor c return emp%rowtype is
select * from emp;
End pack;
Here we are defining
‘cursor’ in package specification we can use this anywhere.
Begin
For i in pack.c
DOPL(i.ename);
End loop ;
End;
We can hide the cursor select statement through package concept as
shown in the snippent:
Create or replace package pack is cursor c return emp%rowtype ;
End pack;
Create or replace package body pack is cursor c return
emp%rowtype is select * from emp;
End pack;
Begin
For i in pack.c
DOPL(i.ename);
End loop ;
End;
Out Put:
Emp 14 records
A package body can
optionally contain executable section. Which is one time initialization but it
should be at last in the package body if we have any sub programs.
Create or replace package pack is v number (5);
End pack;
Create or replace package body pack is
Begin
V: =5000;
End pack;
Note:-
We can also call the
packaged function in select statement but that should not contain out and inout parameter.
Drop
package
·
Here along with package specification, body also will drop.
·
Dropping packaged body without dropping package specification
Drop
package body pack;
No comments:
Post a Comment