Creating SQL scalar functions

You create SQL scalar functions when you are designing a database or developing applications. SQL scalar functions are useful to have when there is an identifiable benefit to encapsulating a piece of reusable logic. These functions are called by SQL statements that are used within applications and database objects.

Before you begin

Before you create an SQL function, complete the following tasks:

About this task

Before you create an SQL scalar function, or when troubleshooting problems related to their use, be aware of the restrictions on SQL functions.

Procedure

  1. Define the CREATE FUNCTION (scalar) statement:
    1. Specify a name for the function.
    2. Specify a name and data type for each input parameter.
    3. Specify the RETURNS keyword and the data type of the scalar return value.
    4. Specify the BEGIN keyword to introduce the function-body.
      Note: Use of the BEGIN ATOMIC keyword is not recommended for new functions.
    5. Specify the function body. Specify the RETURN clause and a scalar return value or variable.
    6. Specify the END keyword.
  2. Run the CREATE FUNCTION (scalar) statement from a supported interface.

Results

The CREATE FUNCTION (scalar) statement runs and the scalar function is created.

Example

Example 1
An example of a compiled SQL function.
  CREATE FUNCTION GetPrice (Vendor CHAR(20), Pid INT)         
    RETURNS  DECIMAL(10,3) 
    LANGUAGE SQL  
    MODIFIES SQL
    BEGIN 
      DECLARE price DECIMAL(10,3); 

      IF Vendor = 'Vendor 1' 
        THEN SET price = (SELECT ProdPrice FROM V1Table WHERE Id = Pid); 
      ELSE IF Vendor = 'Vendor 2' 
        THEN SET price = (SELECT Price 
                          FROM V2Table 
    		 		 	  WHERE Pid = GetPrice.Pid); 
      END IF; 
  
    RETURN price; 
  END

This function takes in two input parameters and returns a single scalar value, conditionally based on the input parameter values. It requires the declaration and use of a local variable that is named price to hold the value to be returned until the function returns.

Example 2
An example of a compiled SQL function definition that contains a cursor, condition handler statement, and a REPEAT statement.
  CREATE FUNCTION exit_func(a INTEGER)
    SPECIFIC exit_func
    LANGUAGE SQL
    RETURNS INTEGER
    BEGIN 
      DECLARE val INTEGER DEFAULT 0;

      DECLARE myint INTEGER DEFAULT 0;

      DECLARE cur2 CURSOR FOR
        SELECT c2 FROM udfd1 
          WHERE c1 <= a 
          ORDER BY c1;

      DECLARE EXIT HANDLER FOR NOT FOUND
        BEGIN
          SIGNAL SQLSTATE '70001' 
          SET MESSAGE_TEXT = 
            'Exit handler for not found fired';
        END;

    OPEN cur2;

    REPEAT
      FETCH cur2 INTO val;
      SET myint = myint + val;
    UNTIL (myint >= a) 
    END REPEAT;

    CLOSE cur2;

    RETURN myint;

  END@

What to do next

Run the scalar function that you created to test it.