PROCEDURES
A procedure is a module
that performs one or more actions.
Syntax:
Procedure [schema.]name [(parameter1 [,parameter2
…])]
[authid definer | current_user] is
--
[declarations]
Begin
--
executable statements
[Exception
--
exception handlers]
End [name];
In the above authid
clause defines whether the procedure will execute under the authority of the
definer of the procedure or under the authority of the current user.
FUNCTIONS
A function is a module
that returns a value.
Syntax:
Function
[schema.]name [(parameter1 [,parameter2
…])]
Return return_datatype
[authid definer | current_user]
[deterministic]
[parallel_enable] is
--
[declarations]
Begin
--
executable statements
[Exception
--
exception handlers]
End [name];
In the above authid
clause defines whether the procedure will execute under the authority of the
definer of the procedure or under the authority of the current user.
Deterministic clause defines, an
optimization hint that lets the system use a saved copy of the function’s
return result, if available. The quety optimizer can choose whether to use the
saved copy or re-call the function.
Parallel_enable clause defines, an
optimization hint that enables the function to be executed in parallel when called
from within SELECT statement.
PARAMETER MODES
v In (Default)
v Out
v In out
IN
In parameter will act as
pl/sql constant.
OUT
Ø Out parameter will act
as unintialized variable.
Ø You cannot provide a
default value to an out parameter.
Ø Any assignments made to out parameter are rolled back when an
exception is raised in the program.
Ø An actual parameter
corresponding to an out formal
parameter must be a variable.
IN OUT
Ø In out parameter will
act as initialized variable.
Ø An actual parameter
corresponding to an in out formal
parameter must be a variable.
DEFAULT PARAMETERS
Default Parameters will
not allow in the beginning and middle.
Out and In Out parameters
can not have default values.
Ex:
procedure p(a in number
default 5, b in number default 6, c in number default 7) – valid
procedure
p(a in number, b in number default 6, c in number default 7) – valild
procedure
p(a in number, b in number, c in number default 7) – valild
procedure p(a
in number, b in number default 6, c in number) – invalild
procedure
p(a in number default 5, b in number default 6, c in number) – invalild
procedure
p(a in number default 5, b in number, c in number) – invalild
NOTATIONS
Notations are of two
types.
Ø Positional notation
Ø Name notation
We can combine
positional and name notation but positional notation can not be followed by the
name notation.
Ex:
Suppose we have a procedure proc(a
number,b number,c number) and we have one
anonymous block which contains v1,v2, and
v3;
SQL> exec proc (v1,v2,v3) --
Positional notation
SQL> exec proc (a=>v1,b=>v2,c=>v3) -- Named notation
FORMAL AND ACTUAL PARAMETERS
Ø Parametes which are in
calling subprogram are actual parameters.
Ø Parametes which are in
called subprogram are formal parameters.
Ø If any subprogram was
called, once the call was completed then the values of formal
parameters are copied to the actual
parameters.
Ex1:
CREATE OR
REPLACE PROCEDURE SAMPLE(a in number,b out number,c in out number) is
BEGIN
dbms_output.put_line('After call');
dbms_output.put_line('a = ' || a ||' b = '
|| b || ' c = ' || c);
b := 10;
c := 20;
dbms_output.put_line('After assignment');
dbms_output.put_line('a = ' || a ||' b = '
|| b || ' c = ' || c);
END SAMPLE;
DECLARE
v1 number := 4;
v2 number := 5;
v3 number := 6;
BEGIN
dbms_output.put_line('Before call');
dbms_output.put_line('v1 = ' || v1 || ' v2
= ' || v2 || ' v3 = ' || v3);
sample(v1,v2,v3);
dbms_output.put_line('After completion of
call');
dbms_output.put_line('v1 = ' || v1 || ' v2
= ' || v2 || ' v3 = ' || v3);
END;
Output:
Before call
v1 = 4 v2 =
5 v3 = 6
After call
a = 4 b
= c = 6
After
assignment
a = 4 b = 10
c = 20
After
completion of call
v1 = 4 v2 =
10 v3 = 20
Ex2:
CREATE OR
REPLACE FUN(a in number,b out number,c in out number) return number IS
BEGIN
dbms_output.put_line('After call');
dbms_output.put_line('a = ' || a || ' b =
' || b || ' c = ' || c);
dbms_output.put_line('Before assignement
Result = ' || (a*nvl(b,1)*c));
b := 5;
c := 7;
dbms_output.put_line('After assignment');
dbms_output.put_line('a = ' || a || ' b =
' || b || ' c = ' || c);
return (a*b*c);
END FUN;
DECLARE
v1 number := 1;
v2 number := 2;
v3 number := 3;
v number;
BEGIN
dbms_output.put_line('Before call');
dbms_output.put_line('v1 = ' || v1 || '
v2 = ' || v2 || ' v3 = ' || v3);
v := fun(v1,v2,v3);
dbms_output.put_line('After call
completed');
dbms_output.put_line('v1 = ' || v1 || '
v2 = ' || v2 || ' v3 = ' || v3);
dbms_output.put_line('Result = ' || v);
END;
Output:
Before call
v1 = 1 v2 =
2 v3 = 3
After call
a = 1 b
= c = 3
Before
assignement Result = 3
After
assignment
a = 1 b = 5
c = 7
After call
completed
v1 = 1 v2 =
5 v3 = 7
Result = 35
RESTRICTIONS ON FORMAL
PARAMETERS
Ø By declaring with
specified size in actual parameters.
Ø By declaring formal
parameters with %type specifier.
USING NOCOPY
Ø Nocopy is a hint, not a command. This means that the compiler might
silently decide that it can’t fulfill your request for a nocopy parameter.
Ø The copying from formal
to actual can be restricted by issuing nocopy
qualifier.
Ø To pass the out and in
out parameters by reference use nocopy qualifier.
Ex:
CREATE OR REPLACE
PROCEDURE PROC(a in out nocopy number) IS
BEGIN
----
END PROC;
CALL AND EXEC
Call is a SQL statement, which can be
used to execute subprograms like exec.
Syntax:
Call subprogram_name([argument_list]) [into host_variable];
Ø The parantheses are
always required, even if the subprogram takes no arguments.
Ø We can not use call with
out and in out parameters.
Ø Call is a SQL statement, it is not
valid inside a PL/SQL block;
Ø The INTO clause is used for the
output variables of functions only.
Ø We can not use ‘exec’
with out or in out parameters.
Ø Exec is not valid inside
a PL/SQL block;
Ex1:
CREATE OR
REPLACE PROC IS
BEGIN
dbms_output.put_line('hello
world');
END PROC;
Output:
SQL> call proc();
hello world
Ex2:
CREATE OR
REPLACE PROC(a in number,b in number) IS
BEGIN
dbms_output.put_line('a = ' || a || ' b = ' || b);
END PROC;
Output:
SQL> call proc(5,6);
a = 5 b = 6
Ex3:
CREATE OR
REPLACE FUNCTION FUN RETURN VARCHAR IS
BEGIN
return 'hello world';
END FUN;
Output:
SQL> variable v varchar(20)
SQL> call fun() into :v;
SQL> print v
hello world
CALL BY REFERENCE AND CALL
BY VALUE
Ø In parameters by default
call by reference where as out and in
out call by value.
Ø When parameter passed by
reference, a pointer to the actual parameter is passed to the
corresponding formal parameter.
Ø When parameter passed by
value it copies the value of the actual parameter to the formal parameter.
Ø Call by reference is
faster than the call by value because it avoids the copying.
SUBPROGRAMS OVERLOADING
Ø Possible with different
number of parameters.
Ø Possible with different
types of data.
Ø Possible with same type
with objects.
Ø Can not be possible with
different types of modes.
Ø We can overload local
subprograms also.
Ex:
SQL> create or replace type
t1 as object(a number);/
SQL> create or replace type
t1 as object(a number);/
DECLARE
i t1 := t1(5);
j t2 := t2(5);
PROCEDURE P(m t1) IS
BEGIN
dbms_output.put_line('a = ' || m.a);
END P;
PROCEDURE P(n t2) IS
BEGIN
dbms_output.put_line('b = ' || n.b);
END P;
PROCEDURE PRODUCT(a number,b number) IS
BEGIN
dbms_output.put_line('Product of a,b = ' ||
a * b);
END PRODUCT;
PROCEDURE PRODUCT(a number,b number,c
number) IS
BEGIN
dbms_output.put_line('Product of a,b =
' || a * b * c);
END PRODUCT;
BEGIN
p(i);
p(j);
product(4,5);
product(4,5,6);
END;
Output:
a = 5
b = 5
Product of
a,b = 20
Product of
a,b = 120
BENEFITS OF OVERLOADING
Ø Supporting many data
combinations
Ø Fitting the program to
the user.
RESTRICTIONS ON OVERLOADING
Ø Overloaded programs with
parameter lists that differ only by name must be called using named notation.
Ø The parameter list of
overloaded programs must differ by more than parameter mode.
Ø All of the overloaded
programs must be defined within the same PL/SQL scope or block.
Ø Overloaded functions
must differ by more than their return type.
IMPORTANT POINTS ABOUT
SUBPROGRAMS
Ø When a stored subprogram
is created, it is stored in the data
dictionary.
Ø The subprogram is stored
in compile form which is known as p-code
in addition to the source text.
Ø The p-code has all of
the references in the subprogram evaluated, and the source code is translated
into a form that is easily readable by PL/SQL engine.
Ø When the subprogram is
called, the p-code is read from the disk, if necessary, and executed.
Ø Once it reads from the
disk, the p-code is stored in the shared pool portion of the system global area
(SGA), where it can be
accessed by multiple users as needed.
Ø Like all of the contents
of the shared pool, p-code is aged out of the shared pool according to a least
recently used (LRU) algorithm.
Ø Subprograms can be local.
Ø Local subprograms must
be declared in the declarative section of PL/SQL block and called from the executable section.
Ø Subprograms can not have
the declarative section separately.
Ø Stored subprograms can
have local subprograms;
Ø Local subprograms also
can have local subprograms.
Ø If the subprogram
contains a variable with the same name as the column name of the table then use
the dot method to differentiate (subprogram_name.sal).
Ø Subprograms can be
invalidated.
PROCEDURES V FUNCTIONS
Ø Procedures may return
through out and in out parameters where as function must return.
Ø Procedures can not have
return clause where as functions must.
Ø We can use call
statement directly for executing procedure where as we need to declare a
variable in case of functions.
Ø Functions can use in
select statements where as procedures can not.
Ø Functions can call from
reports environment where as procedures can not.
Ø We can use exec for
executing procedures where as functions can not.
Ø Function can be used in
dbms_output where as procedure can not.
Ø Procedure call is a
standalone executable statement where as function call is a part of an
executable statement.
STORED V LOCAL SUBPROGRAMS
Ø The stored subprogram is
stored in compiled p-code in the database, when the procedure is called it does
not have to be compiled.
The local subprogram is compiled as part
of its containing block. If the containing block
is anonymous and is run multiple times,
the subprogram has to be compiled each time.
Ø Stored subprograms can
be called from any block submitted by a user who has execute privileges on the
subprogram.
Local subprograms can be called only from
the block containing the subprogram.
Ø By keeping the stored
subprogram code separate from the calling block, the calling block is shorter
and easier to understand.
The local subprogram and the calling block
are one and the same, which can lead to part
confusion. If a change to the calling
block is made, the subprogram will be recompiled as
of the recompilation of the containing
block.
Ø The compiled p-code can
be pinned in the shared pool using the DBMS_SHARED_POOL
Package. This can improve performance.
Local subprograms cannot be pinned in the
shared pool by themselves.
Ø Stand alone stored
subprograms can not be overloaded, but packaged subprograms can
be overloaded within the same package.
Ø Local subprograms can be
overloaded within the same block.
Ex1:
CREATE OR
REPLACE PROCEDURE P IS
BEGIN
dbms_output.put_line('Stored subprogram');
END;
Output:
SQL> exec p
Stored
subprogram
Ex2:
DECLARE
PROCEDURE P IS
BEGIN
dbms_output.put_line('Local
subprogram');
END;
BEGIN
p;
END;
Output:
Local subprogram
COMPILING SUBPROGRAMS
Ø SQL> Alter procedure P1
compile;
Ø SQL> Alter function F1
compile;
SUBPROGRAMS DEPENDECIES
Ø A stored subprogram is
marked as invalid in the data dictionary if it has compile errors.
Ø A stored subprogram can
also become invalid if a DDL operation is performed on one of its dependent
objects.
Ø If a subprogram is
invalidated, the PL/SQL engine will automatically attempt to recompile in the next time
it is called.
Ø If we have two
procedures like P1 and P2 in which P1 depends on P2. If we compile P2 then P1
is invalidated.
SUBPROGRAMS DEPENDENCIES IN
REMOTE DATABASES
Ø We will call remote
subprogram using connect string like P1@ORACLE;
Ø If we have two
procedures like P1 and P2 in which P1 depends on P2 but P2 was in remote
database. If we compile P2 it will not invalidate P1 immediately because the
data dictionary does not track remote dependencies.
Ø Instead the validity of
remote objects is checked at runtime. When P1 is called, the remote data
dictionary is queried to determine the status of P2.
Ø P1 and P2 are compared
to see it P1 needs to be recompiled, there are two different methods of
comparision
ü Timestamp Model
ü Signature Model
TIMESTAMP MODEL
Ø This is the default
model used by oracle.
Ø With this model, the
timestamps of the last modifications of the two objects are
compared.
Ø The last_ddl_time field of user_objects
contains the timestamp.
Ø If the base object has a
newer timestamp than the dependent object, the dependent
object will be recompiled.
ISSUES WITH THIS MODEL
Ø If the objects are in
different time zones, the comparison is invalid.
Ø When P1 is in a client
side PL/SQL engine such as oracle
forms, in this case it may not possible to recompile P1, because the source for
it may not be included with the forms.
SIGNATURE MODEL
Ø When a procedure is
created, a signature is stored in the data dictionary in addition to the
p-code.
Ø The signature encodes
the types and order of the parametes.
Ø When P1 is compiled the
first time, the signature of P2 is included. Thus, P1 only needs to recompiled
when the signature of P2 changes.
Ø In order to use the
signature model, the parameter REMOTE_DEPENDENCIES_MODE must be set to SIGNATURE. This is a parameter in the database initialization file.
THREE WAYS OF SETTING THIS
MODE
Ø Add the line REMOTE_DEPENDENCIES_MODE=SIGNATURE to the database
initialization file. The next time the database is started, the mode will be
set to SIGNATURE for all sessions.
Ø Alter system set
remote_dependencies_mode = signature;
This will affect the entire database (all
sessions) from the time the statement is issued.
You must have the ALTER SYSTEM privilege to issue this
command.
Ø Alter session set
remote_dependencies_mode = signature;
This will only affect your session
ISSUES WITH THIS MODEL
Ø Signatures don’t get modified
if the default values of formal parameters are changed.
Ø Suppose P2 has a default
value for one of its parameters, and P1 is using this default
value. If the default in the
specification for P2 is changed, P1 will not be recompiled
by default. The old value for the default
parameter will still be used until P1 is manually
recompiled.
Ø If P1 is calling a
packaged procedure P2, and a new overloaded version of P2 is added to
the remote package, the signature is not
changed. P1 will still use the old version
(not the new overloaded one) until P1 is
recompiled manually.
FORWARD DECLERATION
Before going to use the
procedure in any other subprogram or other block , you must declare the
prototype of the procedure in declarative section.
Ex1:
DECLARE
PROCEDURE P1 IS
BEGIN
dbms_output.put_line('From procedure
p1');
p2;
END P1;
PROCEDURE P2 IS
BEGIN
dbms_output.put_line('From procedure
p2');
p3;
END P2;
PROCEDURE P3 IS
BEGIN
dbms_output.put_line('From procedure
p3');
END P3;
BEGIN
p1;
END;
Output:
p2;
*
ERROR at
line 5:
ORA-06550:
line 5, column 1:
PLS-00313:
'P2' not declared in this scope
ORA-06550:
line 5, column 1:
PL/SQL:
Statement ignored
ORA-06550:
line 10, column 1:
PLS-00313:
'P3' not declared in this scope
ORA-06550:
line 10, column 1:
PL/SQL:
Statement ignored
Ex2:
DECLARE
PROCEDURE P2; -- forward declaration
PROCEDURE P3;
PROCEDURE P1 IS
BEGIN
dbms_output.put_line('From procedure
p1');
p2;
END P1;
PROCEDURE P2 IS
BEGIN
dbms_output.put_line('From procedure
p2');
p3;
END P2;
PROCEDURE P3 IS
BEGIN
dbms_output.put_line('From procedure
p3');
END P3;
BEGIN
p1;
END;
Output:
From
procedure p1
From
procedure p2
From
procedure p3
PRIVILEGES AND STORED
SUBPROGRAMS
EXECUTE PREVILEGE
Ø For stored subprograms
and packages the relevant privilege is EXECUTE.
Ø If user A had the
procedure called emp_proc then user A grants execute privilege on procedure to
user B with the following command.
SQL> Grant execute on
emp_proc to user B.
Ø Then user B can run the
procedure by issuing
SQL> Exec user A.emp_proc
User A created the
following procedure
CREATE OR
REPLACE PROCEDURE P IS
cursor is select *from student1;
BEGIN
for v in c loop
insert into student2
values(v.no,v.name,v.marks);
end loop;
END P;
User A granted execute
privilege to userB using
SQL> grant execute on p to
userB
Then userB executed the
procedure
SQL> Exec userA.p
If suppose userB also
having student2 table then which table will populate whether userA’s or
userB’s.
The answer is userA’s
student2 table only because by default the procedure will execute under the
privlige set of its owner.
The above procedure is
known as definer’s procedure.
HOW TO POPULATE USER B’s
TABLE
Ø Oracle introduces Invoker’s and Definer’s rights.
Ø By default it will use
the definer’s rights.
Ø An invoker’s rights
routine can be created by using AUTHID clause to populate the
userB’s table.
Ø It is valid for
stand-alone subprograms, package specifications, and object type
specifications only.
userA created the
following procedure
CREATE OR
REPLACE PROCEDURE P
AUTHID
CURRENT_USER IS
cursor is select *from student1;
BEGIN
for v in c loop
insert into student2
values(v.no,v.name,v.marks);
end loop;
END P;
Then grant execute
privilege on p to userB.
Executing the procedure
by userB, which populates userB’s table.
The above procedure is
called invoker’s procedure.
Instead of current_user
of authid clause, if you use definer then it will be called definer’ procedure.
STORED SUBPROGRAMS AND ROLES
we have two users saketh
and sudha in which saketh has student table and sudha does not.
Sudha is going to create
a procedure based on student table owned by saketh. Before doing this saketh
must grant the permissions on this table to sudha.
SQL> conn saketh/saketh
SQL> grant all on student to
sudha;
then sudha can create
procedure
SQL> conn sudha/sudha
CREATE OR REPLACE
PROCEDURE P IS
cursor c is select *from saketh.student;
BEGIN
for v in c loop
dbms_output.put_line(‘No = ‘ ||
v.no);
end loop;
END P;
here procedure will be
created.
If the same privilege
was granted through a role it wont create the procedure.
Examine the following
code
SQL> conn saketh/saketh
SQL> create role saketh_role;
SQL> grant all on student to
saketh_role;
SQL> grant saketh_role to
sudha;
then conn sudha/sudha
CREATE OR REPLACE
PROCEDURE P IS
cursor c is select *from saketh.student;
BEGIN
for v in c loop
dbms_output.put_line(‘No = ‘ ||
v.no);
end loop;
END P;
The above code will
raise error instead of creating procedure .
This is because of early
binding which PL/SQL uses by default in which references are evaluated in compile
time but when you are using a role this will affect immediately.
ISSUES WITH INVOKER’S RIGHTS
Ø In an invoker’s rights
routine, external references in SQL statements will be resolved using the caller’s privilege set.
Ø But references in PL/SQL statements are still
resolved under the owner’s privilege set.
TRIGGERS, VIEWS AND
INVOKER’S RIGHTS
Ø A database trigger will
always be executed with definer’s rights and will execute under the privilege
set of the schema that owns the triggering table.
Ø This is also true for PL/SQL function that is called
from a view. In this case, the function will execute under the privilege set of
the view’s owner.
No comments:
Post a Comment