Oracle PL/SQL Programming | O'Reilly Media

Content preview·Buy PDF of this chapter|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. 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.

Oracle's PL/SQL language has several defining characteristics:

It is a highly structured, readable, and accessible language: If you are new to programming, PL/SQL is a great place to start. You will find that it is an easy language to learn and is rich with keywords and structure that clearly express the intent of your code. If you are experienced in other programming languages, you will very easily adapt to the new syntax.
It is a standard and portable language for Oracle development: If you write a PL/SQL procedure or function to execute from within the 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."
It 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 PDF of this chapter|Buy reprint rights for this chapter

Oracle's PL/SQL language has several defining characteristics:

It is a highly structured, readable, and accessible language: If you are new to programming, PL/SQL is a great place to start. You will find that it is an easy language to learn and is rich with keywords and structure that clearly express the intent of your code. If you are experienced in other programming languages, you will very easily adapt to the new syntax.
It is a standard and portable language for Oracle development: If you write a PL/SQL procedure or function to execute from within the 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."
It 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 client-side PL/SQL). You cannot, however, create a PL/SQL executable that runs all by itself.
It is a high-performance, highly integrated database language: These days, you have a number of choices when it comes to writing software to run against the Oracle database. You can use Java and JDBC; you can use Visual Basic and ODBC; you can go with Delphi, C++, and so on. You will find, however, that it is easier to write highly efficient code to access the Oracle database in PL/SQL than it is in any other language. In particular, Oracle offers certain PL/SQL-specific enhancements such as the FORALL statement that can improve database performance by an order of magnitude or more.

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 PDF of this chapter|Buy reprint rights for this chapter

Oracle has a history of leading the software industry in providing declarative, non-procedural 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, 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, the declarative approach of SQL, 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 scripts.

In 1988, Oracle Corporation released Oracle Version 6, a major advance in its relational database technology. A key component of that version 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. You could not construct a modular application or store 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 PDF of this chapter|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, 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 look at a few examples that demonstrate some 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" such as 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    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 PDF of this chapter|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 version, Oracle Database 10g.

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 Database release	PL/SQL version	Characteristics
6.0	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.
7.0	2.0	Major upgrade to PL/SQL 1.0. Added support for stored procedures, functions, packages, programmer-defined records, PL/SQL tables, and many package extensions.
7.1	2.1	Supported programmer-defined subtypes, enabled the use of stored functions inside SQL statements, and offered dynamic SQL with the DBMS_SQL package. With PL/SQL 2.1, you could execute SQL DDL statements from within PL/SQL programs.

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 PDF of this chapter|Buy reprint rights for this chapter

With the release of the fourth edition, we are celebrating the tenth anniversary of Oracle PL/SQL Programming! O'Reilly 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 has grown to include quite a long list of books. Here we've summarized the books currently in print. 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 1,200-page tome you are reading now. The deskside companion of a great many professional PL/SQL programmers, this book is designed to cover every feature in the core PL/SQL language. The current version covers through Oracle Database 10g Release 2.
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 PDF of this chapter|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 doing this, 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

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 PDF of this chapter|Buy reprint rights for this chapter

Before exploring the "meat and potatoes" of writing PL/SQL programs, let's review some practical information about how to create, compile, and run those programs. This chapter provides a PL/SQL-focused crash course on the Oracle tool known as SQL*Plus ; it also breezes through a few samples of PL/SQL embedded in some other languages. If you already use SQL*Plus, or one of the third-party tools such as Toad for Oracle, you can skip most of this chapter. However, you might still enjoy reading the section "Calling PL/SQL From Other Languages" near the end of the chapter.

As is true for SQL, PL/SQL programs can be invoked from a variety of other languages. Unlike SQL, PL/SQL can actually run in two different places:

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 environments such as Oracle Forms or Oracle Reports, as a program that executes on a client computer. Often, the runtime environment lives on a middle tier, operated across the network from a browser-only or "thin" client.

Because this is a book about PL/SQL programming on the server, we are not going to spend much time on Oracle Forms or Oracle Reports. Instead, we will concentrate on the common aspects of PL/SQL programming; one of the most common denominators of Oracle programming is SQL*Plus.

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 statements from the user, sends them off to the Oracle server, and 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 and complicated menus. Ironically, when I started using Oracle (circa 1986), this product's predecessor was boldly named UFI—

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 PDF of this chapter|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 statements from the user, sends them off to the Oracle server, and 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 and complicated menus. Ironically, when I started using Oracle (circa 1986), this product's predecessor was boldly named UFI—User Friendly Interface. Two decades later, even the latest version of SQL*Plus is still unlikely to win any user friendliness awards, but at least it doesn't crash very often.

