DECLARE FUNCTION

Syntax

<declare_function> ::=

  DECLARE FUNCTION function_name (<input_parameter_clause>) RETURNS return_datatype

  AS

  [<declare_clause>]

  BEGIN

    [<compound_statement>]

    [<when_do>]

  END

<input_parameter_clause> ::= <input_parameter_spec> [, <input_parameter_spec>]

<input_parameter_spec> ::= parameter_name <data_type> [NOT NULL] [{DEFAULT | = } value]

<declare_clause> ::= { <declare_variable> | <declare_cursor> | <declare_subroutine> } [<declare_clause>]

<declare_subroutine> ::== { <declare_function> | <declare_procedure> }

<compound_statement> ::= { <block> | <statement> } [{ <block> | <statement> }]

As with the complete code block, this can have optional input parameters and a declare clause, but it will only return a single value.

The function body requires a RETURN statement to return the value of the function to the caller.

Examples

execute udsblock(pin integer)

returns(pout integer, pout2 integer)

as

 declare function mult(i1 integer, i2 integer default 10) returns integer

 as 

 begin

   return i1 * i2;

 end

begin

  pout = mult(pin, 5);

  pout2 = mult(pin);

end

In the example above, a function subroutine "mult" is declared that accepts 2 input parameters, 1 optional.

The routine is called twice, once with 2 values and the second time with only 1 value.

Subroutines can be called recursively.

execute udsblock(employeeid integer)

returns(o1 integer)

as

  declare function recursive_test(v integer) returns integer

  as

  begin

    if (v > 0)

    then begin

           v = v - 1;

           v = recursive_test(v);

         end  

    return v;

  end

begin

  o1 = 10;

  o1 = 20;

  o1 = recursive_test(4);

end