STORED PROCEDURE / (NAMED
BLOCKS)
·
They are the stored programs which get stored in DB as a DB
object.
·
Re-usability is possible in terms reduces the redundancy. (avoids
code repetetion)
·
Primarily (initially) we have two types of stored procedures.
1. Application
stored procedures: They are application specific.
2. Database
stored procedure:
1) Procedures
2) Functions
3) Triggers
4) Packages
Mostly we use these and cursors,loops and also TCL commands.
Usages of stored procedures:-
i.
Portability
ii.
Security/data hiding
iii.
Encapsulation
iv.
Modularity
v.
Scalability
vi.
Easy maintenance
vii.
Enhancing performance
viii.
Makes application design easy
ix.
Monitoring application
Tables regarding with stored procedure:-
1) User-arguments
2) User-dependencies
3) User-object
4) User-source
5) User-trigger
6) User-procedures
Procedure:-
It is
program unit (or) PL/SQL block which takes the parameters.
In the
procedure we attach header to the PL/SQL block.
Procedure
is a program unit which is used to execute statements.
It acts as
procedural statements and we use it for statements executions (more often).
It is a
stored program unit.
A procedure has to be four sections:-
They are:
1) Header
section
2) Declarative
section
3) Execution
section
4) Exception
section
Syntax:-
create[or replace] procedure (Para)[authid_user
definer/current_user] is
create[or replace] procedure
begin
-----------------------
Exception
-----------------------
End [procedure name];
Note: -In the above syntax “is” represents
standard alone procedure.
Calling procedure:-
SQL/Plus
SQL
developer (new tool)
Blocks
Unix
Front end
applications (apps)
Web
applications
Parameters:-
Basically parameters
of 3 types ate:
1. IN (read)
2. OUT (write)
3. INOUT (read
and write)
Procedure without any parameters:-
create or replace
procedure p is
begin
DOPL(‘hello’);
end p;
procedure
created
Desc p;
Procedure p
Calling
in SQL*plus:-
Exec p;
Calling
in blocks:-
begin
P;
end;
procedure with ‘IN’ parameter:-
Create or replace procedure
p(X in number) is V number (5);
begin
v:=X+5000;
DOCL (V);
End;
Note: Number (5) is invalid
because if you define size it will throw error.
SQl*Plus:-
Calling
environment:
Var a number;
We
will declare this variable in executable section.
Exec:a:=1000;
Here
assigning value to variable(a), from this variable(a) to I am sending value
to’X’.
Exec p(:a);
Here calling the procedure.
declare
a number:=&n;(calling in blocks)
begin
p(a); (this is calling
environment)
end;
Note:-
x number;
x In number
above both two
statements are same and ‘In’ is default parameter.
Declare
vname
varchar2(10):=&n;
a number(5);
begin
select sal into a from
emp where ename=vname;
p(a);
end;
create
or replace procdure p(X number, Y number) is vname varchar2(10);
begin
select ename into
vname from emp where empno=X and sal=Y;
DOPL(‘employee
name’||vname);
end;/
-procedure created.
declare
Vno number (5):=&n;
Vsal number (5):=&m;
begin
p(vno,vsal);
end;
SQL/Plus:- Calling environment
var a
number;
var b
number;
exec
:a:=7839;
exec
:b:=5000;
exec
p(:a,:b);
-----------------------------------------------------------------------------------------------------------------
procedure
with ‘OUT’ parameters:-
Create or
replace procedure p(X in number,Y out varchar) is
begin
select
ename into Y from emp where empno=x;
end;
SQL/Plus:-
var a number;
var b varchar2(10);
exec :a:=7839;
exec p(:a,:b);
print b;
exec DOCL(:b);
blocks:-
declare
a number(5):=7839;
b varchar2(10);
begin
p(a,b);
DOCL(b);
end;
Spool
concept:-
Sql> spool c:/gv.sql;
Sql> select * from emp;
Sql> spool off;
Sql> spool c:/gv.sql append ; (it will append to previous one)
Procedure
with ‘IN-OUT’ parameters:-
>
create or replace procedure p(X in out number) is
begin
select
sal into X from emp where empno=x
end p;
SQL *
Plus:-
var a number;
exec :a:=7788;
exec p(:a);
print a;
Blocks:-
declare
a number(5):=&n;
begin
p(a);
DOPL(a);
End;
While
creating time we will declare (arguments/parameters eg: X in number) we will
call that as “formal parameters”.
EX: exec
p(:a,:b) these are “actual parameters”.
We will refer
the values from actual to formal in three notations:
1. Positional
notation; (based on position value will be referred).
2. Named
notation; (by using names (formal parameters) we can implies the value)
3. Mixed
notation.
create or replace procedure p(X number, Y number, Z number) is
begin
DOCL(X||’ ‘||Y||’ ‘||Z);
end;
/
-Procedure created.
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Positional
notations:-
Exec p(100,200,300);
Named
notation:-
Exec
p(Z=>300, Y=>200, X=>500);
Mixed
notation:-
Note:- In mixed notation, named notation has to follow positional ` notation.P(100,200,Z=>300);
P(100,Y=>200,300);
P(100,Y=>300,X=>400,Z=>200);
In the above statements first will be valid remaining are
in-valid because here we are assigning values in two times to ‘X’.
Ø
values get referred from actual to formal and formal to actual
in two methods they are:
i.
Reference method; (enhances performance).
ii.
Copy method; (or) (pass by value method).
Ø By default
‘In’ parameter takes the values through reference method which is faster than
the copy method and desirable, where as in the case of out and in out
parameters values get passed through copy method. Which degrades
Ø the
performance, so to avoid this thing we have a reserved word called ‘No Copy’
which is introduced from 9i onwards.
No copy:-
ü
we mention No Copy for Out and InOut parameters so, to take the
values through reference method (which in turn) accelerates/alters the
performance.
Note:- Mentioning NOCOPY to the ‘IN’ parameters
throws an error.
Eg:- Create or replace procedure p(X number, Y out nocopy number) is
Begin
Y:=X+1000;
End p;
Note:- Here ‘nocopy’ is
reserved word or key word.
SQL*Plus:-
Var a number;
Var b number;
Exec :a:=100;
Exec p(:a, :b);
Print b;
ü
Once we pass the value to ‘In’ parameter throughout the program
we can’t vary the value.
Note:- Always‘In’ parameters should not be at left side of assignment
operator which means ‘In’ parameters acts as a ‘constant’ in the scope of
program.
Create or replace procedure p(X in number, Y out number) is
Begin
X:=200;
Y:=X+100;
End;
ü Exec p(100,:b)
Local subprograms:-
ü
They are procedures/functions which are defined in the
declarative section named and unnamed blocks.
ü
We have to call these local programs within the scope of that
block.
ü
It won’t get stored anywhere else on by them selves.
Declare
Procedure p is
Begin
local sub programs
DOCL(‘hello’);
End p;
Begin p;
End;
Here we won’t mention create/replace.
Here we won’t mention create/replace.
Declarative
section we will handle these 8:-
Variables,
exceptions, cursors, constants, data types, programs, defining local programs.
Forward
declaration:-
ü Basically
if you want to call a program it has to be get defined very previously.
ü Whenever
you call a program before defining throws an error.
ü For mutual
exchange purpose sometimes we have a need to call a procedure before defining.
ü To fulfill
this requirement we will declare the procedure very previously which is calling
before defining.
ü If you want
to call a procedure before defining you have to declare those procedures vary
previously.
Declare
V number (5):=5;
Procedure p2(Y inout number); //forward declaration
Procedure p1(X inout number) is
Begin
If X>0 then
Docl(X);
X:=X-1;
P2(X);//calling
End if;
End p1;
Procedure
p2(Y inout number) is //defining
Begin
P1(Y);
End p2;
Begin
P2(v); // calling and executable section first
compiler comes here.
First define then call any procedure without
defining if
you need
to make a call at least we need to declare.
End;
ü Create or
replace procedure p3 is
(1)
End p3;
Exec p3;
Note:-
A procedure
can optionally contain ‘return’ statement but it won’t through any value as in
the given eg.
Create or replace procedure p3 is
V
number(5):=100;
Begin
DOCL(v);
Return
v;
DOPL(v);
End p3;
No comments:
Post a Comment