Procedure / Function / Parameters

Procedure:

Syntax:

PROCEDURE [schema.]name [( parameter [, parameter ...] ) ]
   [AUTHID DEFINER | CURRENT_USER]
IS
   [declarations]


BEGIN
   executable statements


[ EXCEPTION
     exception handlers]


END [name];


Function:

Syntax:

FUNCTION [schema.]name [( parameter [, parameter ...] ) ]
   RETURN return_datatype
   [AUTHID DEFINER | CURRENT_USER]
   [DETERMINISTIC]
   [PARALLEL ENABLE ...]
   [PIPELINED]
IS
   [declaration statements]


BEGIN
   executable statements


[EXCEPTION
   exception handler statements]


END [ name ];


Note:

A function's execution section should have a RETURN statement in it,although it is not necessary for the function to compile. If, however, your function finishes executing without processing a RETURN statement, Oracle will raise the following error:

ORA-06503: PL/SQL: Function returned without value



PARAMETERS

1. A parameter has a passing mode.
2. A parameter declaration must be unconstrained.


Unconstrained means, the size or the upper limit of the datatype must not be set

Unconstrained Example: VARCHAR2
                                      NUMBER

Constrainted   Example : VARCHAR2(60)


The formal parameter and the actual parameter that corresponds to it (when called) must be of the same or compatible datatypes.

The actual parameter values are copied to the formal parameters in the function header and those will be used within the function.


Parameter Modes:

1. IN ( Read only ) : Default Mode

2. OUT ( Write only ):

Any assignments made to OUT parameters are rolled back when an exception is raised in the program. Because the value for an OUT parameter is not actually assigned until a program completes successfully, any intermediate assignments are therefore ignored. Unless an exception handler traps the exception and then assigns a value to the OUT parameter,no assignment is made to that parameter. The variable will retain the same value it had before the program was called.

Example:

Create or Replace Procedure test_out_par
(my_out_var out varchar2,
 my_in_var in varchar2)
Is
  my_exception exception;
Begin
  my_out_var:=my_in_var;
  raise my_exception;
End;
/


Declare
  my_out_var_kk varchar2(200);
  my_in_var_kk  varchar2(200);
Begin
  my_in_var_kk :='kiran';
  my_out_var_kk :='kodavati';
  dbms_output.put_line('my_in_var_kk:'|| my_in_var_kk);
  dbms_output.put_line('my_out_var_kk:'|| my_out_var_kk);
  test_out_par (my_out_var_kk,my_in_var_kk);
Exception
  when others then
    dbms_output.put_line('my_in_var_kk:'|| my_in_var_kk);
    dbms_output.put_line('my_out_var_kk:'|| my_out_var_kk);
End;
/

3. IN OUT ( Read / Write )

An IN OUT parameter cannot have a default value.


Matching Actual and Formal Parameters:


1. Positional notation
2. Named notation : (formal parameter => actual parameter)

Note: If we do mix notation, however, we must list all of our positional parameters before any named notation parameters. Positional notation has to have a starting point from which to keep track of positions, and the only starting point is the first parameter.


Advantages of Named notation:

--  We can list the parameters in any order we want.

--  We can also include only the parameters we want or need in the parameter list.

--  Self documenting.


As a general rule, named notation is the best way to write code that is readable and more easily maintained.


Default values:

There are two ways to specify a default value:

1.with the keyword DEFAULT
2.with the assignment operator (:=)