create function

Create a stored function

Syntax

create_function:
          CREATE FUNCTION [database_name.]func_name ( [fcn_arg_decl [, fcn_arg_decl]...] )
          RETURNS var_type
          [LANGUAGE SQL]
          [[NOT] DETERMINISTIC]
          [CONTAINS SQL | {MODIFIES | READS} SQL DATA]
          sqlproc_stmt

fcn_arg_decl:
          arg_name var_type

Description

The CREATE FUNCTION statement is used to define a stored function named func_name. The name of the database (database_name) with which the procedure is to be associated can optionally be specified. If not specified, then the stored procedure will be associated with either the database accessed by the SQL statements contained in it or, if there are none, then the most recently opened database. Function names associated with the same database must be unique.

The CREATE FUNCTION statement defines a function that can be referenced in any SQL expression just like an SQL built-in function such as SQRT or CONCAT. Use of the CREATE FUNCTION provides the ability to more easily implement and more quickly make available new functions that are needed by the application or users.

Arguments declared for a stored function are input only. Stored functions return a single result whose type is specified by the RETURNS clause. The other clauses are the same as described above for CREATE PROCEDURE.

The sqlproc_stmt and statement_list possibilities are described earlier in section Compound Statements.

Note that the CREATE FUNCTION statement is not transactional so that the stored function is created immediately upon successful execution of the statement.

Example

create function my_sqrt(in val double, in eps double) returns doublebegin    declare low, high, mid, oldmid, midsqr double;    if val < 0.0 then        signal sqlcode eINVARG set message_text = "sq root of negative number";    end if;    if eps <= 0.0 or eps > 0.01 then    signal sqlcode eINVARG set message_text = "2nd arg must be >= 0.0 and < 0.01";    end if;    set (low, mid, high, oldmid) = (0.0, val, val, -1.0);    while abs(oldmid - mid) >= eps do        set oldmid = mid;        set mid = (high + low)/2.0;        set midsqr = mid * mid;        if midsqr > val then            set high = mid;        else            set low = mid;        end if;    end while;    return mid;end;