Working with PL/SQL
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 characters ”a 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 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.
