Java Programming with Oracle SQLJ

2.3. PL/SQL

PL/SQL is Oracle's proprietary procedural language that was developed to add programming constructs around SQL. PL/SQL contains standard programming constructs such as the following:

• Blocks

• Variable declarations

• Conditionals

• Loops

• Cursors

• The ability to define procedures and functions

PL/SQL is primarily used for adding procedures and functions to a database, and in Chapter 3 you will see how to call PL/SQL procedures and functions from your SQLJ programs.

2.3.1. PL/SQL Program Structure

Like most programs written in third-generation programming languages, PL/SQL programs are divided up into blocks, with each block encapsulating a set of PL/SQL program statements. A PL/SQL block has the following structure:

[DECLARE
     declaration_statements
]
BEGIN
  executable_statements
[EXCEPTION
  exception_handling_statements
]
END;

The syntax elements are as follows:

declaration_statements

The declarations for the variables used by the block.

executable_statements

The executable statements for the block. These statements implement the business logic.

exception_handling_statements

The statements that handle errors generated by the executable statements.

A block must be terminated using the END keyword. The following is a simple example of a PL/SQL block, which declares an integer variable named x and initializes x to 10 in the executable section:

DECLARE
  x INTEGER;
BEGIN
  x := 10;
END;
/

An EXCEPTION block allows you to trap errors. In the following example, an attempt to ...