Functions, procedures and their parameters - General concepts

The differences between functions and procedures are:

1) Functions need to be part of an expression (ie. assignment) whilst procedures can be called by themselves

Function:

DECLARE

    v_student_name students.name%TYPE

BEGIN

   

      v_student_name :=  get_student_name_function( 'student_id' => 1020283 );

END

Procedure:

   remove_graduated_students( :current_year => 2011, :current_campus => 'Clayton' );

2) Functions MUST return value(s) whilst procedures WILL NEVER EVER return anything

Format:

FUNCTION <name>

   RETURN return_datatype

IS

   ---declare all variables here

BEGIN

[EXCEPTION]

END [<name>];

3) format

FUNCTION:

FUNCTION <name>

 [

    (args)

 ]

   RETURN return_datatype

IS

   ---declare all variables here

BEGIN

[EXCEPTION]

END [<name>];

PROCEDURE:

PROCEDURE <name> 

 [

    (args)

 ]

IS

   ---declare all variables here

BEGIN

[EXCEPTION]

END [<name>];

Parameters:

 In the world of pl/sql, there are 2 types of parameters:
a) formal - this is the name of the parameter that is known to callers of the function/procedure. b) actual - the actual values that are represented by the formal parameters
Thinking in terms of a perl hash, formal parameters are keys whilst the actual parameters are the values.
Example:

FUNCTION get_total_sales 

     (   

   

          company_id IN company.id%TYPE,

     

         status          IN company.status%TYPE 

 

    ) 

  RETURN NUMBER

IS   v_total_sales NUMBER;

data type    ….  

 RETURN v_total_sales;

END 

DBMS_OUTPUT.PUT_LINE "The total sales is " || get_total_sales( :company_id => 21232, :status => 'trading' )

actual parameters: 21232 and 'trading'

formal parameters: company_id and status

Modes for parameters:

 The following modes are used when you define a procedure/function's parameters in its header section.

IN         - can only be read. Think of them as constants that cannot be mutated. This is the default mode pl/sql will assume when none is specified in your functions/procedures

OUT      - can only be written to (ie. assigned)

IN OUT  - can be read and written to. This is useful in the especially for procedures which would do processing and alter the values of certain variables as it does not return any values.

Advice: best to be clear in your parameters definition in the headers of your procedures/functions