SQL Bible, Second Edition

Chapter 10. SQL Functions

SQL functions exist to make your life easier, and — to some extent — alleviate the procedural deficiency of set-based SQL. While a query is busy retrieving some data for you, the functions used within that query are validating, converting, calculating, getting the system information, and much more.

Think of the SQL functions as tools designed to accomplish a single well-defined task — calculating a square root or converting lowercase letters into uppercase, for example. You invoke a function within an SQL query by its name (usually a single keyword), and it performs its action and returns a value for every row returned by the query. Some functions accept arguments and some do not, but what differentiates a function from every other executable module in an RDBMS is that it always returns a value.

We have mentioned before that SQL itself is not a procedural language (at least without SQL/PSM). It lacks procedural features such as flow control structures and loops, but using functions allows you to compensate for problems stemming from this deficiency.

While classifications of the SQL functions abound, all functions could be divided into two broad categories: deterministic functions and nondeterministic functions. Deterministic functions always return the same result as long as you pass in the same arguments; nondeterministic functions might return different results, even if they are called with exactly the same arguments. For example, the function ABS, which ...