Oracle SQL*Plus: The Definitive Guide

Working with PL/SQL

PL/SQL is a programming language developed by Oracle as an extension to SQL in order to allow procedural logic to be implemented at the database level. PL/SQL is used to write stored procedures, stored functions, and triggers, and, beginning with Oracle8, to define object types. It can also be used to simply write a block of procedural code for the database to execute. SQL*Plus was originally one of the only front-ends that could be used to send PL/SQL code to the database, and even today it is still one of the most widely used.

This section explains the mechanics of entering and executing PL/SQL code with SQL*Plus. You’ll learn what PL/SQL mode is, and you’ll learn the differences between entering a PL/SQL block and a SQL query.

If you are unfamiliar with PL/SQL, you may want to pick up a copy of Steven Feuerstein and Bill Pribyl’s book, Oracle PL/SQL Programming, second edition (O’Reilly & Associates, 1997). PL/SQL opens up a world of possibilities. You’ll want to take advantage of it if you are doing serious work with Oracle.

What Is a PL/SQL Block?

The PL/SQL block is the fundamental unit of PL/SQL programming. The term block refers to a program unit that contains some or all of the following elements:

• Variable and subprogram declarations

• Procedural code, which may include nested PL/SQL blocks

• An error handler

Here is an example of a reasonably simple, but complete, PL/SQL block:

DECLARE X VARCHAR2(12) := 'Hello World!'; BEGIN DBMS_OUTPUT.PUT_LINE(X); EXCEPTION ...