This section describes how user-defined functions are invoked, including the purpose, syntax, parameters, usage considerations, and examples.
A user-defined function is invoked through a qualified or unqualified function identifier together with an optional set of parameter values and returns a single value. To invoke a user-defined function, you must either own or hold the SELECT privilege on the named function.
Expansion of user-defined-function
►►─┬────────────┬──────function-identifier────────────────────────────────────► └schema-name.┘ ►─ ( ─┬───────────────────────────────┬─ ) ──────────────────────────────────►◄ │ ┌──────────── , ───────────┐ │ └─▼─ parameter-specification─┴──┘
Expansion of parameter-specification
►►────┬───────────────────────┬─ value-expression ────────────────────────────►◄ └─ parameter-name ── = ─┘
Specifies the schema with which the function identified by function-identifier is associated.
Note: For more information about using a schema name to qualify a function, see Identifying Entities in Schemas.
Identifies a function defined in the dictionary.
Specifies a value to be assigned to a parameter of a function. Both the positional (with NO parameter-name) and the non-positional (with parameter-name) forms of parameter specification can be used in a single function invocation. If a non-positional parameter specification is used, all remaining parameter specifications in the parameter list MUST be non-positional. Positional parameter specifications are assumed to correspond to the declared parameters of a function in the sequence of their declaration.
Specifies the name of a parameter associated with the function.
Specifies a value-expression. See Expansion of Value-expression.
Passing and Returning Values to a Function: During SQL function processing, CA IDMS issues a call to the corresponding SQL-invoked routine with the values supplied in the function invocation. Before returning control, the SQL-invoked routine must set a value for the implicitly defined output parameter USER_FUNC; this then becomes the function return value.
Usage Restriction: You cannot reference a user-defined function within the search condition of a table's check constraint.
The invocation of the function UDF_FUNBOUS, defined in the schema FIN, causes the external program FUNBONUS to be called by CA IDMS with two parameters. The first parameter contains the value for EMP_ID, the second is the implicitly defined parameter USER_FUNC, which needs to be given a value by FUNBONUS before returning control to CA IDMS.
SELECT EMP_ID, FUN.UDF_FUNBONUS(EMP_ID) FROM DEMOEMPL.EMPLOYEE;
|
Copyright © 2014 CA.
All rights reserved.
|
|