Summary

This chapter introduces the key components of the SQL procedural languages, for which the main purpose is to amend deficiencies of non-procedural nature of SQL proper. The ISO/ANSI standard provides for the existence of the RDBMS-stored modules for data processing without specifying what language should be used for the implementation. Left to their own devices, vendors have implemented it in a variety of ways — from their own proprietary SQL extensions like PL/SQL (Oracle), Transact-SQL (Microsoft SQL Server and Sybase), TSQL or IBM SQL (IBM DB2 UDB), to Java, Cobol, and Visual Basic (Data Transformation Services).

Programming RDBMS using these languages does not differ much from creating programs using any other programming language, with the notable exception that these SQL extensions are database-oriented, and include many specific constructs for specific tasks (like cursors, for row-by-row data manipulation).

The procedural SQL programs can be compiled into a variety of the module types: stored procedures, user-defined functions, and triggers. All these might differ in flavor, with various invocation methods and so on, but the general principles remain the same: the module is compiled and stored server-side using either a proprietary SQL extension language or a general-purpose language (like Java); the module includes constructs of the procedural language and SQL statements, and is executed within RDBMS environment.

The procedural languages are not part of the SQL standard (though persistent modules are mandated), and some popular SQL products did not even have this capability until very recently.