Today, Oracle supports three different styles of executing 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. Beware: Oracle has been threatening to desupport this product for years, and it hasn't really been updated since Oracle8i Database.
Via iSQL*Plus (in Oracle9i Database or later): This program executes from a web browser connected to a middle-tier machine running Oracle's HTTP server and iSQL*Plus server.

Figure 2-1 is a screenshot of a SQL*Plus console-style session.

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 PDF of this chapter|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 a new stored PL/SQL program, you use one of SQL's CREATE statements. For example, if you want to create a stored function that counts words in a string, you can do so using a CREATE FUNCTION statement :

    CREATE FUNCTION wordcount (str IN VARCHAR2)
       RETURN PLS_INTEGER
    ASdeclare local variables 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 DBA has granted you Oracle's CREATE PROCEDURE privilege (which also gives you the privilege of creating functions), this statement causes 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 reason that Oracle provides the OR REPLACE option, which you will want to use probably 99% of the time.

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

The OR REPLACE option avoids the side effects of dropping and recreating the program; in other words, it preserves any object privileges you have granted to other users or roles. Fortunately, it replaces only 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 PDF of this chapter|Buy reprint rights for this chapter

Sooner or later, you will probably want to call PL/SQL from C, Java, Perl, PHP, 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 Open DataBase Connectivity (ODBC).

I will show a few very brief examples of calling PL/SQL from the outside world. 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
PHP
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 PDF of this chapter|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.

In PL/SQL, as in most other procedural languages, the smallest meaningful grouping of code is known as a block. A block is a unit of code that provides execution and scoping boundaries for variable declarations and exception handling. PL/SQL allows you to create anonymous blocks (blocks of code that have no name) and named blocks , which are either procedures or functions.

A 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.

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 PDF of this chapter|Buy reprint rights for this chapter

In PL/SQL, as in most other procedural languages, the smallest meaningful grouping of code is known as a block. A block is a unit of code that provides execution and scoping boundaries for variable declarations and exception handling. PL/SQL allows you to create anonymous blocks (blocks of code that have no name) and named blocks , which are either procedures or functions.

A 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 subblocks that are referenced in the execution and exception sections. Optional.
Execution section: Statements the PL/SQL runtime engine will execute at runtime. Mandatory.
Exception section: Handles exceptions to normal processing (warnings and error conditions). Optional.

Figure 3-1 shows the structure of the PL/SQL block for a procedure.

Figure 3-1: The PL/SQL block structure

Figure 3-2 shows a procedure containing all four sections of the elements of a block. This particular block begins with the keyword PROCEDURE, and, like all blocks, ends with the keyword END.

When someone wishes to remain anonymous , that person goes unnamed. Same with the anonymous block in PL/SQL, which is shown in Figure 3-3: it lacks a header section altogether, beginning instead with either DECLARE or BEGIN. That means that it cannot be called by any other block—it doesn't have a handle for reference. 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 PDF of this chapter|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-1 illustrates the available characters in the US7ASCII character set .

Table 3-1: 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, operator, and token 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!

And now for some real PL/SQL trivia: Oracle's documentation—as well as earlier editions of this book—list the ampersand, curly braces, and square brackets as part of the default character set:

    & { } [ ]

While all characters are allowed in literal strings, Oracle does not seem to use these particular five characters anywhere in the visible portions of PL/SQL. Moreover, there is no direct way to use these characters in programmer-defined identifiers.

Regardless of your memory for such trivia, you'll definitely want to remember that PL/SQL is a case-insensitive language. That is, it doesn't matter how you type keywords and identifiers; uppercase letters are treated the same way as lowercase letters unless surrounded by delimiters that make them a literal string. By convention, the authors of this book prefer uppercase for built-in language keywords, and lowercase for programmer-defined identifiers.

A number of these characters—both singly and in combination with other characters—have a special significance in PL/SQL. Table 3-2 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 PDF of this chapter|Buy reprint rights for this chapter

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

Constant or variable
Exception
Cursor
Program name: procedure, function, package, object type, trigger, etc.
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 any "whitespace" characters

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                           -- Doesn't start with a letter
    procedure-name                     -- Contains invalid character "-"
    minimum_%_due                      -- Contains invalid character "%"
    maximum_value_exploded_for_detail  -- Too long
    company ID                         -- Has embedded whitespace

