Example of creating and using a user-defined scalar function
You can create a user-defined scalar function that gets input from a table and puts the output in a table.
Suppose that your organization needs a user-defined scalar function that calculates the bonus that each employee receives. All employee data, including salaries, commissions, and bonuses, is kept in the employee table, EMP. The input fields for the bonus calculation function are the values of the SALARY and COMM columns. The output from the function goes into the BONUS column. Because this function gets its input from a Db2 table and puts the output in a Db2 table, a convenient way to manipulate the data is through a user-defined function.
The user-defined function's definer and invoker determine that
this new user-defined function should have these characteristics:
- The user-defined function name is CALC_BONUS.
- The two input fields are of type DECIMAL(9,2).
- The output field is of type DECIMAL(9,2).
- The program for the user-defined function is written in COBOL and has a load module name of CBONUS.
Because no built-in function or user-defined function exists on
which to build a sourced user-defined function, the function implementer
must code an external user-defined function. The implementer performs
the following steps:
- Writes the user-defined function, which is a COBOL program
- Precompiles, compiles, and links the program
- Binds a package if the user-defined function contains SQL statements
- Tests the program thoroughly
- Grants execute authority on the user-defined function package to the definer
The user-defined function definer executes this CREATE FUNCTION
statement to register CALC_BONUS to Db2:
CREATE FUNCTION CALC_BONUS(DECIMAL(9,2),DECIMAL(9,2))
RETURNS DECIMAL(9,2)
EXTERNAL NAME 'CBONUS'
PARAMETER STYLE SQL
LANGUAGE COBOL;
The definer then grants execute authority on CALC_BONUS to all invokers.
User-defined function invokers write and prepare application programs
that invoke CALC_BONUS. An invoker might write a statement like this,
which uses the user-defined function to update the BONUS field in
the employee table:
UPDATE EMP
SET BONUS = CALC_BONUS(SALARY,COMM);
An invoker can
execute this statement either statically or dynamically.