Different ways to call or execute a procedure in PL/SQL

Execution methods:

   Positional notation:

Ø Specify the actual parameters by the same order as the formal parameters are declared.

   Named notation:

Ø Named the notation is an actual argument with the formal parameters.

Ø Named the notation allows changing the order in which you pass actual parameter.

    Mixed notation:

Ø Mix of position and named notation is mixed notation. The position must precede the named notation, reversed is not allowed.

Example:

1. Create or replace procedure update_sal(

      P_Empno number,

     amt    number,

     Proceed_update varchar2 default ‘N’)

   Is

   Begin

          Update emp set sal = sal + amt

          where Empno= P_Empno;

     Commit;

   If  Proceed_update =’Y’ then

         Dbms_output.put_line(‘Salary updated and committed for  the Employee || ‘ ’|| p_empno)

   Else

        Dbms_output.put_line(‘Salary updated but not committed for the Employee || ‘ ’|| p_empno)

   End if;

   End;

 Positional notation:

1. Begin

   Increment_sal(100,2000,’N’);

   End;

Explanation:

·       Here,100 is the empno (First parameter of the procedure),2000 is the amt (second parameter), ‘N’ is the last parameter).

·       Oracle takes the value of the Formal parameter when the position is an accurate match to the Actual parameters.

Output:

Salary updated and committed for  the Employee 100

Named notation:

1.  Begin

      Increment_sal(p_empno =>100,

   Amt          => 2000,

                             Proceed_update =>’N’);

   End;

Explanation:

·       Here, Parameter name with the value. we can able to jumbled the parameter what we want.

·       Order is not mandatory.

 Another method , 

1. Begin

          Increment_sal(Amt          => 2000,

                                   p_empno  =>100,

                                  Proceed_update  =>’N’);

   End; 

Explanation:

Mixed notation:

1.   Begin

      Increment_sal(100,

   Amt          => 2000,

                             Proceed_update =>’N’);

   End;

      Below execution shows error:

        Invalid notation:

1.  Begin

             Increment_sal(Amt          => 2000,

                             100,

                             Proceed_update =>’N’);

   End;

Output:

Error:

ORA: A positional parameter association may not follow a named association.

  

Share: