An Extremely Brief Introduction to PL/SQL
10 / Nov 2002This 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 ]