Section 2.13. Program Logic into Queries

2.13. Program Logic into Queries

There are several ways to achieve procedural logic in a database application. It's possible to put some degree of procedurality inside an SQL statement (even if a statement should say what, and not how). Even when using a well-integrated host language within which SQL statements are embedded, it is still preferable to embed as much procedural logic as possible within an actual SQL statement, rather than in the host language. Of the two alternatives, embedding logic in the SQL statement will yield higher performance than embedding it in the application. Procedural languages are characterized by the ability to iterate (loops ) and to perform conditional logic (if...then...else constructs). SQL doesn't need looping, since by essence it operates on sets; all it requires is the ability to test logically for some conditions.

Obtaining conditional logic breaks down into two componentsIF and ELSE. Achieving IF is easy enoughthe where condition provides the capability. What is difficult is to obtain the ELSE logic. For example, we may need to retrieve a set of rows, and then apply different transformations to different subsets. The case expression (Oracle has also long provided a functionally equivalent operator in decode( )^[*]) makes it easy to simulate some logic: it allows us to change on the fly the values that are returned to the result set by testing on row values. In pseudocode, the case construct operates like this:^[]

^[] There are two variants of the case construct; the example shown is the most sophisticated variant.

     CASE     WHEN condition THEN <return something to the result set>        WHEN condition THEN <return something else>     ...        WHEN condition THEN <return still something else>        ELSE <fall back on this value>     END

Comparing numerical values or dates is straightforward. With strings, functions such as Oracle's greatest( ) or least( ) or MySQL's strcmp( ) can be useful. It is also sometimes possible to add some logic to insert statements, through multiple table inserts and conditional inserts,^[*] and by using the merge statement. Don't hesitate to use such statements if they are available with your DBMS. In other words, a lot of logic can be pushed into SQL statements; although the benefit may be small when executing only one of several statements, the gain can be much greater if you can manage to use case or merge or similar functionality to combine several statements into one.

^[*] Available, for instance, in Oracle since release 9.2.

Wherever possible, try to embed your conditional logic within your SQL statements rather than in an associated host language.