The central concept of dynamic SQL is simple: don’t hard-code an embedded SQL statement into the program’s source code. Instead, let the program build the text of a SQL statement in one of its data areas at runtime. The program then passes the statement text to the DBMS for execution on the fly. Although the details get quite complex, all of dynamic SQL is built on this simple concept, and it’s a good idea to keep it in mind.
To understand dynamic SQL and how it compares with static SQL, it’s useful to consider once again the process the DBMS goes through to execute a SQL statement, originally shown in Figure 17-1 and repeated here in Figure 18-1. Recall from Chapter 17 that a static SQL statement goes through the first four steps of the process at compile-time. The BIND utility (or the equivalent part of the DBMS runtime system) analyzes the SQL statement, determines the best way to carry it out, and stores the application plan for the statement in the database as part of the program development process. When the static SQL statement is executed at runtime, the DBMS simply executes the stored application plan.
In dynamic SQL, the situation is quite different. The SQL statement to be executed isn’t known until runtime, so the DBMS cannot prepare for the statement in advance. When the program is actually executed, the DBMS receives the text of the statement to be dynamically executed (called the statement string) and goes through all five of the steps shown in Figure 18-1 at runtime.
As you might expect, dynamic SQL is less efficient than static SQL. For this reason, static SQL is used whenever possible, and many application programmers never need to learn about dynamic SQL. However, dynamic SQL has grown in importance as more and more database access has moved to a client/server, front-end/back-end architecture over the last ten years. Database access from within personal computer applications such as spreadsheets and word processors has grown dramatically, and an entire set of PC-based front-end data entry and data access tools has emerged. All of these applications require the features of dynamic SQL.
More recently, the emergence of Internet-based three-tier architectures—with applications logic executing on one (mid-tier) system and the database logic executing on another (back-end) system—have added new importance to capabilities that have grown out of dynamic SQL. In most of these three-tier environments, the applications logic running in the middle tier is quite dynamic. It must be changed frequently to respond to new business conditions and to implement new business rules. This frequently changing environment is at odds with the very tight coupling of applications programs and database contents implied by static SQL. As a result, most three-tier architectures use a callable SQL API (described in Chapter 19) to link the middle tier to back-end databases. These APIs explicitly borrow the key concepts of dynamic SQL (for example, separate PREPARE and EXECUTE steps and the EXECUTE IMMEDIATE capability) to provide their database access. A solid understanding of dynamic SQL concepts is thus important to help a programmer understand what’s going on behind the scenes of the SQL API. In performance-sensitive applications, this understanding can make all the difference between an application design that provides good performance and response times and one that does not.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.