Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

2.5 Working with PL/SQL

PL/SQL is
a programming language developed by Oracle as an extension to SQL 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 , Third Edition
(O'Reilly). PL/SQL opens up a world of
possibilities. You'll want to take advantage of it
if you are doing serious work with Oracle.

2.5.1 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
Example 2-5 shows a reasonably simple, but complete,
PL/SQL block.

Example 2-5. "Hello World!" written in PL/SQL

DECLARE
X VARCHAR2(12) := 'Hello World!';
BEGIN
DBMS_OUTPUT.PUT_LINE(X);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred.');
END;

This code contains all the elements of a PL/SQL block and is one
implementation of the traditional "Hello
World!" program. Using SQL*Plus, you can send it to
the database for execution.

2.5.2 Executing a PL/SQL Block

To
execute a PL/SQL block, you type it into SQL*Plus and terminate it
with a forward slash. The forward slash tells SQL*Plus that you are
done entering the block and to send it to the database for execution.
Example 2-6 shows how it would look to enter and
execute the block from Example 2-5.

Example 2-6. Executing a PL/SQL block

SQL> DECLARE
2 X VARCHAR2(12) := 'Hello World!';
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(X);
5 EXCEPTION
6 WHEN OTHERS THEN
7 DBMS_OUTPUT.PUT_LINE('An error occurred.');
8 END;
9 /
PL/SQL procedure successfully completed.

2.5.2.1 Where's the output?

You may be wondering why there was no output from the code block in
Example 2-6. After all, the code does contain a call
to the
PUT_LINE
procedure that sure looks as if it ought to display something.

In fact, the code did generate some output. You just
didn't see it. Remember from Chapter 1 that
SQL*Plus itself does not execute PL/SQL
code. It simply sends that code to the database server, which
executes the code for you. The Oracle database server
doesn't have any way to display the output for you
to see. Instead, any output from PL/SQL code is buffered by the
server for later retrieval by the application that executed it, in
this case SQL*Plus.

By default, SQL*Plus doesn't retrieve PL/SQL output
from the server. You have to tell it to retrieve the output if you
want to see it. Here is the command:

SET SERVEROUTPUT ON If you enter this command, followed by the same PL/SQL block that you
entered earlier, your output will look like that shown in Example 2-7.

Example 2-7. Enabling the display of DBMS_OUTPUT

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 X VARCHAR2(12) := 'Hello World!';
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(X);
5 EXCEPTION
6 WHEN OTHERS THEN
7 DBMS_OUTPUT.PUT_LINE('An error occurred.');
8 END;
9 /
Hello World!
PL/SQL procedure successfully completed.

This time around, you do see the output from the block. The
SERVEROUTPUT setting "sticks" for
the duration of your SQL*Plus session, so you don't
have to keep turning it on each time you execute another block. There
are some other parameters to the SET SERVEROUTPUT command that affect
formatting and the output buffer size. The:SIZE parameter lets you increase
the buffer size from the default of 2,000 bytes, something you should
do if you expect to display a lot of information from PL/SQL. The
FORMAT parameter lets you control whether, and
how, long lines of output are wrapped when they are displayed. The
following example shows how you can turn server output on, allow for
a maximum of 1,000,000 bytes to be displayed, and word-wrap any long
lines.

SET SERVEROUTPUT ON SIZE 1000000 FORMAT WORD_WRAPPED Prior to Version 8 of SQL*Plus, the SIZE and FORMAT parameters did
not exist. To increase the buffer size, you had to make a call to
DBMS_OUTPUT.ENABLE. Example 2-8, in the section
Section 2.5.3,
shows how that's done.

2.5.2.2 Rules for entering PL/SQL blocks

When you begin entering a PL/SQL block,
SQL*Plus switches to
what is called PL/SQL
mode . It knows to do this by watching for the keywords
BEGIN and DECLARE, either of which may start a PL/SQL block. Once in
PL/SQL mode, you can pretty much type anything you please. SQL*Plus
simply buffers everything you type until you terminate PL/SQL mode by
typing one of the termination charactersa forward slash or a
period on a line by itself. Parsing and syntax checking of your
PL/SQL code is done by the database server, not by SQL*Plus> and
doesn't happen until after you have completely
entered and terminated the block.

The following SQL statements put you into PL/SQL mode: CREATE PROCEDURE, CREATE FUNCTION, CREATE TRIGGER, CREATE PACKAGE, CREATE PACKAGE BODY, CREATE TYPE, CREATE TYPE BODY, and the CREATE OR REPLACE versions of each of these. That's because these statements allow you to define stored objects based on PL/SQL code.

The rules for entering a PL/SQL block are as follows:

The first word of a PL/SQL block must be BEGIN, DECLARE, CREATE
PROCEDURE, CREATE FUNCTION, CREATE TRIGGER, CREATE PACKAGE, CREATE
TYPE, or CREATE TYPE BODY. Lowercase is acceptable; PL/SQL is not
case-sensitive.

PL/SQL blocks may span multiple lines.

Line breaks may occur anywhere you can legally enter whitespace.

Comments, delimited by /*...*/, may be embedded anywhere whitespace
is allowed. These commands may span multiple lines.

A double hyphen () makes everything after it on the same line
a comment.

Blank lines are allowed in a PL/SQL block.

Entry of a PL/SQL block must be terminated in one of two ways:

Using the forward slash character. The forward slash must be on a
line by itself, and must be in column one of that line. Using a
forward slash tells SQL*Plus to execute the block you have just
entered.

Using a period. The period must be on a line by itself, and
in the first position. Using a period causes the statement to be
stored in the SQL buffer rather than be executed immediately.

Because blank lines are allowed within a block of code, they
can't be used to terminate a block.
That's where the period comes into play. Just as you
can enter a SQL statement into the buffer without executing it, so
you also need a way to enter a PL/SQL block into the buffer without
executing it. Because a blank line can't be used for
that purpose, as it can be with an SQL statement, Oracle decided to
allow the period on a line by itself to serve this function.

Likewise, because a PL/SQL block may be made up of many statements,
each of which itself ends with a semicolon, that character cannot
reliably be used as a termination character. So, to enter and execute
a block, you are left with only the forward slash.

2.5.3 Executing a Single PL/SQL Statement

If you wish to execute a single
PL/SQL
statement, you can use the SQL*Plus EXECUTE
command rather than write an entire block. The syntax for EXECUTE is:

EXECUTE plsql_statement EXECUTE is most helpful when you want to make a quick call to a
PL/SQL function. Example 2-8 shows EXECUTE being
used to make a call to DBMS_OUTPUT.ENABLE, in order to allow more
than the default 2000 bytes of PL/SQL output to be displayed.

Example 2-8. EXECUTE allows you to execute a single PL/SQL statement

SQL> EXECUTE DBMS_OUTPUT.ENABLE(10000)
PL/SQL procedure successfully completed.

The value of 10,000 in this example tells Oracle to allow for up to
10,000 bytes of output to be displayed by the

DBMS_OUTPUT.PUT_LINE
procedure. The EXECUTE

command is nothing more than an SQL*Plus shortcut. SQL*Plus takes
whatever text you type after EXECUTE, adds a semicolon to the end,
wraps the keywords BEGIN and END around it, and sends it to Oracle as
just another PL/SQL block.