PARAMETER
MODES
1. In (Default)
2. Out
3. In out
IN
In parameter will act as pl/sql
constant.
OUT
Out
parameter will act as uninitialized 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) – valid
procedure p(a in number, b in number, c in
number default 7) – valid
procedure p(a in number, b in number
default 6, c in number) – invalid
procedure p(a in number default 5, b in
number default 6, c in number) – invalid
procedure p(a in number default 5, b in
number, c in number) – invalid
NOTATIONS
Notations are of two types.
Positional notation
Name notation
We can combine positional and name
notation but positional notation cannot 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
Parameters which are in
calling subprogram are actual parameters.
Parameters 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
assignment 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 assignment 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;
