Using SQL for Complex Business Logic Is a Bad Idea

With correct use of PL/SQL, you avoid mixing your application code with SQL. This also helps you to keep away from the knotty area of trying to express complex business logic in SQL scripts. SQL is not nearly as strong as PL/SQL in the area of error/exception handling. A small incoming data change can easily cause a SQL script to suddenly fail with little or no indication of the cause.

The use of SQL for business logic can produce extremely brittle solutions. I think it’s fair to view the use of SQL for business logic as something of an antipattern.

An example of this type of SQL is a multistep SQL script that attempts to merge data from a number of sources, while at the same time executing one or more complex joins and updates on other tables. This type of SQL construct can tend to grow over time as additions are made to accommodate new features and data changes. The end result can be unnecessary complexity.

As the SQL script grows over time, so too does the tendency for it to fail when, for example, someone attempts to insert rows into a table with the same primary key. This type of failed insertion may cause the whole SQL block to fail unexpectedly. In many cases, the failure is not noticed or even logged in the overall workflow.

PL/SQL helps avoid the antipattern by facilitating modular, procedural code that includes powerful exception handling.

A Cautionary Tale

I once saw a case where a large block of SQL was failing in a daily batch system (see Figure 1-1). The batch job was responsible for merging sales data from a range of source tables and views across a number of databases and other source systems. The end result was a report that was generated for use by a downstream finance team. The SQL was rather badly designed in that it was written to not expect any errors to occur.

Figure 1-1. An antipattern: big block of SQL with business logic and no error handling

Notice, at the top of Figure 1-1, the insertion of a PL/SQL BEGIN statement and a corresponding END with a COMMIT. In this context, the use of PL/SQL is just a mechanism for running the contained SQL script. I’ll look more closely at this as we explore the later examples, but the point is that while the example uses PL/SQL, it doesn’t really take advantage of the many benefits of the language. The intention was good but the execution left a lot to be desired.