Using scalar user-defined functions

User-defined functions provide us with the ability to group a set of statements or instructions which can perform a task. A simple example would be a function that returns the sum of values. Functions can be designed to return a scalar value, a row, or a table. Similar to the stored procedures, user-defined functions can also be designed in SQL or any high-level programming language and they also accept input parameters and return the result. In this recipe, we will focus on the simplest form of functions, which is a scalar function. As the name suggests, a scalar function returns a scalar value. DB2 provides many scalar in-built functions like SUM(), AVG(), MAX(), MIN(), and so on. Scalar functions are commonly used for string manipulation, mathematical operations, and so on.

Getting ready

We need the following privileges to create a stored procedure:

• The CREATIN or IMPLICIT schema privilege, whichever is applicable

• The SELECT privileges on each...