Identifiers are the handles for objects in your program and one of your chief means of communicating with other programmers. For this reason, many organizations adopt naming conventions; if your project doesn't require naming conventions, you will still want to choose variable names carefully ... even if you are the only person who will ever see the code!

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 PDF of this chapter|Buy reprint rights for this chapter

A literal is a value that is not represented by an identifier; it is simply a value. Here is a smattering of literals you could see in a PL/SQL program:

Number: 415, 21.6, 3.141592654f, 7D, NULL
String: 'This is my sentence', '01-OCT-2006', q'!hello!', NULL
Time interval: INTERVAL '25-6' YEAR TO MONTH, INTERVAL '-18' MONTH, NULL
Boolean: TRUE, FALSE, NULL

The trailing "f" in number literal 3.14159f designates a 32-bit floating point number as defined by the IEEE 754 standard, which Oracle partially supports beginning with Oracle Database 10g Release 1. Similarly, 7D is the number 7 as represented in a 64-bit float.

The string q'!hello!' bears some explanation. The ! is a user-defined delimiter, also introduced in Oracle Database 10g; the leading q and the surrounding single quotes tell the compiler that the ! is the delimiter, and the string represented is simply the word hello.

The notion of a time interval is yet another feature introduced in Oracle Database 10g; the first example above represents "25 years and six months after"; the second represents "18 months before."

Even though Oracle allows you to input time intervals using a literal format, you cannot do so with DATE datatypes; notice that '01-OCT-2006' is listed as a string rather than as an Oracle DATE. Yes, PL/SQL or SQL can implicitly convert '01-OCT-2006' to and from Oracle's internal date format, but you will normally use built-in functions to perform explicit conversions. For example:

    TO_DATE('01-OCT-2006', 'DD-MON-YYYY')
    TO_TIMESTAMP_TZ('01-OCT-2006 00:00:00 -6','DD-MON-YYYY HH24:MI:SS TZH')

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 PDF of this chapter|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 PDF of this chapter|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 PDF of this chapter|Buy reprint rights for this chapter

A programming notion that is truly derived from Greek is pragma, which means "deed" or, by implication, an "action." In various programming languages, a pragma is generally a line of source code prescribing an action you want the compiler to take. It's like an option that you give the compiler; it can result in different runtime behavior for the program, but it doesn't get translated directly into byte code.

PL/SQL has a PRAGMA keyword with the following syntax:

    PRAGMAinstruction_to_compiler;

The PL/SQL compiler will accept such directives anywhere in the declaration section, but most of them have certain additional requirements regarding placement.

PL/SQL offers several 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 14 for more information.
EXCEPTION_INIT: Tells the compiler to associate a particular error number with an identifier you have declared as an exception in your program. Must follow the declaration of the exception. See Chapter 6 for more information.
RESTRICT_REFERENCES: Tells the compiler the purity level (freedom from side effects) of a packaged program. See Chapter 17 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 18 for more information.

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 PDF of this chapter|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 the "Identifiers" section). 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" an anonymous 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 a GOTO statement. For better or worse, 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 "real world" 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 PDF of this chapter|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 available in Oracle9i Database and Oracle Database 10g). 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.

The IF statement allows you to design conditional logic into your programs. With it, you'll be able to implement requirements such as:

If the salary is between ten and twenty thousand, then apply a bonus of $1,500.
If the salary is between twenty and forty thousand, apply a bonus of $1,000.
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 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 PDF of this chapter|Buy reprint rights for this chapter

The IF statement allows you to design conditional logic into your programs. With it, you'll be able to implement requirements such as:

If the salary is between ten and twenty thousand, then apply a bonus of $1,500.
If the salary is between twenty and forty thousand, apply a bonus of $1,000.
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 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 a condition that is TRUE 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 using any release from Oracle9i Database Release 1 onwards, 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 PDF of this chapter|Buy reprint rights for this chapter

The CASE statement allows you to select one sequence of statements to execute out of many possible sequences. They have been part of the SQL standard since 1992, although Oracle SQL didn't support CASE until the release of Oracle8i Database,

Earlier we stated that the result from a Boolean expression can be TRUE, FALSE, or NULL. In PL/SQL that is quite true, but in the larger realm of relational theory it's considered incorrect to speak of a NULL result from a Boolean expression. Relational theory says that a comparison to NULL, such as:

    2 < NULL

yields the Boolean value UNKNOWN. And UNKNOWN is not the same as NULL. That PL/SQL refers to UNKNOWN as NULL is not something you should lose sleep over. We want you to be aware though, that UNKNOWN is the

Table of Contents