SUBPROGRAMS
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
Ø 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
userA 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;
userA 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