O'Reilly logo

Java Programming with Oracle SQLJ by Jason Price

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required