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
No comments:
Post a Comment