 |
|
PL/SQL Overview
Oracle Tips by
Burleson
|
The Procedural Language extension to SQL (PL/SQL) was first
introduced in late 1991 and dramatically reshaped the role of the
Oracle developer. PL/SQL literally made the impossible possible for
SQL developers; for the first time, they could develop complex
applications without using a 3GL program from inside Oracle*Forms.
PL/SQL introduced several abilities to the Oracle developer,
including:
-
Looping structures
-
The ability to embed SQL statements inside
PL/SQL code
-
IF-THEN-ELSE logic
-
The ability to deal with multiple rows of data
-
A robust method of handling errors
In its first incarnation, PL/SQL was used only inside Oracle*Forms
3.0 (in special blocks called procedures). Triggers inside a form
could execute both SQL DML statements and PL/SQL procedures (which in
turn could execute SQL DML statements and other PL/SQL procedures).
Today, PL/SQL has matured considerably, adding many new features and
becoming more tightly integrated with the Oracle database.
PL/SQL code runs in any Oracle database on any hardware platform,
making the code highly transportable. Applications developed for your
Personal Oracle database can easily be moved up to larger machines and
vice versa.
Blocks
PL/SQL is written in sections called blocks. Listing 2.10 shows the
structure of a typical PL/SQL block.
Listing 2.10 A sample PL/SQL block.
DECLARE
<variable declarations>
BEGIN
<statements>
EXCEPTION
<error condition>
<code for handling error>
END;
Blocks of PL/SQL code may be nested within each other to form
sub-blocks, as shown in Listing 2.11.
Listing 2.11 A sample PL/SQL block with a sub-block.
DECLARE
<variable declarations>
BEGIN
<statements>
DECLARE
<variable declarations>
BEGIN
<statements>
EXCEPTION
<error condition>
<code for handling error condition>
END;
<statements>
EXCEPTION
<error condition>
<code for handling error>
END;
In practice, there is no limit to how far PL/SQL blocks may be
nested within each other (although too many levels of indentation
become confusing).
The structure of a PL/SQL block is simple. The DECLARE
statement is followed by variable, constant, and other definitions.
You can then manipulate variables and data following the BEGIN
statement. The EXCEPTION statement (errors in PL/SQL are called
exceptions; this term originated within Ada, PL/SQL’s root
language) allows you to define code to handle specific error
conditions. The END statement signifies the end of the PL/SQL
block.
Not every PL/SQL block will have a DECLARE statement. This
is especially true of sub-blocks, because variables inside a block
cease to exist once the block is closed.
This is an
excerpt from the book "High Performance Oracle Database
Automation" by Jonathan Ingram and Donald K. Burleson, Series
Editor. |
