Monday, 22 February 2016

GLOBAL VARIABLES

Global Variables:
It is a variable which we need to declare in the package specification so that we can access these variables in the package body also.

Example:
Create or Replace Package mypack
g number:=5;
Function fun(a in number)
Return number;
End;

Create or Replace Package Body mypack
IS
Function fun(a in number)
Return number
IS
C number;
Begin
C:=a+g;
Return C;
End fun;
End;

NOTE:  If we want to maintain the state of the global variable we need to use the 
              'PRAGMA  SERIALLY_REUSABLE'

EXAMPLE USING PRAGMA SERIALLY_REUSABLE:
Create or Replace Package mypack
IS
g  number:=5;
Pragma serially_reusable;
End;

 The variable g is used in below Anonymous Block
Anonymous block :
Declare
f number:=1;
Begin
for i in 1..mypack.g 
loop
f:=f*i;
End loop;
Dbms_output.put_line("The factorial of the given number is'||f);
End;

State of Global Cursor:
Example :
Create or Replace Package p
IS
Cursor c is select  * from emp;
Pragma  serially_Reusable;
End;

The Above declared  cursor is used in below anaonymous block
Anonymous Block:
Begin
For I in p.c
loop
Dbms_output.Put_Line(I.empno||' '||I.ename||' '||I.deptno||'  '||I.sal);
End loop;
End;


No comments:

Post a Comment

If you Like my blog Spread it and help friends for whom this blog is useful for their career.