Total Pageviews

March 31, 2015

3/31/2015 10:27:00 AM


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;




 
Related Posts Plugin for WordPress, Blogger...