An Extremely Brief Introduction to PL/SQL

This article was modified from its original published form. The most recent modification was on2014-09-25.

PL/SQL is Oracle’s “procedural language extensions to SQL”. It is embedded into Oracle server products and client development tools. It is modeled on Ada, although anyone with any familiarity with Pascal will find it easy to understand.

The language offers most of the features one would expect in a modern procedural language: modularity, loops, conditionals, exceptions, native and user-definable data types (including collection types), and native handling of SQL queries and cursors. The PL/SQL engine is responsible for memory management with no option for user defined memory management; there are no pointers or references of any sort. PL/SQL is block-oriented (all code is organized between BEGIN and END statements) and offers both anonymous blocks and named blocks, which are procedures (may not return a value) and functions (must return a value). Parameters to procedures or functions are passed by value, not by reference (PL/SQL 8 offers a NOCOPY keyword allowing pass by reference parameters).

Although PL/SQL isn’t an object-oriented language, it encourages encapsulation through packages. A package is written in two pieces: the package definition that defines the prototypes of the public modules and public data object definitions, and the package body that defines the implementation of the package, including private modules, variable types, and variables. PL/SQL supports exception handling in named and anonymous blocks, and allows the programmer to define exceptions and associate these with error numbers. Several of the common Oracle errors are given names, and exceptions can be defined within packages (if they are defined in the public portion of the package, then they can be referenced just as a package procedure would be referenced).

PL/SQL supports all of the Oracle built-in types, plus a few for PL/SQL only (BOOLEAN, PLS_INTEGER, BINARY_INTEGER, others). Complex data types can be created as records or, in Oracle 8 and later, database object types. Records can be created from references to tables or as an explicit declaration. Collections can be created from native data types, database object types, or records, but may not contain (directly or indirectly) other collections.

Two of the collection types (varying arrays and nested tables) are based on Oracle 8 object facilities and provide the best flexibility when defined as objects in the schema, as they can provide additional capabilities with CAST … AS statements. The remaining collection type, index-by tables, offers a sparse collection, where the entries do not need to be contiguous and allow for smart indexes. (Entries that have not been set in index-by tables do not exist and have no value, not even NULL, meaning that “random” index selection should test that the value EXISTS before attempting to get a value.)

The performance of PL/SQL is very good on certain sets of tasks, sometimes rivalling compiled C and C++. PL/SQL performs very well on tasks that involve data manipulation, and does not perform as well as tasks that involve significant calculations. Even with these limitations, PL/SQL’s flexibility, ease of use, and portability can make it an ideal choice for wide ranges of business logic and other functionality within Oracle products.

  • 2014-09-25: Various specification errors have been fixed.[ back ]

More Reading
Older// Ansible