Oracle PL/SQL Programming | O'Reilly Media

Content preview·Buy reprint rights for this chapter

This chapter introduces PL/SQL, its origins, and its various versions. It also offers a guide to additional resources for PL/SQL developers and some words of advice.

PL/SQL stands for "Procedural Language extensions to the Structured Query Language."SQL is the now-ubiquitous language for both querying and updating—never mind the name—of relational databases. Oracle Corporation introduced PL/SQL to overcome some limitations in SQL and to provide a more complete programming solution for those who sought to build mission-critical applications to run against the Oracle database.

PL/SQL has several defining characteristics:

It is a highly structured, readable, and accessible language: Modeled after the Ada language, PL/SQL incorporates some of the latest and greatest in language design. If you are new to programming, PL/SQL is a great place to start. If you are experienced in other programming languages, you will very easily adapt to the new syntax. The accessibility of PL/SQL also means that you can write code that is easily maintained and enhanced over time, a critical aspect of high-quality software development.
PL/SQL is a standard and portable language for Oracle development: If you write a PL/SQL procedure or function to execute from within the Personal Oracle database sitting on your laptop, you can move that same procedure to a database on your corporate network and execute it there without any changes (assuming compatibility of Oracle versions, of course!). "Write once, run everywhere" was the mantra of PL/SQL long before Java appeared. For PL/SQL, though, "everywhere" means "everywhere there is an Oracle database."
PL/SQL is an embedded language

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

PL/SQL stands for "Procedural Language extensions to the Structured Query Language."SQL is the now-ubiquitous language for both querying and updating—never mind the name—of relational databases. Oracle Corporation introduced PL/SQL to overcome some limitations in SQL and to provide a more complete programming solution for those who sought to build mission-critical applications to run against the Oracle database.

PL/SQL has several defining characteristics:

