Learning the Basics of PL/SQL

June 4, 2001

Congratulations on your decision to read this book, Sams Teach Yourself PL/SQL in 21 Days, Second Edition! If you are new to the Oracle environment, this book will help you learn and master Oracle's built-in procedural language quickly. Knowledge of PL/SQL (Procedural Language/Structured Query Language) is becoming a fundamental necessity no matter which of Oracle's many products you use.

Today, on your first day, you will accomplish these tasks:

  • Learn what PL/SQL is and why you should master it

  • Learn how PL/SQL relates to other Oracle products

  • Learn what resources you need to finish this book

  • Write your first PL/SQL function

Over the remaining 20 days, you'll delve deeper into the power and capabilities of this language and learn how to leverage its power in your applications regardless of whether you are doing client/server programming with Oracle's tools (such as Developer/2000), using other front-end tools (such as PowerBuilder), or simply writing some batch jobs that run on the server.

What Is PL/SQL?

PL/SQL is a procedural language that Oracle developed as an extension to standard SQL to provide a way to execute procedural logic on the database.


New Term - If you have worked with relational databases in the past, you are no doubt familiar with SQL, which stands for Structured Query Language. SQL itself is a powerful declarative language. It is declarative in the sense that you describe the results that you want but not how they are obtained. This is good because you can insulate an application from the specifics of how the data is physically stored. A competent SQL programmer can also push a great deal of processing work back to the server level through the creative use of SQL.


There are limits, though, to what you can accomplish with a single declarative query. The real world is seldom as neat and clean as we would like it to be. Developers often find themselves needing to execute several queries in succession and process the specific results of one query before going on to the next. This leads to two problems in a client/server environment:

  • The procedural logic, that is, the definition of the process, resides on client machines.

  • The need to look at the data from one query and use it as the basis for the next query results in an increased amount of network traffic.

Why are these problems? The procedural logic on client machines can quickly become out of sync if the software is upgraded. It can also be implemented incorrectly, resulting in a loss of database integrity. The need to pull down large amounts of intermediate data to a client results in a long wait for the end users who must sit there staring at the hourglass while the data is transferred to their machines. The cumulative effects of a number of clients pulling large amounts of data across the network further decrease performance.

PL/SQL provides a mechanism for developers to add a procedural component at the server level. It has been enhanced to the point where developers now have access to all the features of a full-featured procedural language at the server level. It also forms the basis for programming in Oracle's continually evolving set of client/server development tools, most notably Developer/2000.

Why Learn PL/SQL?

If you are developing with Oracle products, Developer/2000 for example, the answer to this question is simple. You need to know PL/SQL because those products use PL/SQL for any procedural code. But what if you don't develop with Oracle's products? What if all you use is Oracle's database engine? Is PL/SQL of any use to you? Yes! Absolutely it is.

Regardless of the front-end tool that you are using, you can use PL/SQL to perform processing on the server rather than the client. You can use PL/SQL to encapsulate business rules and other complicated logic. It provides for modularity and abstraction. You can use it in database triggers to code complex constraints, which enforce database integrity; to log changes; and to replicate data. PL/SQL can also be used with stored procedures and functions to provide enhanced database security. Finally, it provides you with a level of platform independence. Oracle is implemented on many hardware platforms, but PL/SQL is the same on all of them. It makes no difference whether you are running Personal Oracle on a laptop or Oracle8i Enterprise on UNIX.

Regardless of what development tools you use, if you are developing in an Oracle environment, your knowledge of PL/SQL and your ability to apply it will give you a competitive advantage against those who do not have that knowledge. With PL/SQL you have the power to make your applications more robust, more efficient, and more secure.

SQL, SQL*Plus, PL/SQL: What's the Difference?

This question has bedeviled many people new to Oracle. There are several products with the letters "SQL" in the title, and these three, SQL*Plus, SQL, and PL/SQL, are often used together. Because of this, it's easy to become confused as to which product is doing the work and where the work is being done. This section briefly describes each of these three products.

SQL

SQL stands for Structured Query Language. This has become the lingua franca of database access languages. It has been adopted by the International Standards Organization (ISO) and has also been adopted by the American National Standards Institute (ANSI). When you code statements such as SELECT, INSERT, UPDATE, and DELETE, SQL is the language you are using. It is a declarative language and is always executed on the database server. Often you will find yourself coding SQL statements in a development tool, such as PowerBuilder or Visual Basic, but at runtime those statements are sent to the server for execution.

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 PL/SQL code and send it to the server to be executed. SQL*Plus 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.

The relationship between SQL*Plus, PL/SQL, and Oracle
Relationship of SQL*Plus, PL/SQL, and Oracle.

Several other tools besides SQL*Plus can serve as your window to the database. Server Manager, which has an interface similar to SQL*Plus, is one such tool, although Oracle plans to stop supporting it sometime in the future. If you have Oracle Enterprise Manager installed, you should take a look at SQLPlus Worksheet. SQLPlus Worksheet is a GUI tool that is fully compatible with SQL*Plus but is much easier to use. If you are a Developer 2000 programmer, you'll have access to Oracle's Procedure Builder--a tool designed for developing and debugging PL/SQL code. You'll read more about SQLPlus Worksheet and Procedure Builder later in this chapter.

SQL*Plus is used for most of the examples in this book because of its 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.


Note - In addition to Oracle's tools, several third-party vendors also have tools that can be used to develop PL/SQL code. Some of the major products in this space are


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 Oracle8i database (the Personal Edition will work)

  • SQL*Plus or SQLPlus worksheet


Note - Where possible, the exercises and examples in this book have been designed to run equally well under both Oracle8 and Oracle8i. Many, especially those in the first nine days, will even run under Oracle7. However, Oracle8i contains many new features that are not available in previous releases. Days 10, 11, 12, 20, and 21, in particular, are heavily focused on the new 8i features.


If you do not currently have access to an Oracle database, there are at least two ways to get your hands on one. For a nominal cost, you can visit Oracle's online store and purchase a 30-day evaluation version of almost any Oracle product, including the database. You can get to the online Oracle Store from Oracle's home page, http://www.oracle.com. Another option is to join the Oracle Technology Network (OTN). OTN members can download developer-licensed copies of Oracle's database software at no charge. OTN members also have the option of subscribing to various technology tracks in order to get regular shipments of Oracle software CDs. You can register as an OTN member at no cost. The URL to visit is http://technet.oracle.com.

You will need these database privileges roles:

  • CREATE PROCEDURE

  • CREATE SEQUENCE

  • CREATE SESSION

  • CREATE TABLE

  • CREATE TRIGGER

  • CREATE VIEW

  • CREATE TYPE

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.

If you are using Oracle8i Personal Edition, you can verify the existence of these packages by logging on as the user SYSTEM and issuing the following query:

SELECT object_name FROM dba_objects WHERE owner='SYS' AND object_type = 'PACKAGE';

The resulting list will show you all packages in the database owned by the user SYS. The packages named in this chapter should be in that list. Of those, 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.


Caution - I recommend that you do not use a production database and that you create the sample tables in a schema that is not shared with other users. If you are using Personal Oracle on your own PC, you won't have a problem with this. If you are using an employer's facilities, you might want to discuss use of the database with your employer's database administrator, or DBA, as they are often called. There is nothing inherently dangerous in any of the exercises or examples, but there is always the risk that a coding mistake, such as an infinite loop, might tie up CPU or I/O resources. It's always good etiquette to minimize the potential impact of your mistakes on other developers and end users.









The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers