Discovering SQL: A Hands-On Guide for Beginners

SQL PROCEDURAL EXTENSIONS

If you are familiar with other programming languages, such as Java, C/C#, Visual Basic, PHP, or JavaScript, you will immediately notice the difference among any of these languages and set-based SQL.

The majority of general-purpose programming languages out there are procedural. A procedural program is essentially a list of step-by-step instructions that tell the computer what to do (for example, repeatedly read user's input, multiply it by some predefined constant, and store the result in a database table). A procedural program can be instructed to evaluate input and branch into different execution paths, depending on the outcome; it can recover from an error or use subroutines. Most importantly, it does not need an SQL engine to run.

The first RDBMS implementations did not have procedural language capabilities. All procedural database processing was done using embedded programming. All major procedural languages that were popular back then (C, COBOL, Pascal) had (and still have) special extensions (precompilers) that allowed the programmer to embed SQL statements directly into programming language code to be compiled together. The work of precompilers was to translate SQL into appropriate language constructs that could later be compiled into binary code.

As relational databases became increasingly sophisticated, and more internal control was delegated to RDBMSs, the idea arose to store procedural programming modules inside RDBMSs in compiled (binary) ...