It is a highly structured, readable, and accessible language: Modeled after the Ada language, PL/SQL incorporates some of the latest and greatest in language design. If you are new to programming, PL/SQL is a great place to start. If you are experienced in other programming languages, you will very easily adapt to the new syntax. The accessibility of PL/SQL also means that you can write code that is easily maintained and enhanced over time, a critical aspect of high-quality software development.
PL/SQL is a standard and portable language for Oracle development: If you write a PL/SQL procedure or function to execute from within the Personal Oracle database sitting on your laptop, you can move that same procedure to a database on your corporate network and execute it there without any changes (assuming compatibility of Oracle versions, of course!). "Write once, run everywhere" was the mantra of PL/SQL long before Java appeared. For PL/SQL, though, "everywhere" means "everywhere there is an Oracle database."
PL/SQL is an embedded language: PL/SQL was not designed to be used as a "standalone" language, but instead to be invoked from within a "host" environment. So, for example, you can run PL/SQL programs from within the database (through, say, the SQL*Plus interface). Alternatively, you can define and execute PL/SQL programs from within an Oracle Developer form or report (this approach is called

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Oracle has a history of leading the software industry in providing declarative, nonprocedural approaches to designing both databases and applications. The Oracle Server technology is among the most advanced, powerful, and stable relational databases in the world. Its application development tools, such as Oracle Forms, can offer high levels of productivity by relying heavily on a "paint-your-screen" approach in which extensive default capabilities allow developers to avoid heavy customized programming efforts.

In Oracle's early years, this declarative approach, combined with its groundbreaking relational technology, was enough to satisfy developers. But as the industry matured, expectations rose and requirements became more stringent. Developers needed to get "under the skin" of the products. They needed to build complicated formulas, exceptions, and rules into their forms and database procedures.

In 1988, Oracle Corporation released Oracle Version 6.0, a major advance in its relational database technology. A key component of Oracle Version 6.0 was the so-called "procedural option" or PL/SQL. At roughly the same time, Oracle released its long-awaited upgrade to SQL*Forms Version 2.3 (the original name for the product now known as Oracle Forms or Forms Developer). SQL*Forms V3.0 incorporated the PL/SQL engine for the first time on the tools side, allowing developers to code their procedural logic in a natural, straightforward manner.

This first release of PL/SQL was very limited in its capabilities. On the server side, you could use PL/SQL only to build "batch-processing" scripts of procedural and SQL statements. In other words, you could not store procedures or functions for execution at some later time. You could not construct a modular application or store complex business rules in the server. On the client side, SQL*Forms V3.0 did allow you to create procedures and functions, although support for functions was not documented, and was therefore not used by many developers for years. In addition, this release of PL/SQL did not implement array support and could not interact with the operating system (for input or output). It was a far cry from a full-fledged programming language.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

If you are completely new to programming or to working with PL/SQL (or even SQL, for that matter), learning PL/SQL may seem an intimidating prospect. If this is the case, don't fret! We are confident that you will find it easier than you think. There are two reasons for our optimism:

Computer languages in general are not that hard to learn, at least compared to a second or third "human language." The reason? It's simply that computers are not particularly smart (they "think"—perform operations—rapidly, but not at all creatively). We must rely on a very rigid syntax in order to tell a computer what we want it to do. So the resulting language is also rigid (no exceptions!) and therefore easier for us to pick up.
PL/SQL truly is an easy language as compared to other programming languages. It relies on a highly structured "block" design with different sections, all identified with explicit, self-documenting keywords.

Let's take a look at a few examples that demonstrate some of the key elements of both PL/SQL structure and functionality.

One of the most important aspects of PL/SQL is its tight integration with SQL. You don't need to rely on any intermediate software "glue" like ODBC (Open DataBase Connectivity) or JDBC (Java DataBase Connectivity) to run SQL statements in your PL/SQL programs. Instead, you just insert the UPDATE or SELECT into your code, as shown here:

 1  DECLARE
 2     l_book_count INTEGER;
 3    
 4  BEGIN
 5     SELECT COUNT(*)
 6       INTO l_book_count
 7       FROM books
 8      WHERE author LIKE '%FEUERSTEIN, STEVEN%';
 9 
10     DBMS_OUTPUT.PUT_LINE (
11        'Steven has written (or co-written) ' ||
12         l_book_count ||
13         ' books.');
14
15     -- Oh, and I changed my name, so...
16     UPDATE books
17        SET author = REPLACE (author, 'STEVEN', 'STEPHEN')
18      WHERE author LIKE '%FEUERSTEIN, STEVEN%';
19  END;

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Each version of the Oracle database comes with its own corresponding version of PL/SQL. As you use more up-to-date versions of PL/SQL, an increasing array of functionality will be available to you. One of our biggest challenges as PL/SQL programmers is simply "keeping up." We need to constantly educate ourselves about the new features in each version—figuring out how to use them and how to apply them to our applications, and determining which new techniques are so useful that we should modify existing applications to take advantage of them.

Table 1-1 summarizes the major elements in each of the versions (past and present) of PL/SQL in the database. It offers a very high-level glimpse of the new features available in each version. Following the table, you will find more detailed descriptions of "what's new" in PL/SQL in the latest Oracle versions, Oracle8i and Oracle9i.

The Oracle Developer product suite also comes with its own version of PL/SQL, and it generally lags behind the version available in the Oracle RDBMS itself. This chapter (and the book as whole) concentrates on server-side PL/SQL programming.

Table 1-1: Oracle database and corresponding PL/SQL versions
Oracle version	PL/SQL version	Characteristics
Oracle6	1.0	Initial version of PL/SQL, used primarily as a scripting language in SQLPlus (it was not yet possible to create named, reusable, and callable programs) and also as a programming language in SQLForms 3.
Oracle7

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

O'Reilly & Associates published the first edition of this book back in 1995. At that time, Oracle PL/SQL Programming made quite a splash. It was the first independent (i.e., not emanating from Oracle) book on PL/SQL, and it fulfilled a clear and intensely felt need of developers around the world. Since that time, resources—books, development environments, utilities, and web sites—for PL/SQL programmers have proliferated. (Of course, this book is still by far the most important and valuable of these resources!)

The following sections describe very briefly many of these resources. By taking full advantage of these resources, many of which are available either free or at a relatively low cost, you will greatly improve your development experience (and resulting code).

Over the years, the Oracle PL/SQL series from O'Reilly & Associates has grown to include quite a long list of books. We've summarized the whole set below. Please check out the Oracle area of the O'Reilly web site (http://oracle.oreilly.com) for much more complete information.

Oracle PL/SQL Programming, by Steven Feuerstein with Bill Pribyl: The thousand-page tome you are reading now. The desk-side companion of a great many professional PL/SQL programmers, this book is designed to cover every feature in the core PL/SQL language. The second edition covered Oracle versions through Oracle8, but this third edition covers all PL/SQL versions through Oracle9i.
Learning Oracle PL/SQL, by Bill Pribyl with Steven Feuerstein: A comparatively gentle introduction to the language, ideal for new programmers and those who know a language other than PL/SQL.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Since 1995, when the first edition of this book was published, we have had the opportunity to train, assist, and work with tens of thousands of PL/SQL developers. In the process, we have learned an awful lot from our students and readers, and have also gained some insights into the way we all do our work in the world of PL/SQL. We hope that you will not find it too tiresome if we share some advice with you on how you can work more effectively with this powerful programming language.

We are almost always working under tight deadlines, or playing catch-up from one setback or another. We have no time to waste, and lots of code to write. So let's get right to it—right?

Wrong. If we dive too quickly into the depths of code construction, slavishly converting requirements to hundreds, thousands, or even tens of thousands of lines of code, we will end up with a total mess that is almost impossible to debug and maintain. Don't respond to looming deadlines with panic; you are more likely to meet those deadlines if you do some careful planning.

We strongly encourage you to resist these time pressures and make sure to do the following before you start a new application, or even a specific program in an application:

Construct test cases and test scripts before you write your code: You should determine how you want to verify a successful implementation before you write a single line of a program. By taking this approach (adopted from the Extreme Programming methodology), you are more likely to get the interface of your programs correct, and be able to thoroughly identify what it is your program needs to do.
Establish clear rules for how developers will write the SQL statements in the application: In general, we recommend that individual developers not write a whole lot of SQL. Instead, those single-row queries and inserts and updates should be "hidden" behind prebuilt and thoroughly tested procedures and functions (this is called

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Before exploring the "meat and potatoes" of writing PL/SQL programs, you might benefit from some practical information about how to compile and run those programs. This chapter surveys a range of tools, in particular SQL*Plus, from which you can invoke your PL/SQL programs, and provides the specifics you'll need to get started.

If you already have some experience with database programming, you probably realize that there are lots of different ways and places to use SQL (Structured Query Language). Well, the same is true for PL/SQL; not only can you invoke it from a variety of other languages, it can execute in two different runtime environments:

"Inside" the Oracle database server, as stored code. If you call PL/SQL from SQL*Plus, Java, or any other language, this is typically where it's going to run.
In one of Oracle's application development environments such as Oracle Forms or Oracle Reports, as a program that executes on a client computer. (Alternately, these tools can run on a "middle tier," operated across the network from a browser-only or "thin" client.)

Let's begin by looking at the first option, running on the Oracle server. Here you have additional options for the front end from which you launch your code. Some of the most popular programming tools include:

Oracle's command-line tool, SQL*Plus, which connects to an Oracle server where you can run PL/SQL statements.
A host language such as C, C++, Java, Visual Basic, COBOL, Ada, or FORTRAN, for which Oracle provides a runtime library and/or precompiler that allows you to embed SQL and PL/SQL calls into your program.

Because the number of execution tools at your disposal will probably far exceed your time to assimilate them, it makes sense to concentrate on one or two, and learn them really well. So, while this chapter shows some examples of using PL/SQL with a variety of tools, the main concentration is on SQL*Plus.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The granddaddy of Oracle front ends, Oracle's SQL*Plus provides a command-line interpreter for both SQL and PL/SQL. That is, it accepts database statements from the user, then sends them off to the Oracle server, and finally displays the results.

Often maligned for its primitive user interface, SQL*Plus is one of my favorite Oracle tools. I actually like the lack of fancy gizmos or complicated menus. When I started using Oracle (circa 1986), this product's predecessor was boldly named UFI—User Friendly Interface. Almost two decades later, even Oracle9i's version of SQL*Plus is still unlikely to win any user friendliness awards, but at least it doesn't crash very often.

Today there are several different styles ofexecuting SQL*Plus:

As a console program: This is a program that runs from a shell or command prompt (an environment that is sometimes called a console).
As a pseudo-GUI program: This form of SQL*Plus is available only on Microsoft Windows. I call it a "pseudo-GUI" because it looks pretty much like the console program but with bitmapped fonts; few other features distinguish it from the console program. In fact, Oracle is already phasing out support for this product, with even "extended" support terminating in September 2005.
Via iSQL*Plus (in Oracle9i or later): This program executes from a web browser connected to a middle-tier machine running Oracle's HTTP server and iSQL*Plus server.
Via SQL*Plus Worksheet: This is merely a Java GUI front end on the console version of SQL*Plus. Although it does maintain some statement history, there is little else to commend this version.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Let's turn to the highlights of creating, running, deleting, and otherwise managing PL/SQL programs, using SQL*Plus as the front end. Don't expect to be overwhelmed with detail here; treat this section as a glimpse of topics that will be covered in much greater detail in the chapters ahead.

To build your own stored PL/SQL program, you use one of SQL's CREATE statements. For example, if you want to create a stored function named wordcount that counts words in a string, you can do so using a CREATE FUNCTION statement:

CREATE FUNCTION wordcount (str IN VARCHAR2)
   RETURN PLS_INTEGER
AS
   declare local variables go here
BEGIN
   implement algorithm here
END;
/

As with the simple BEGIN-END blocks shown earlier, running this statement from SQL*Plus requires a trailing slash on a line by itself.

Assuming that the database administrator has granted you Oracle's CREATE PROCEDURE privilege, this statement will cause Oracle to compile and store this stored function in your schema; if your code compiles, you'll probably see a success message such as:

Function created.

If a table or stored program named wordcount already exists in your Oracle schema, CREATE FUNCTION will fail with the error message ORA-00955: name is already used by an existing object. That is one of the reasons that Oracle provides the OR REPLACE OPTION, which you will want to use probably 99% of the time.

CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2)
   RETURN PLS_INTEGER
AS
   same as before

The OR REPLACE option avoids the side effects of dropping and re-creating the program; in other words, it preserves any object privileges you have granted to other users or roles. Fortunately, it only replaces objects of the same type, and it won't automatically drop a table named wordcount just because you decided to create a function by that name.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

If you want to use PL/SQL for all of your programming needs, including the user interface, one way to accomplish that goal is to use two of Oracle's developer tools, commonly known as Forms and Reports. The programmer's tools are part of what Oracle currently calls the Oracle9i Developer Suite, which also includes a Java developer environment and a software configuration manager. Another tool, which was known as Oracle Graphics, is no longer sold as a separate product, but its functionality is available in Forms and Reports.

Included with the Forms Builder and Reports Developer products is a runtime engine that allows programmers to run their own applications; actually deploying your applications for end users, though, involves licensing a runtime environment such as Oracle9i Forms Services or Oracle9i Reports Services. These "services" are components of yet another product, Oracle9i Application Server, which would typically run on a mid-tier server machine, offering forms and reports to end users via Java-enabled browsers. However, older versions of Oracle Forms and Oracle Reports—still in use in many Oracle shops—run in a so-called "fat client" arrangement, in which the runtime software resides on every end user's desktop machine.

Throughout this book, you will notice references to Oracle's "client-side" developer tools, which refer to these tools. From the perspective of the database, everything is a client, even though you may be running multi-tier arrangements instead of just client and server.

Figure 2-5 shows what the Oracle9i Forms Builder user interface looks like. Look closely and you can see the built-in PL/SQL editor in the second subwindow from the right.

Figure 2-5: The programmer's user interface in Oracle Forms Builder

To help provide the kind of functionality that most users expect, Oracle provides an assortment of extensions to PL/SQL that are unique to the developer tools. For example, in Oracle Forms, PL/SQL programs can:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Sooner or later, you will probably want to call PL/SQL from C, Java, Perl, Visual Basic, or any number of other places. This seems like a reasonable request, but if you've ever done cross-language work before, you may be all too familiar with some of the intricacies of mating up language-specific datatypes—especially composite datatypes like arrays, records, and objects—not to mention differing parameter semantics or vendor extensions to "standard" application programming interfaces (APIs) like Oracle DataBase Connectivity (ODBC).

I will show a few examples. Let's say that I've written a PL/SQL function that accepts an ISBN expressed as a string and returns the corresponding book title:

/* File on web: booktitle.fun */
CREATE OR REPLACE FUNCTION booktitle (isbn_in IN VARCHAR2)
   RETURN VARCHAR2
IS
   l_isbn books.title%TYPE;
   CURSOR icur IS SELECT title FROM books WHERE isbn = isbn_in;
BEGIN
   OPEN icur;
   FETCH icur INTO l_isbn;
   CLOSE icur;
   RETURN l_isbn;
END;
/

In SQL*Plus, I could call this in several different ways. The shortest way would be as follows:

SQL> EXEC DBMS_OUTPUT.PUT_LINE(booktitle('0-596-00180-0'))
Learning Oracle PL/SQL

PL/SQL procedure successfully completed.

Let's see how I might call this function from the following environments:

C, using Oracle's precompiler (Pro*C)
Java, using JDBC
Perl, using Perl DBI and DBD::Oracle
PL/SQL Server Pages

These examples are very contrived—for example, the username and password are hardcoded, and the programs simply display the output to stdout. Moreover, I'm not even going to pretend to describe every line of code. Still, these examples will give you an idea of some of the patterns you may encounter in different languages.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

We've seen how to use PL/SQL in SQL*Plus and in a number of other common environments and programming languages. There are still more places you can use PL/SQL:

Embedded in COBOL or FORTRAN and processed with Oracle's precompiler
Called from Visual Basic, using some flavor of ODBC
Called from the Ada programming language, via a technology called SQL*Module
Executed automatically, as triggers on events in the Oracle database such as table updates
Scheduled to execute on a recurring basis inside the Oracle database, via the DBMS_JOB built-in package

We'll take a look at some of these approaches in upcoming chapters.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Every language—whether human or computer—has a syntax, a vocabulary, and a character set. In order to communicate within that language, you have to learn the rules that govern its usage. Many of us are wary of learning a new computer language. Change is often scary, but in general, programming languages are very simple tongues, and PL/SQL is a relatively simple programming language. The difficulty of conversing in languages based on bytes is not with the language itself, but with the compiler or computer with which we are having the discussion. Compilers are, for the most part, rather dull-witted. They are not creative, sentient beings. They are not capable of original thought. Their vocabulary is severely limited. Compilers just happen to think their dull thoughts very, very rapidly—and very inflexibly.

If I hear someone ask "gottabuck?", I can readily interpret that sentence and decide how to respond. On the other hand, if I instruct PL/SQL to "gimme the next half-dozen records," I will not get very far in my application. To use the PL/SQL language, you must dot your i's and cross your t's—syntactically speaking. So, this chapter covers the fundamental language rules that will help you converse with the PL/SQL compiler—the PL/SQL block structure, character set, lexical units, and PRAGMA keyword.

Virtually all programming languages give you a way to organize logically related elements into programming units. In PL/SQL, the fundamental unit of organization is the block, which is at the core of two key language concepts.

Modularization: The PL/SQL block is the basic unit of code from which you will build modules, such as procedures and functions, which in turn comprise applications. As the lowest organizational unit, well-designed blocks are fundamental to achieving code that other programmers can easily use and maintain.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Virtually all programming languages give you a way to organize logically related elements into programming units. In PL/SQL, the fundamental unit of organization is the block, which is at the core of two key language concepts.

Modularization: The PL/SQL block is the basic unit of code from which you will build modules, such as procedures and functions, which in turn comprise applications. As the lowest organizational unit, well-designed blocks are fundamental to achieving code that other programmers can easily use and maintain.
Scope: The block provides a scope or context for logically related objects. In the block, you group together declarations and executable statements that belong together.

You can create anonymous blocks (blocks of code that have no name) and named blocks, which are procedures and functions. Furthermore, you can build packages in PL/SQL that group together multiple procedures and functions.

Each PL/SQL block has up to four different sections, only one of which is mandatory:

Header: Used only for named blocks. The header determines the way the named block or program must be called. Optional.
Declaration section: Identifies variables, cursors, and sub-blocks that are referenced in the execution and exception sections. Optional.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

A PL/SQL program consists of a sequence of statements, each made up of one or more lines of text. The precise characters available to you will depend on what database character set you're using. For example, Table 3-2 illustrates the available characters in the US7ASCII character set.

Table 3-2: Characters available to PL/SQL in the US7ASCII character set
Type	Characters
Letters	A-Z, a-z
Digits	0-9
Symbols	~ ! @ # $ % & * ( ) _ - + = \| [ ] { } : ; " ' < > , . ? / ^
Whitespace	Tab, space, newline, carriage return

Every keyword in PL/SQL is made from various combinations of characters in this character set. Now you just have to figure out how to put them all together!

By default, PL/SQL is a case-insensitive language. That is, uppercase letters are treated the same way as lowercase letters except when characters are surrounded by single quotes, which makes them a literal string.

A number of these characters—both singly and in combination with other characters—have a special significance in PL/SQL. Table 3-3 lists these special symbols.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

An identifier is a name for a PL/SQL object, including any of the following:

Constant
Scalar variable
Composite variable (record or collection)
Exception
Procedure
Function
Package
Type
Cursor
Reserved word
Label

Default properties of PL/SQL identifiers are summarized below:

Up to 30 characters in length
Must start with a letter
Can include $ (dollar sign), _ (underscore), and # (pound sign)
Cannot contain spaces

If the only difference between two identifiers is the case of one or more letters, PL/SQL normally treats those two identifiers as the same. For example, the following identifiers are all considered by PL/SQL to be the same:

lots_of_$MONEY$
LOTS_of_$MONEY$
Lots_of_$Money$

The following strings are valid names of identifiers:

company_id#
primary_acct_responsibility
First_Name
FirstName
address_line1
S123456

The following identifiers are all illegal in PL/SQL:

1st_year                           -- Starts with numeral
procedure-name                     -- Contains invalid character "-"
minimum_%_due                      -- Contains invalid character "%"
maximum_value_exploded_for_detail  -- Name is too long
company ID                         -- Cannot have embedded spaces in name

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

A literal is a value that is not represented by an identifier; it is simply a value. A literal may be composed of one of the following types of data:

Number: 415, 21.6, or NULL
String: `This is my sentence' or `01-FEB-2003' or NULL
Boolean: TRUE, FALSE, or NULL

Notice that there is no direct way to code a true date literal. The value `01-FEB-2003' is a string literal (any sequence of characters enclosed by single quotes). You can convert such a string to a date in PL/SQL or SQL, but a date has only an internal binary representation in Oracle.

A string literal can be composed of zero or more characters from the PL/SQL character set. A literal of zero characters is represented as '' (two consecutive single quotes with no characters between them). At least through Oracle9i, this zero-length string literal has the value NULL, and a datatype of CHAR (fixed-length string).

Unlike identifiers, string literals in PL/SQL arecase-sensitive. The following two literals are different:

'Steven'
'steven'

The following condition, for example, evaluates to FALSE:

IF 'Steven' = 'steven'

The trickiest part of working with string literals comes when you need to include a single quote inside a string literal (as part of the literal itself). Generally, the rule is that you write two single quotes next to each other inside a string if you want the literal to contain a single quote in that position. The following table shows the literal in one column and the resulting "internal" string in the second column:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

A PL/SQL program is made up of a series of declarations and statements. These are defined logically, as opposed to physically. In other words, they are not terminated with the physical end of a line of code; instead, they are terminated with a semicolon (;). In fact, a single statement is often spread over several lines to make it more readable. The following IF statement takes up four lines and is indented to reinforce the logic behind the statement:

IF salary < min_salary (2003)
THEN
   salary := salary + salary*.25;
END IF;

There are two semicolons in this IF statement. The first semicolon indicates the end of the single executable statement within the IF-END IF construct. The second semicolon terminates the IF statement itself. This same statement could also be placed on a single physical line and have exactly the same result:

IF salary < min_salary (2003) THEN salary := salary + salary*.25; END IF;

The semicolons are still needed to terminate each logical, executable statement, even if they are nested inside one another. Unless you're trying to create unreadable code, I suggest that you not combine the different components of the IF statement on a single line. I also recommend that you place no more than one statement or declaration on each line.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Inline documentation, otherwise known as comments, is an important element of a good program. While this book offers many suggestions on how to make your program self-documenting through good naming practices and modularization, such techniques are seldom enough by themselves to communicate a thorough understanding of a complex program.

PL/SQL offers two different styles for comments: single-line and multiline block comments.

The single-line comment is initiated with two hyphens ( -- ), which cannot be separated by a space or any other characters. All text after the double hyphen to the end of the physical line is considered commentary and is ignored by the compiler. If the double hyphen appears at the beginning of the line, the whole line is a comment.

Remember: the double hyphen comments out the remainder of a physical line, not a logical PL/SQL statement. In the following IF statement, I use a single-line comment to clarify the logic of the Boolean expression:

IF salary < min_salary (2003) -- Function returns min salary for year.
THEN
   salary := salary + salary*.25;
END IF;

While single-line comments are useful for documenting brief bits of code or ignoring a line that you do not want executed at the moment, the multiline comment is superior for including longer blocks of commentary.

Multiline comments start with a slash-asterisk (/*) and end with an asterisk-slash (*/). PL/SQL considers all characters found between these two sequences of symbols to be part of the comment, and they are ignored by the compiler.

The following example of multiline comments shows a header section for a procedure. I use the vertical bars in the left margin so that, as the eye moves down the left edge of the program, it can easily pick out the chunks of comments:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The PRAGMA keyword is used to signify that the remainder of the PL/SQL statement is a pragma, or directive, to the compiler. Also called apseudoinstruction, a pragma simply passes information to the compiler rather than getting transformed into a particular execution.

The syntax for using the PRAGMA keyword is as follows:

PRAGMA instruction;

where instruction is a statement providing instructions to the compiler. The PL/SQL compiler will accept such directives anywhere in the declaration section.

PL/SQL offers the following pragmas:

AUTONOMOUS_TRANSACTION: Tells the PL/SQL runtime engine to commit or roll back any changes made to the database inside the current block without affecting the main or outer transaction. See Chapter 13 for more information. Introduced in Oracle8i.
EXCEPTION_INIT: Tells the compiler to associate a particular error number with an identifier you have declared as an exception in your program. See Chapter 6 for more information.
RESTRICT_REFERENCES: Tells the compiler the purity level (freedom from side effects) of a packaged program. See Chapter 16 for more information.
SERIALLY_REUSABLE: Tells the PL/SQL runtime engine that package-level data should not persist between references to that data. See Chapter 17 for more information. Introduced in Oracle8.

The following block demonstrates the use of the EXCEPTION_INIT pragma to name a built-in exception that would otherwise have only a number.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

A PL/SQL label is a way to name a particular part of your program. Syntactically, a label has the format:

<<identifier>>

where identifier is a valid PL/SQL identifier (up to 30 characters in length and starting with a letter, as discussed earlier in Section 3.3). There is no terminator; labels appear directly in front of the thing they're labeling, which must be an executable statement—even if it is merely the NULL statement.

BEGIN
   ...
   <<the_spot>>
   NULL;

Because anonymous blocks are themselves executable statements, a label can "name" ananonymous block for the duration of its execution. For example:

<<insert_but_ignore_dups>>
BEGIN
   INSERT INTO catalog
   VALUES (...);
EXCEPTION
   WHEN DUP_VAL_ON_INDEX
   THEN
      NULL;
END insert_but_ignore_dups;

One reason you might label a block is to improve the readability of your code. When you give something a name, you self-document that code. You also clarify your own thinking about what that code is supposed to do, sometimes ferreting out errors in the process.

Another reason to use a block label is to allow you to qualify references to elements from an enclosing block that have duplicate names in the current, nested block. Here's a schematic example:

<<outerblock>>
DECLARE
   counter INTEGER := 0;
BEGIN
   ...
   DECLARE
      counter INTEGER := 1;
   BEGIN
      IF counter = outerblock.counter
      THEN
         ...
      END IF;
   END;
END;

Without the block label, there would be no way to distinguish between the two "counter" variables. Again, though, a better solution would probably have been to use distinct variable names.

A third function of labels is to serve as the target of aGOTO statement. However, these days, GOTO statements are virtually nonexistent, thanks to Edsger Dijkstra's now-legendary essay on the subject (and the fact that exception handling is usually a better way to go). In all the PL/SQL code I've ever seen, I recall only one GOTO.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

This chapter describes two types of PL/SQL control statements: conditional control statements and sequential control statements. Almost every piece of code you write will require conditional control, which is the ability to direct the flow of execution through your program based on a condition. You do this with IF-THEN-ELSE and CASE statements (CASE statements are new in Oracle9i). There are also CASE expressions; while not the same as CASE statements, they can sometimes be used to eliminate the need for an IF or CASE statement altogether. Far less often, you will need to tell PL/SQL to transfer control unconditionally via the GOTO statement, or explicitly to do nothing via the NULL statement.

In your programs, you need to be able to implement requirements such as:

If the salary is between ten and twenty thousand, then apply a bonus of $1500. If the salary is between twenty and forty thousand, apply a bonus of $1000. If the salary is over forty thousand, give the employee a bonus of $500.

or:

If the user preference includes the toolbar, display the toolbar when the window first opens.

The IF statement allows you to design conditional logic into your programs. The IF statement comes in three flavors, as shown in the following table:

IF type	Characteristics
IF THEN END IF;	This is the simplest form of the IF statement. The condition between IF and THEN determines whether the set of statements between THEN and END IF should be executed. If the condition evaluates to FALSE, the code is not executed.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In your programs, you need to be able to implement requirements such as:

If the salary is between ten and twenty thousand, then apply a bonus of $1500. If the salary is between twenty and forty thousand, apply a bonus of $1000. If the salary is over forty thousand, give the employee a bonus of $500.

or:

If the user preference includes the toolbar, display the toolbar when the window first opens.

The IF statement allows you to design conditional logic into your programs. The IF statement comes in three flavors, as shown in the following table:

IF type	Characteristics
IF THEN END IF;	This is the simplest form of the IF statement. The condition between IF and THEN determines whether the set of statements between THEN and END IF should be executed. If the condition evaluates to FALSE, the code is not executed.
IF THEN ELSE END IF;	This combination implements an either/or logic: based on the condition between the IF and THEN keywords, execute the code either between THEN and ELSE or between ELSE and END IF. One of these two sections of executable statements is performed.
IF THEN ELSIF ELSE END IF;	This last and most complex form of the IF statement selects an action from a series of mutually exclusive conditions and then executes the set of statements associated with that condition. If you're writing IF statements like this in Oracle9i, you should consider using searched CASE statements instead.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

New to PL/SQL in Oracle9i, the CASE statement allows you to select one sequence of statements to execute out of many possible sequences. CASE statements themselves are not new; they have long been implemented in other programming languages. They've been part of the SQL standard since 1992, although Oracle SQL didn't support CASE until the release of Oracle8i, and PL/SQL didn't support CASE until Oracle9i.

Oracle9i (and higher) supports the following two types of CASE statements:

Simple CASE statement: Associates each of one or more sequences of PL/SQL statements with a value. Chooses which sequence of statements to execute based on an expression that returns one of those values.
Searched CASE statement: Chooses which of one or more sequences of PL/SQL statements to execute by evaluating a list of Boolean conditions. The sequence of statements associated with the first condition that evaluates to TRUE is executed.

In addition to CASE statements, PL/SQL also supports CASE expressions. A CASE expression is very similar in form to a CASE statement, and allows you to choose which of one or more expressions to evaluate. The result of a CASE expression is a single value, whereas the result of a CASE statement is the execution of a sequence of PL/SQL statements.

A simple CASE statement allows you to choose which of several sequences of PL/SQL statements to execute based on the results of a single expression. Simple CASE statements take the following form:

CASE expression
WHEN result1 THEN
   statements1
WHEN result2 THEN

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Certain PL/SQL control structures offer structured methods for processing executable statements in your program. You use an IF statement or a CASE statement to test a condition to determine which parts of your code to execute; you use a LOOP variation (described in Chapter 5) to execute a section of code more than once. In addition to these well-structured approaches to program control, PL/SQL offers the GOTO. The GOTO statement performs unconditional branching to another executable statement in the same execution section of a PL/SQLblock. As with other constructs in the language, if you use GOTO appropriately and with care, your programs will be stronger for it.

The general format for a GOTO statement is:

GOTO label_name;

where label_name is the name of a label identifying the target statement. This GOTO label is defined in the program as follows:

               <<label_name>>

You must surround the label name with double enclosing angle brackets (<< >>). When PL/SQL encounters a GOTO statement, it immediately shifts control to the first executable statement following the label. Following is a complete code block containing both a GOTO and a label:

BEGIN
   GOTO second_output;
   
   DBMS_OUTPUT.PUT_LINE('This line will never execute.');

   <<second_output>>
   DBMS_OUTPUT.PUT_LINE('We are here!');
END;

Contrary to popular opinion (including mine), the GOTO statement can come in handy. There are cases where a GOTO statement can simplify the logic in your program. On the other hand, because PL/SQL provides so many different control constructs and modularization techniques, you can almost always find a better way to do something than with a GOTO.

There are several restrictions on the GOTO statement:

At least one executable statement must follow a label.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Usually when you write a statement in

Table of Contents