Creating SQL scalar functions

Creating SQL scalar functions is a task that you would perform when designing a database or when developing applications. SOL scalar functions are generally created when there is an identifiable benefit in encapsulating a piece of reusable logic so that it can be referenced within SQL statements in multiple applications or within database objects.

Before you begin

Before you create an SQL function:

About this task


Restrictions

See: 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. Execute the CREATE FUNCTION (scalar) statement from a supported interface.

Results

The CREATE FUNCTION (scalar) statement should execute successfully and the scalar function should be created.

Example

Example 1
The following is 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 named price to hold the value to be returned until the function returns.

Example 2
The following example demonstrates a compiled SQL function definition containing 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

After creating the scalar function you might want to invoke the function to test it.