Introduction of PL/SQL

PL/SQL (Procedural Language/SQL) is a procedural extension of Oracle-SQL that offers language
constructs similar to those in imperative programming languages. PL/SQL allows users and designers to develop complex database applications that require the usage of control structures and procedural elements such as procedures, functions, and modules.

The basic construct in PL/SQL is a block. Blocks allow designers to combine logically related
(SQL-) statements into units. In a block, constants and variables can be declared, and variables
can be used to store query results. Statements in a PL/SQL block include SQL statements,
control structures (loops), condition statements (if-then-else), exception handling, and calls of
other PL/SQL blocks.

PL/SQL blocks that specify procedures and functions can be grouped into packages. A package
is similar to a module and has an interface and an implementation part. Oracle offers several
predefined packages, for example, input/output routines, file handling, job scheduling etc. (see
directory $ORACLE HOME/rdbms/admin).

Another important feature of PL/SQL is that it offers a mechanism to process query results
in a tuple-oriented way, that is, one tuple at a time. For this, cursors are used. A cursor basically is a pointer to a query result and is used to read attribute values of selected tuples into variables. A cursor typically is used in combination with a loop construct such that each tuple read by the cursor can be processed individually.

In summary, the major goals of PL/SQL are to

• increase the expressiveness of SQL,
• process query results in a tuple-oriented way,
• optimize combined SQL statements,
• develop modular database application programs,
• reuse program code, and
• reduce the cost for maintaining and changing applications.