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
- Read SQL functions.
- Read Features of SQL functions.
- Ensure that you have the privileges to run the CREATE FUNCTION (scalar) statement.
About this task
Procedure
Results
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@