Chapter 16. SQL and Procedural Programming

As you already know, SQL is a nonprocedural language. In the previous chapters, you learned how to create procedural programs using proprietary SQL procedural extensions. These programs (stored procedures, user-defined functions, triggers, and so on) are stored inside RDBMS. This approach, although very popular since the 1990s, was not the first attempt to empower SQL with procedural language capabilities. The idea of embedded SQL arose long before the SQL procedural extensions were developed. It was introduced by IBM in the beginning of the 1980s and then implemented by many other SQL vendors. Dynamic SQL was the logical continuation of the embedded SQL principles that alleviated some limitations and inconveniences of the latter.

Before a single SQL statement can execute, a client application that submits this statement must somehow establish connection with RDBMS. The connection can be established through a variety of mechanisms — ranging from proprietary call-level interface to Java Database Connectivity (JDBC) and ADO.NET.

While not in the domain of SQL proper, application programming interface concepts should be understood by anyone serious enough to go to the trouble of learning RDBMS and their native SQLs.

SQL Statement Processing Steps

The internal mechanisms of SQL statement processing are different for each RDBMS, but generally, the following five steps are needed to process an SQL statement:

  1. The RDBMS parses the SQL statement by breaking ...

Get SQL Bible, Second Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.