Examples of UDFs

Example 1: Determining the year number

The GetCurrentYear UDF below returns the current year as a number. It contains no SQL statements for reading or updating data.

CREATE FUNCTION GetCurrentYear (IN time TIMESTAMP(3))
   RETURNS DECIMAL(4)
   CONTAINS SQL
   RETURN EXTRACT (YEAR FROM time)

The GetCurrentYear UDF in the schema FuncSchema is used:

• Determining all orders of the year 2014:

  DECLARE cursor_1 CURSOR FOR

  SELECT order_number, customer_name FROM orders

  WHERE FuncSchema.GetCurrentYear(order_completion_date) = 2014

• Set expiration year to the year after next (schema FuncSchema is preset):

  UPDATE model.exemplar

  SET expiration_year = GetCurrentYear(CURRENT_TIMESTAMP(3)) + 2

Example 2: Determining the price of an item

CREATE FUNCTION ITEM_PRICE (IN P_ITEMNUM INTEGER) 
   RETURNS NUMERIC(8,2) 
   READS SQL DATA 
   BEGIN 
      RETURN (SELECT PRICE FROM PARTS.ITEM WHERE ITEMNUM= P_ITEMNUM); 
   END

Example 3: Anonymizing a credit card number

The UDF mask_credit_card_number below anonymizes a credit card number by masking the last four digits:

CREATE FUNCTION mask_credit_card_number(IN card_no CHAR(16)) 
   RETURNS CHAR(16)
   CONTAINS SQL
   RETURN SUBSTRING(card_no FROM 1 FOR 12) || '****'

A notification could thus be structured as follows:

Select surname, first_name, mask_credit_card_number(credit_card_number)

from ...