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