Defining and Using Functions › Defining a Function

Defining a Function

How to define and deploy user-defined functions depends on the language of the function.

• For SQL functions, specified with LANGUAGE SQL, define the function using the CREATE FUNCTION statement with the procedure-statement clause. After the successful execution and commit of the create function statement, the function can be invoked.
• For external functions, not specified with LANGUAGE SQL, complete the following steps:
  1. Define the function using the CREATE FUNCTION statement.
  2. Write the function in COBOL, PL/I, Assembler, or CA ADS following the guidelines given in this chapter. You may also be able to use an existing program as a template for a function.
  3. Define the function to a CA IDMS system, if necessary.
  4. Invoke the function as needed by specifying it anywhere that a value-expression can be specified in an SQL statement.

Note: You invoke the SQL function in a way very similar to the way in which you invoke built-in functions.

An example is shown next:

CREATE FUNCTION DEFJE01.UDF_FUNBONUS
  ( EMP_ID         DECIMAL(4)    )
    RETURNS DECIMAL(10)
    EXTERNAL NAME FUNBONUS PROTOCOL IDMS
    DEFAULT DATABASE CURRENT
    USER MODE
    LOCAL WORK AREA 0
    ;

Similarly, use the ALTER FUNCTION and DROP FUNCTION statements to modify and delete the definition of existing functions.

More Information

• For more information about the syntax and parameters used in defining functions, see CREATE FUNCTION, ALTER FUNCTION, and DROP FUNCTION.
• For more information and detailed examples about using a CREATE FUNCTION, see Sample COBOL Function, and Sample CA ADS Function.