Learning Visually with Examples [Electronic resources]

7.15. User-Defined Functions

DB2 provides built-in functions that allow you to manipulate your data within an SQL statement. For example, the

year function can retrieve the year of a timestamp column, as shown here.

db2 select year(current timestamp) from sysibm.sysdummy1 
1
-----------
2005

In addition to built-in functions, DB2 allows you to create your own functions. These

user-defined functions (UDFs) allow you to simplify database application development by moving some of the logic to the database. A UDF takes zero to many input parameters and returns a value, a row or a table. To create a UDF, use the

CREATE FUNCTION statement.

UDFs can be classified as follows.

• Sourced functions : These functions are created on top of DB2 built-in functions. Here's an example.

  CREATE FUNCTION trim (p_var1 VARCHAR(50)) 
  RETURNS VARCHAR(50) 
  RETURN RTRIM(LTRIM(p_var1)) 
  

  In this example,

  RTRIM is a DB2 built-in function that removes all the blanks at the end of a string.

  LTRIM is a DB2 built-in function that removes all the blanks at the beginning of a string. The UDF

  trim is created to remove blanks at the beginning and the end of a string by using these two built-in functions. To test the function, you can use the

  VALUES statement as follows:

  VALUES (trim('       hello    ') 
  

  which returns:

  1
  --------------------------------------------------
  hello
  

• SQL functions : These functions are written in SQL PL language. They can return a scalar value, a single row, or a table of data. The following code shows an example of an SQL UDF returning a scalar value: the rounded salary of an employee.

  CREATE FUNCTION csmmgr.salary_round(p_empno CHAR(6)) 
  RETURNS INTEGER 
  LANGUAGE SQL 
  F1: BEGIN ATOMIC 
  DECLARE v_salary INTEGER; 
  SET v_salary = (SELECT ceiling(salary) FROM employee 
  WHERE empno = p_empno); 
  RETURN v_salary; 
  END 
  

  This function takes an employee number as input and returns the salary rounded to the highest integer value. SQL functions can be developed by using the Development Center, as illustrated in Figure 7.29.

  Figure 7.29. Using the Development Center tool to develop, test, and run the

  csmmgr.salary_round user-defined function

  [View full size image]

• External functions : These functions are defined in the database with references to object code libraries that are written in C, Java, or OLE. Consider this example.

  CREATE FUNCTION csmmgr.db2killapp(INT) 
  RETURNS INT 
  EXTERNAL NAME 'db2killapplib!db2killapp' 
  LANGUAGE C 
  PARAMETER STYLE SQL 
  NOT FENCED 
  RETURNS NULL ON NULL INPUT 
  NOT DETERMINISTIC 
  NO SQL 
  NO EXTERNAL ACTION 
  

  This statement registers the UDF

  csmmgr.db2killapp to DB2. It is an external function written in C. The C executable code is stored in the

  db2killapplib library, which is stored under the sqllib\function subdirectory.