We said in Chapter 1, "Facilis Descensus Averni," that we assume you're already familiar with (among other things) stored procedures, but here's a quick summary of syntax to refresh your memory.
Informix calls it Stored Procedure Language (SPL); Sybase and Microsoft call it Transact-SQL; Oracle calls it Procedure Language extensions to SQL (PL/SQL); the SQL Standard refers to Persistent Stored Modules (PSM). All these names refer to the same thing. It's easy to see this if you write the same stored procedure in several dialects and put the statements in columns side by side, with each syntax element occupying one row. We've taken the stored procedure declaration shown in Listing 11-1 and done this; the result is shown in Table 11-1.
Although no two columns of Table 11-1 are exactly alike, the important thing the table shows is how similar the statements are to one another, and to the SQL Standard. For example, if your background is Microsoft/Sybase, you just have to adapt to a few differences: Parameter and variable names do not begin with @; blocked statements are terminated explicitly (for example, IF END IF) as in Ada; the parameter list must be inside parentheses; semicolons are statement separators. Those are just details. We're confident that you'll be able to read our standard SQL PSM syntax examples regardless of your prior experience.
Table 11-1. Listing 11-1's Stored Procedure in Four SQL Dialects
A function is deterministic if it always generates the same outputs, given the same inputs. For example, the SQL Standard built-in function UPPER is deterministic if UPPER('i') always returns I . Notice that there are two inputs here: the explicit argument 'i' and the constant environment setting 'code page = Western' . (If the code page were Turkish, the result of UPPER('i') would not be I .)
In contrast, a function is nondeterministic if it's possible that it might generate different outputs each time it is run, even if the inputs are always the same. This user -defined function is nondeterministic:
CREATE FUNCTION Sp_non_deterministic () RETURNS INTEGER BEGIN IF CURRENT_TIME = TIME '11:00:00' THEN RETURN 1; ELSE RETURN 2; END IF; END
Function Sp_non_deterministic will return a different result depending on an input value that is not determinable until execution, namely the time of day.
Nondeterministic functions are bad.
Nondeterminism is a long name but a fairly simple idea. Clearly, you want to make sure your functions are deterministic, and you want to declare to the DBMS that they are deterministic. The problem area is external functions. They often depend on hidden factors (like the existence of a file) that the DBMS cannot detect.