Implementing a user-defined function

You can code an application program to implement a user-defined function.

Procedure

Begin general-use programming interface information. To implement a user-defined function:

The implementer codes a program that implements the user-defined function. Assume that the implementer codes the following external user-defined function in C and names the function C_SALARY:

/**********************************************************************
* This routine accepts an employee serial number and a percent raise. *
* If the employee is a manager, the raise is not applied.  Otherwise, *
* the new salary is computed, truncated if it exceeds the employee's  *
* manager's salary, and then applied to the database.                 *
**********************************************************************/
 void C_SALARY                         /* main routine               */
 ( char        *employeeSerial         /* in: employee serial no.    */
   decimal     *percentRaise           /* in: percentage raise       */
   decimal     *newSalary,             /* out: employee's new salary */
   short int   *niEmployeeSerial       /* in: indic var, empl ser    */
   short int   *niPercentRaise         /* in: indic var, % raise     */
   short int   *niNewSalary,           /* out: indic var, new salary */
   char        *sqlstate,              /* out: SQLSTATE              */
   char        *fnName,                /* in: family name of function*/
   char        *specificName,          /* in: specific name of func  */
   char        *message                /* out: diagnostic message    */
 )
 {
   EXEC SQL BEGIN DECLARE SECTION;
   char        hvEMPNO-7-;             /* host var for empl serial   */
   decimal     hvSALARY;               /* host var for empl salary   */
   char        hvWORKDEPT-3-;          /* host var for empl dept no. */
   decimal     hvManagerSalary;        /* host var,emp's mgr's salary*/
   EXEC SQL END DECLARE SECTION;
 
   sqlstate = 0;
   memset( message,0,70 );
   /*******************************************************************
   * Copy the employee's serial into a host variable                  *
   *******************************************************************/
   strcpy( hvEMPNO,employeeSerial );
   /*******************************************************************
   * Get the employee's work department and current salary            *
   *******************************************************************/
   EXEC SQL SELECT  WORKDEPT, SALARY
              INTO :hvWORKDEPT, :hvSALARY
              FROM  EMP
             WHERE  EMPNO = :hvEMPNO;
   /*******************************************************************
   * See if the employee is a manager                                 *
   *******************************************************************/
   EXEC SQL SELECT  DEPTNO
              INTO :hvWORKDEPT
              FROM  DEPT
             WHERE  MGRNO = :hvEMPNO;
   /*******************************************************************
   * If the employee is a manager, do not apply the raise             *
   *******************************************************************/
   if( SQLCODE == 0 )
     {
       newSalary = hvSALARY;
     }
   /*******************************************************************
   * Otherwise, compute and apply the raise such that it does not     *
   * exceed the employee's manager's salary                           *
   *******************************************************************/
   else
     {
       /***************************************************************
       * Get the employee's manager's salary                          *
       ***************************************************************/
       EXEC SQL SELECT  SALARY
                  INTO :hvManagerSalary
                  FROM  EMP
                 WHERE  EMPNO = (SELECT MGRNO
                                   FROM DSN8610.DEPT
                                  WHERE DEPTNO = :hvWORKDEPT);
       /***************************************************************
       * Compute proposed raise for the employee                      *
       ***************************************************************/
       newSalary = hvSALARY * (1 + percentRaise/100);
       /***************************************************************
       * Don't let the proposed raise exceed the manager's salary     *
       ***************************************************************/
       if( newSalary > hvManagerSalary
         newSalary = hvManagerSalary;
       /***************************************************************
       * Apply the raise                                              *
       ***************************************************************/
       hvSALARY = newSalary;
       EXEC SQL UPDATE  EMP
                   SET  SALARY = :hvSALARY
                 WHERE  EMPNO  = :hvEMPNO;
     }
 
   return;
} /* end C_SALARY */

The implementer requires the UPDATE privilege on table EMP. Users with the EXECUTE privilege on function C_SALARY do not need the UPDATE privilege on the table.

Because this program contains SQL, the implementer performs the following steps:
1. Precompile the program that implements the user-defined function.
2. Link-edit the user-defined function with DSNRLI (RRS attachment facility), and name the program's load module C_SALARY.
3. Bind the DBRM into package MYCOLLID.C_SALARY.
After performing these steps, the implementer is the function package owner.
The implementer then grants EXECUTE privilege on the user-defined function package to the definer.
```
GRANT EXECUTE ON PACKAGE MYCOLLID.C_SALARY
 TO definer
```
As package owner, the implementer can grant execute privileges to other users, which allows those users to execute code within the package. For example:
```
GRANT EXECUTE ON PACKAGE MYCOLLID.C_SALARY
      TO other_user
```