PL/SQL

PL/SQL is Oracle's Procedural Language extension to SQL. It, too, usually runs on the database server, but some Oracle products such as Developer/2000 also contain a PL/SQL engine that resides on the client. Thus, you can run your PL/SQL code on either the client or the server depending on which is more appropriate for the task at hand. Unlike SQL,
PL/SQL is procedural, not declarative. This means that your code specifies exactly how things get done. As in SQL, however, you need some way to send your PL/SQL code up to the server for execution. PL/SQL also enables you to embed SQL statements within its procedural code. This tight-knit relationship between PL/SQL, SQL, and SQL*Plus is the cause for some of the confusion between the products.

SQL*Plus

SQL*Plus is an interactive program that allows you to type in and execute SQL statements. It also enables you to type in and execute PL/SQL code and is one of the most common front ends used to develop and create stored PL/SQL procedures and functions.

What happens when you run SQL*Plus and type in a SQL statement? Where does the processing take place? What exactly does SQL*Plus do, and what does the database do? If you are in a Windows environment and you have a database server somewhere on the network, the following things happen:

1. SQL*Plus transmits your SQL query over the network to the database server.
2. SQL*Plus waits for a reply from the database server.
3. The database server executes the query and transmits the results back to SQL*Plus.
4. SQL*Plus displays the query results on your computer screen.

Even if you're not running in a networked Windows environment, the same things happen. The only difference might be that the database server and SQL*Plus are running on the same physical machine. This would be true, for example, if you were running Personal Oracle on a single PC.

PL/SQL is executed in much the same manner. Type a PL/SQL block into SQL*Plus, and it is transmitted to the database server for execution. If there are any SQL statements in the PL/SQL code, they are sent to the server's SQL engine for execution, and the results are returned back to the PL/SQL program.

The important thing is that SQL*Plus does not execute your SQL queries. SQL*Plus also does not execute your PL/SQL code. SQL*Plus simply serves as your window into the Oracle database, which is where the real action takes place. Figure 1.1 illustrates this relationship.

Figure 1.1.
Relationship of
SQL*Plus, PL/SQL,
and Oracle.

Several other tools besides SQL*Plus can serve as your window to the database. SQL*DBA and its recent replacement, Server Manager, also enable you to execute SQL queries and
PL/SQL code. Oracle has a product named Procedure Builder, which is specifically designed for use in the creation and debugging of PL/SQL procedures and functions. In addition, there is a growing list of tools from other vendors that are designed to help developers create SQL queries and stored database procedures.

SQL*Plus is used for most of the examples in this book because of its almost universal availability to developers. It is perhaps still the most widely used tool to develop, test, and create PL/SQL stored subprograms and SQL queries.

What You Need to Finish This Book

In order to try the examples and complete the exercises in this book, you will need access to

• An Oracle7 or later database
• SQL*Plus

If you do not currently have access to an Oracle database, you can obtain a 60-day evaluation version of Personal Oracle from Oracle Corporation. You'll find details on how to obtain this later in this chapter in the section "How to Obtain Personal Oracle."

You will need these database privileges:

• CREATE PROCEDURE
• CREATE SEQUENCE
• CREATE SESSION
• CREATE TABLE
• CREATE TRIGGER
• CREATE VIEW
• CREATE TYPE (necessary for Oracle8)

The following Oracle-supplied packages should be available:

• DBMS_OUTPUT
• DBMS_SQL
• UTL_FILE
• DBMS_PIPE
• DBMS_ALERT

Your database administrator can help you verify that these packages are available to you. Of the preceding packages, the DBMS_OUTPUT is the most essential and is used throughout most of the exercises and examples to display results. The other packages are discussed only in specific chapters.

How to Obtain Personal Oracle

Oracle Corporation currently offers a 60-day trial version of Personal Oracle, which is a version of Oracle designed for use by a single user on a desktop PC. If you have a fast Internet connection, you can download it from Oracle's Web site at the following address:

www.oracle.com

If you prefer, you can also order it on CD-ROM from the Oracle Store. Phone 1-800-ORACLE1 for more information.

Getting Started with PL/SQL

By now you should have a basic understanding of what PL/SQL is and how it relates to other Oracle products. You should have access to an Oracle database environment either at work or at home. During the rest of this chapter, you will learn some of the basics of PL/SQL, and you will write your first Oracle stored function.

PL/SQL Is Block Structured

NEW TERM
PL/SQL is referred to as a block structured language. A PL/SQL block is a syntactical unit that might contain program code, variable declarations, error handlers, procedures, functions, and even other PL/SQL blocks.