Learning Oracle PL/SQL | O'Reilly Media

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Let's start at the beginning and take a look at what Procedural Language/Structured Query Language (PL/SQL) really is, what it is good for, and how it fits into the world.

Pick up most any reference book about PL/SQL and you'll read that it is Oracle's "procedural extension to Structured Query Language (SQL)." If that definition doesn't help much, consider what it assumes you know:

What a computer "language" is
What "procedural" means in this context
Some concept of Structured Query Language, including the notion that SQL is not procedural
The idea of a language "extension"

Let's look at each concept in turn.

A computer language is a particular way of giving instructions to (that is, programming) a computer. Computer languages tend to have a small vocabulary compared to regular human language. In addition, the way you can use the language vocabulary—that is, the grammar—is much less flexible than human language. These limitations occur because computers take everything literally; they have no way of reading between the lines and assuming what you intended.

Procedural refers to a series of ordered steps that the computer should follow to produce a result. This type of language also includes data structures that hold information that can be used multiple times. The individual statements could be expressed as a flow chart (although flow charts are out of fashion these days). Programs written in such a language use its sequential, conditional, and iterative constructs to express algorithms. So this part of the PL/SQL's definition is just saying that it is in the same family of languages as BASIC, COBOL, FORTRAN, Pascal, and C. For a description of how procedural languages contrast with three other common language categories, see the following sidebar.

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

Pick up most any reference book about PL/SQL and you'll read that it is Oracle's "procedural extension to Structured Query Language (SQL)." If that definition doesn't help much, consider what it assumes you know:

What a computer "language" is
What "procedural" means in this context
Some concept of Structured Query Language, including the notion that SQL is not procedural
The idea of a language "extension"

Let's look at each concept in turn.

A computer language is a particular way of giving instructions to (that is, programming) a computer. Computer languages tend to have a small vocabulary compared to regular human language. In addition, the way you can use the language vocabulary—that is, the grammar—is much less flexible than human language. These limitations occur because computers take everything literally; they have no way of reading between the lines and assuming what you intended.

Procedural refers to a series of ordered steps that the computer should follow to produce a result. This type of language also includes data structures that hold information that can be used multiple times. The individual statements could be expressed as a flow chart (although flow charts are out of fashion these days). Programs written in such a language use its sequential, conditional, and iterative constructs to express algorithms. So this part of the PL/SQL's definition is just saying that it is in the same family of languages as BASIC, COBOL, FORTRAN, Pascal, and C. For a description of how procedural languages contrast with three other common language categories, see the following sidebar.

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

To fully understand why and where PL/SQL is a good fit, it's important to understand the limitations of alternate languages. Let's first hark back to the early days and find out why PL/SQL exists at all.

Before PL/SQL, the only way to bundle up Oracle's SQL statements inside complex procedural programs was to embed your SQL in another programming language, which was typically C. This was essential because SQL alone has no way to enforce rules such as "when you sell a widget, total the monthly sales figures, and decrease the widget inventory by one," or "only a manager can discount blue widgets by more than 10%." So the C programs had to enforce those business rules.

While using a "host language" like C can work, more or less (as long as everybody is strictly required to use the application program—and that's a big if), it has some other limitations:

Different vendors' C compilers and libraries are not 100% compatible, making it expensive to port application programs from one maker's computer to another. Even if the code doesn't change, you still have to test it. Because Oracle designed PL/SQL to run identically on every platform, though, stored procedures are reusable across different server hardware and operating systems, with minimal testing required (after testing on one platform, some people don't even bother to test PL/SQL before using it on another platform). This turns out to be important not just to customers' applications but also to Oracle itself, since it lets the company easily package and deliver new features on all 80+ platforms where the Oracle server runs. (One of Oracle's hallmark marketing angles has long been the promise of "running everywhere.")
Despite widespread adoption, C is generally considered more suited for a class of programming tasks that does not include writing typical business applications. Programmers in the corporate MIS shop usually prefer languages immune from the peril of C's "pointers." In addition, text manipulation in C is sort of tedious compared to 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

Now that you're sold on PL/SQL, I'd like to change gears a bit and give you some practical information on what you'll need to start programming.

First off, you'll need access to an Oracle database. Since Oracle is a product designed to be shared, it isn't necessary to have your own private copy of Oracle on your own private machine. You just need an account in an Oracle installation where the administrator will let you experiment with PL/SQL. You can use your desktop machine merely as a tool through which you connect to a database on a different machine. If you don't have that, though, you might have to set up your own Oracle database.

In the simplest arrangement, you would have the Oracle server running on a machine on your desk, where you would also do all your development. There are four things you will need:

Access to a "big enough" machine running an operating system supported by Oracle
A licensed copy of Oracle's server software, available free (with some restrictions) from Oracle's web site
A text editor
A copy of this book

Since you've already got the book, and getting #2 will also get you #3, you're halfway there already.

If you want to install the Enterprise Edition of Oracle9 i on a typical Unix machine, Oracle says you need at least the following:

256 megabytes of RAM
2.5 gigabytes of disk for software and starter database
400 megabytes (or more) of swap space during installation

Or, if you want to run the older release, Oracle8i, on a Windows NT or 2000 machine, you'll need a machine something like this:

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

To do anything really interesting with PL/SQL, you need an understanding of the fundamentals of the language: what constitutes a valid statement, how you name things, how you construct programs, and so on. You also need at least a basic understanding of how to work with a hands-on tool like SQL*Plus so you can run the examples yourself. After reading through this chapter, you should be able to look at existing PL/SQL code and understand the different sections of code and the roles they play; you should also be able to build your own simple blocks of code.

In order to make the material easier to find later, I have resorted to organizing this chapter in more of a reference format rather than the example-driven format that you'll find elsewhere in the book.

While some programming languages look like Einstein's scribbles, Oracle PL/SQL uses specific, easy to understand keywords (reserved words in the language) to identify the different parts of a PL/SQL program. To indicate the beginning of the exception section in your program, for example, you use the

EXCEPTION

keyword. To show that you have ended your program, you use the (you guessed it!)

END;

keyword.

The English-like nature of PL/SQL is just one aspect of the language that makes it relatively easy to learn. However, given the fact that PL/SQL lives with one foot in the database and one foot in the procedural world, even the English terminology may be unfamiliar to you. Here are a few concepts and terms you'll want to know up front.

Keyword: This book uses the term keyword to mean a word that the language recognizes. In PL/SQL, keywords include BEGIN, END, IF, and RETURN.
Identifier: A name for something such as a variable or a stored procedure. Some are predefined by the language, and some you invent. Some examples of invented identifiers:

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

While some programming languages look like Einstein's scribbles, Oracle PL/SQL uses specific, easy to understand keywords (reserved words in the language) to identify the different parts of a PL/SQL program. To indicate the beginning of the exception section in your program, for example, you use the

EXCEPTION

keyword. To show that you have ended your program, you use the (you guessed it!)

END;

keyword.

The English-like nature of PL/SQL is just one aspect of the language that makes it relatively easy to learn. However, given the fact that PL/SQL lives with one foot in the database and one foot in the procedural world, even the English terminology may be unfamiliar to you. Here are a few concepts and terms you'll want to know up front.

Keyword: This book uses the term keyword to mean a word that the language recognizes. In PL/SQL, keywords include BEGIN, END, IF, and RETURN.
Identifier: A name for something such as a variable or a stored procedure. Some are predefined by the language, and some you invent. Some examples of invented identifiers: printme, balance_in_$, book2.
Datatype: A name for a class of values. PL/SQL's built-in datatypes include NUMBER, DATE, and VARCHAR2 (that means text).
Variable: A "container," named with an identifier and of a particular datatype, that can temporarily store data. Some variables can hold only a single thing, like the number of people who live in Portugal, and some can hold a list of things, like the birth dates of my family members.
Declaring, declaration, declaration section: Declaring a variable means naming it and defining its datatype. With few exceptions, variables must be declared prior to use. In PL/SQL, these designations most often occur in a separate section of the program called the declaration section. Declarations are not, strictly speaking, "statements" themselves.

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

To partake in a grand tradition of beginning programmers, the first program to write in a new language will merely print out the message "hello, world". PL/SQL can display this archetypal greeting with only three lines of code:

BEGIN
   DBMS_OUTPUT.PUT_LINE('hello, world');
END;

This is called an anonymous block —that is, a block with no name. Its only executable statement is a call to the procedure PUT_LINE, supplied in Oracle's built-in package named DBMS_OUTPUT. This built-in stored procedure can accept a string that can get printed to the screen when you run it. As you can see, when your program needs to call another stored procedure, you merely invoke its name and supply any needed values. We'll discuss packaged procedures extensively in upcoming chapters.

The program seems simple enough, but how would you go about running it? For this we'll turn to an Oracle tool called SQL*Plus.

Once you have access to an Oracle server, you almost certainly have access to a program called SQL*Plus, which is a very common command-line tool used by almost every Oracle programmer. Once it's installed properly, you can usually launch SQL*Plus from the command prompt (see the sidebar) using the sqlplus command.

OS> sqlplus

(Here, I've substituted OS> for the operating system command prompt. On MS Windows it might say C:\>, and on Unix, $.)

If you're a programming neophyte, it's possible you've been exposed only to windows-based software and have never worked at the venerable command prompt, where you actually use the keyboard to type instructions to the system (as opposed to a using a point-and-click, graphical user interface tool).

On most MS Windows installations, there is an option from the Start menu to launch a command window (Start → Programs → Command Prompt), but on others you have to type in the command at the Run menu (Start → Run →

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

My son, now eight, is a Lego fanatic. While many of his new-fangled, special-purpose Lego components are in many ways different from the simple bricks I had as a child, the idea of constructing from parts is the same. I think humans find something comforting about assembling objects of similar size, shape, and function into useful artifacts. As a grown-up programmer I'm still, after a fashion, playing with blocks.

In PL/SQL, there are only three types of blocks:

Anonymous blocks
Procedures
Functions

We've already seen a simple anonymous block—that is, one without a name—in the "hello, world" example. The other two types of blocks, procedures and functions, are similar but also include a header.

There are actually four possible components of a PL/SQL block:

The definition of its interface (that is, information such as its name that will be needed to invoke it later)
Some number of variables
A sequence of statements (which may include SQL statements) intended to solve some problem or perform some action
A way to recover from "issues"

These components are organized into sections inside the block, as illustrated in Figure 2-1.

Figure 2-1: Representation of a named PL/SQL block

Although there are places other than blocks where PL/SQL can exist, I'll introduce those in later chapters. The next two sections will give you a slightly closer look at the three forms of blocks.

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, before you can work with any kind of variable, you must first declare it; that is, you must give it a name and specify its datatype. Borrowing experts' terminology, you could restate this as "PL/SQL is a strongly typed programming language." Whatever you call it, this section reviews common PL/SQL datatypes and discusses how to declare variables.

The most common datatypes in PL/SQL are in four families: string, number, date, and logical (Boolean).

Section 2.4.1.1: Strings

Strings are "free form" data. A string can contain any valid character in the character set of a language. While there are several variations of strings, the datatype you will almost always use is VARCHAR2.

VARCHAR2 variables store variable-length character strings, which means that the length of the string depends on the value stored in the variable (which can vary). When you declare a variable-length string, you must also specify a maximum length for the string, which can range from 1 to 32,767 bytes. The general format for a VARCHAR2 declaration is:

                  variable_name VARCHAR2(n);

Where:

variable_name: Programmer-supplied identifier that is subject to PL/SQL's naming rules (see Section 2.9.2 near the end of the chapter).
n: Literal integer between 1 and 32,767 that designates the maximum length of the string's contents, expressed by default in bytes.

Here's an example of some declarations and corresponding assignments:

DECLARE
   small_string VARCHAR2(4);
   line_of_text VARCHAR2(2000);
   biggest_string_allowed VARCHAR2(32767);
BEGIN
   biggest_string_allowed := 'Tiny';
   line_of_text := 'Tiny';

   IF biggest_string_allowed = line_of_text
   THEN
      DBMS_OUTPUT.PUT_LINE ('They match!');
   END IF;
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

An operator is a symbol or keyword that the language provides to perform an arithmetic, logical, or other function. As in mathematics, the "things" upon which the operators operate are called operands. This section explores some of the most frequently used operators in the PL/SQL language; they are summarized in Table 2-1. If you already know one programming language, you'll be happy to hear that most of PL/SQL's operators, such as mathematical and comparison operators, are consistent with common programming usage.

Table 2-1: Common PL/SQL operators
Operator Category	Notation	Meaning
Assignment	:=	Store the value
Arithmetic	`+` `-` `/` `` `*`	Addition Subtraction Division Multiplication Exponentiation
Logical	`AND` `OR` `NOT`

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 world is a very complicated place, and the software we write is generally intended to reflect some part of that complexity. So we need constructs in our programming language that can respond to all sorts of situations and requirements, including conditional behavior, such as: "if x is true, then do y, otherwise do z." Enter the IF and CASE statements.

PL/SQL supports conditional logic with the IF statement:

IF condition1
THEN
   statements
[ ELSIF condition2
THEN
   statements ] ...
[ ELSIF conditionn
THEN
   statements ]
[ ELSE
   last_statements ]
END IF;

Where:

conditionn: An expression that yields a Boolean result. Typically, each condition in an IF statement is mutually exclusive from the others.
statements, last_statements: One or more executable statements that execute when the corresponding condition is true. As usual, each statement must have a terminator (closing semi-colon).

The basic idea is that you can test for any number of conditions, and the first one that is true causes the corresponding statement to execute. If none are true, and the ELSE clause is present, last_statements execute.

Here is a simple IF statement:

IF book_count > 10000
THEN
   ready := TRUE;
   DBMS_OUTPUT.PUT_LINE ('We''re ready to open the library!');
END IF;

And here is an example of the IF-THEN-ELSE statement that gives a raise to everyone, but a smaller raise if your hourly wage is $10 or greater:

IF hourly_wage < 10
THEN
   hourly_wage := hourly_wage * 1.5;
ELSE
   hourly_wage := hourly_wage * 1.1;
END IF;

Here is an example of a multipart conditional rule:

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 very common requirement in programming is to execute the same functionality repetitively—in a loop. Programmers call this iteration , and it is a mainstay of virtually all procedural languages.

Why would you want to use a loop? You might want to display all the book titles reserved for a given individual or separate a string of comma-delimited words. PL/SQL offers three kinds of loops to help you with this kind of processing:

FOR loop (numeric and cursor): This loop executes its body of code for a specific, limited number of iterations.
Simple or infinite loop: This loop executes its body of code until it encounters an EXIT statement.
WHILE loop: This loop executes its body of code until the WHILE condition evaluates to FALSE.

Oracle offers three different types of loops so that you can write the most straightforward code to handle any particular situation. Most situations that require a loop could be written with any of the three loop constructs. If you do not pick the construct that is best suited for that particular requirement, however, you might write more (and more complex) code than is necessary. The resulting program would also be harder to understand and maintain.

Let's take a look at each of these different kinds of loops.

Use the FOR loop when you know in advance how many times you want the loop to execute (its number of iterations). This doesn't mean you have to know the exact, literal number, just that you are able to specify start and end values. Let's start with an example where we invoke a program that shows all the books that have been borrowed from the library on a monthly basis. We want to see the borrowing from the first half of the year, so we have to invoke the program six times:

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

As you begin to write your own code, you will have many questions about the best approach to capitalization, indentation, spacing, and other aspects of programming style. This section lists some of the features of the language in this area, and should help you get started with good habits.

PL/SQL is case-insensitive (except for the values of literal strings). That means you can type keywords and identifiers in uppercase or lowercase or mixed-case—it doesn't make any difference. So all of these statements are identical:

favorite_flavor VARCHAR2(20);
Favorite_Flavor varchar2(20);
fAvOrItE_flaVOR vArCHAr(20);

O'Reilly's PL/SQL books generally recommend putting reserved words in all upper-case and programmer-supplied identifiers in all lowercase, as in the first line of the previous code. I have to admit that this seemed at first strange and inconvenient to me. Over time, though, I learned the merits of this convention—it lets my eye skate very rapidly over the contrasting type styles to find the essential information in the code.

You'll sometimes hear programmers talk of whitespace in their programs. Whitespace consists of spaces, tabs, and/or line breaks. PL/SQL allows any amount of whitespace to separate keywords and identifiers. The declaration:

favorite_flavor VARCHAR2(20);

is completely equivalent to:

   favorite_flavor VARCHAR2(20);

and also to the ludicrous:

       favorite_flavor         
VARCHAR2 
     (     20 )  ;

As you can see, PL/SQL attaches no particular significance to line breaks. Most people use line indentation conventions, but tend not to put in funky extra whitespace. Generally this means that logically subordinated code gets indented a fixed number of spaces (usually three) from the previous line. Virtually all the code in this book follows such a convention.

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

Okay, you've been introduced to block structure, variables, common operators, conditional statements, and iterative statements. As if that weren't enough fundamentals, there are a few more necessary details we want to expose you to:

The challenge of null values in SQL and PL/SQL
Naming rules for identifiers
Scope of variables
User-defined datatypes
Interpreted versus compiled code

If, however, you are a beginner who is struggling with too many new concepts, you probably want to skip this section for now, and come back to it after you've worked more with actual programs.

Anyone new to the world of Oracle is likely to have a hard time making heads and tails of NULL .

When a variable, column, or constant has a value of NULL, its value is either undefined or unknown—that is, indeterminate. "Unknown" is different from a blank or a zero or the Boolean value FALSE. "Unknown" means that the variable has no value at all and so cannot be compared directly with other variables.

Earlier in this chapter we saw how testing for null with the equality (=) operator (as opposed to the proper way, using IS NULL) puts you on the road to doom. Let's look at a few other close encounters you're likely to have with NULLs.

Section 2.9.1.1: Null strings

If a string is null, you can't really compare it with anything, as we explained in the earlier discussion of operators. However, you can still combine a null string with non-null strings and get a sensible result. For example:

DECLARE
   empty VARCHAR2(2000); -- defaults to null
   the_enemy VARCHAR2(200);
BEGIN
   the_enemy := 'blue' || empty || 'meanies';
END;

will store the string

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

Armed with the fundamentals of PL/SQL, it's now time to write programs that do more than say hello. This chapter starts to build the actual library catalog application that will accompany us throughout the book. The new language topics introduced in this chapter include procedures, functions, and packages. You will learn what they are, how to create them, and how to use them to address functional requirements.

The programming examples in this book center around building a system that will assist in the cataloging and searching of library books—a kind of "cardless" electronic catalog. In my hypothetical library, all the library's operational data is to reside in an Oracle database. How will the actual catalog information—title, author, and the like—get stored in Oracle? One way is for librarians to enter the data by hand. In later chapters, you will see a way the data can be loaded automatically from a remote source, and how library patrons search and retrieve information once it's in the catalog.

For now, I'd like to address two requirements:

Allow the creation of catalog entries for each newly acquired book
Provide a means of counting how many copies of a particular book the library owns

Implementing the first requirement demonstrates a PL/SQL procedure that inserts data into the database. The program for the second requirement will show you a PL/SQL function in action. Before you can understand how to write either of these programs, you'll need an understanding of the design of the underlying database.

As with many projects undertaken by PL/SQL developers, the database has already been designed and built for us, presumably based on the best knowledge of user requirements that was available. The database subset relevant to the two requirements at hand contains information about each copy of each book in the library. Figure 3-1 shows this design as represented with what is known as an

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 programming examples in this book center around building a system that will assist in the cataloging and searching of library books—a kind of "cardless" electronic catalog. In my hypothetical library, all the library's operational data is to reside in an Oracle database. How will the actual catalog information—title, author, and the like—get stored in Oracle? One way is for librarians to enter the data by hand. In later chapters, you will see a way the data can be loaded automatically from a remote source, and how library patrons search and retrieve information once it's in the catalog.

For now, I'd like to address two requirements:

Allow the creation of catalog entries for each newly acquired book
Provide a means of counting how many copies of a particular book the library owns

Implementing the first requirement demonstrates a PL/SQL procedure that inserts data into the database. The program for the second requirement will show you a PL/SQL function in action. Before you can understand how to write either of these programs, you'll need an understanding of the design of the underlying database.

As with many projects undertaken by PL/SQL developers, the database has already been designed and built for us, presumably based on the best knowledge of user requirements that was available. The database subset relevant to the two requirements at hand contains information about each copy of each book in the library. Figure 3-1 shows this design as represented with what is known as an entity-relationship diagram (ERD).

Figure 3-1: Relationship between books and their physical copies

This kind of diagram captures succinct information about the real world. The labeled boxes correspond to database

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 fairly simple place to begin is by writing a PL/SQL program that will add a new book to the database. Of course, I could just write a SQL INSERT statement (or two) whenever I need to perform this function:

INSERT INTO books (isbn, title, author)
VALUES ('0-596-00180-0', 'Learning Oracle PL/SQL, 'Bill Pribyl with Steven Feuerstein');

Why would I ever bother writing a PL/SQL program?

Say I have two different places where I need to add books to the catalog: one needs to be interactive, enabling hand-entry of the input data, and one automatic, retrieving the book's properties from a remote database. So now what do I do? Duplicate the INSERT statements in these programs? And maybe I later write a third program that adds book records by reading them off a CD-ROM. Just cut and paste another copy of the INSERTs, right? Now pretend the design of those tables changes, and I have to change all of my programs. Oops.

There are several good reasons to put the INSERTs into a PL/SQL program, but the most important benefits are:

To reduce, if not eliminate, a lot of tedious, error-prone software maintenance work when the database structure changes
To help optimize database server performance
To centralize complexity

Writing correct SQL statements may require interpreting and coding a lot of complicated business rules; having to re-code all this logic in every application is a needlessly risky waste of time and effort. The general principle is:

Centralize SQL statements in reusable PL/SQL programs, rather than scattering them helter-skelter throughout various applications.

Even if you are the only programmer in your organization, you should still follow the localization guideline. In fact, this advice actually extends beyond SQL statements; you should program each behavior only one time, and call it whenever needed. By defining the tasks each reusable program unit will perform, you are well on the road to establishing your own

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 trying to write the function, let's examine the generic syntax for creating this second type of stored program.

Here is the template for creating a function. As you look it over, you'll probably realize that you have seen most of these elements before, other than those in boldface.

CREATE [ OR REPLACE ] FUNCTION procedure_name 
   (parameter1 MODE DATATYPE DEFAULT expression,
    parameter2 MODE DATATYPE DEFAULT expression,
    ...)
RETURN DATATYPE
               
AS
[ variable1 DATATYPE;
   variable2 DATATYPE;
   ... ]
BEGIN
   executable_statement;
   RETURN expression;
[ EXCEPTION
   WHEN exception_name
   THEN
      executable_statement; ]
END;
/

The differences between this function template and the procedure template are minimal. In addition to the fact that the CREATE statement says FUNCTION instead of PROCEDURE, this code differs from a procedure in only two places: the header, which specifies the returned datatype, and the body, which must explicitly convey a value back to the caller.

RETURN datatype: In the header, the RETURN clause is part of the function declaration. It tells the compiler (and other programs) what datatype to expect back when you invoke the function.
RETURN expression: Inside the executable section, this use of RETURN is known as the return statement, and it says "Okay, I'm all done; it's time to send back (return) the following value." You can also put a RETURN statement in the EXCEPTION section.

Both of these are required. You can see an example that fills out this template in the next section.

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

You are probably eager to get going on making our sample application do more—so am I! But it is important to first make sure the code we've written so far works as flawlessly as it possibly can. That's why I'm going to take what might seem like a digression.

You've probably heard the expression garbage in, garbage out (GIGO). Maybe you've even uttered this phrase from time to time, or heard it over the phone from support staff; it's supposed to "explain" some nonsensical result (garbage out) by blaming faulty input (garbage in).

But is GIGO an inevitable state of affairs? Most programmers are incorrigible optimists when it comes to thinking about how their programs will be used. The assumption is tidy in, tidy out. Nobody wants to plan for inputs they consider to be "abnormal."

To avoid unanticipated digital squalor, we have to run test cases. To run good test cases means dreaming up various combinations of input data that we hope will break the program. Then we note the expected results...run the program...compare the output...fix the program...rerun the tests. Yep, that's a lot of bookkeeping that I would certainly prefer to avoid.

Hmm, lots of tedious executions of code with different inputs; this sounds like a good opportunity to write some utilities, doesn't it? Let's create one ourselves and see if we can make this testing stuff more fun—or at least automate the tiresome bits.

First, I'd like to create a simple way that will compare two values and print out a "pass" message if they're the same, or a "fail" message if they differ. This test is enormously useful, since the basis of testing is comparing the actual output to the expected response. We'll probably use such a procedure every time we run a test. While we're at it, let's throw in a description for the test, so that when we call this a bunch of times back-to-back we can keep up with which tests have failed. Have a look at this "report equality" (reporteq ) procedure:

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

At this point we've written PL/SQL to handle a few of the catalog tasks, plus we've written some unit testing code and utilities.

Now it's time to identify some of the shortcuts we have taken with our overall requirements and design, and figure out how we're going to overcome the resulting limitations.

There are a lot of things that we've completely ignored in the code shown so far. For example:

What happens if the record in the books table already exists? Is that the same thing as adding a new copy of the book?
How can the librarian modify information in the catalog?
What if the book gets "weeded," lost, or otherwise removed from the library? How will we use PL/SQL to record that fact in the database?
What if there are lots of different kinds of database lookups (queries) we'll need to do, such as retrieving books based on various search criteria?

Clearly, by the time this thing is done, we're going to wind up with a lot of bits and pieces of code that support related, but not identical, tasks. Wouldn't it be nice if there were a way to organize this code to make it easier to build and manage? There is, and it's called a package.

A PL/SQL package is a named container that can hold any number of procedures and functions. Packages can hold other constructs too, such as exceptions, variables, and type declarations, and later we'll see how incredibly useful these additional features can be. For now, though, we'll start by putting only program units into our package.

While it's true that other programming languages like Java and Ada have a construct called a package, PL/SQL's rendition has its own unique definition and idiosyncrasies (that's just something to keep in mind if you have encountered those other languages).

For reasons that will become clear as we go on, packages usually have two parts: a

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

As you gain some proficiency with procedures, functions, and packages, you may begin to wonder how you can take your work to the next level of sophistication. Here are several tips and suggestions for doing so.

As a practical matter, an issue that you will face is what to name your files of source code when you store them in the operating system. When assigning names to operating system files containing PL/SQL code, I tend to use the file extension to give a clue as to what's inside the file. Although Chapter 6 will revisit the issue of file naming in some detail, here are some guidelines to get you started:

Filename pattern	Contents
name `.pro`	(Standalone) stored procedure
name `.fun`	(Standalone) stored function
name `.sql`	Anonymous block or script containing multiple blocks, SQL statements, and/or SQL*Plus commands
name `.pks`	Package specification
name `.pkb`

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 working title of this chapter was "Treasure In, Treasure Out," a phrase that sums up how most of us start out wanting our programs to behave. I've tried to spice up this chapter with various lessons on programming defensively—that is, programming in such a way that you assume the worst conditions will happen. Your programs should be able to deal with garbage in without producing garbage out.

There are a variety of ways to prevent "garbage out syndrome." We've looked at a few of them in the course of creating a package that services and protects the book data in the database. To summarize:

Always remember the possibility that PL/SQL variables and parameters can be null, especially when programming IF-THEN logic.
Build and use "table wrappers" with PL/SQL; develop the programming discipline needed to use the approach consistently.
When declaring parameters for stored routines, give them default values wherever it makes sense to do so.
In general, prefer named notation to positional notation, especially when it adds information that needs to be present.
Avoid duplication in your code; doing so will make future modifications less prone to errors.
Organize your code into packages rather than into a lot of standalone procedures and functions.
Handle exceptions where doing so makes sense, but raise exceptions if your program might encounter problems it shouldn't be deciding how to solve.
Use overloading to transfer complexity away from the developer and into the system, simplifying future development.
And finally, you should write some sort of companion test routine for every program unit that you create.

In the next chapter we'll start to expand the system outward, toward the end user, by developing a user interface for some of the book management features we've just programmed.

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 the previous chapter, we introduced the library application and built its basic book management features. But the system we've built so far doesn't give end users any way to take advantage of these features. Because we probably don't want everyone in the library lining up at our office door asking us to make entries in the catalog, we'll want to build a user interface for the system.

When building the application's user interface, or front end, the first decision is what overall style the application will have: does it need to look like a typical Windows application, with toolbars, fancy onscreen doodads, and sophisticated online help? In our case, probably not, particularly considering our development budget. Does it need to run from the command line? Almost certainly not. Instead, making the front end web-based—that is, accessible from a web browser like Netscape—makes sense for a number of reasons:

Almost everyone in the user community is comfortable surfing the Web, so they should be comfortable with an application they can run from a web browser.
We want to provide the same interface to users whether they are logged in locally or remotely, a requirement that is easy to fulfill with a web-based program.
We don't want to have to install special software on each computer workstation.
We can build the entire application in PL/SQL.

Before we get into the details of building our web-based user interface, let's make sure you understand some basic web concepts. What I want to show next is a quick overview of how web pages come into the world, both with and without PL/SQL. This discussion begins with an introduction to writing web pages using standard HyperText Markup Language (HTML).

If you're working in an environment where you aren't currently building web-based PL/SQL applications, you might be tempted to skip this chapter. Given how prevalent web-based applications are these days, though, we recommend that you read on. It's only a matter of time before you'll need to know how to use the features described here.

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 World Wide Web was not invented by Oracle, Microsoft, IBM, Netscape, or any other software company. It was, in fact, created by a physicist at a laboratory in Switzerland, Tim Berners-Lee, who wanted a platform-neutral way for researchers around the world, already joined via the Internet, to share and link their work. Since improved communication, rather than commercial gain, motivated the Web's inventors, open standards rather than proprietary technologies are its lifeblood.

Even the language of the Web, HTML, one of a broad class of markup languages , is derived from a more mature open standard called Standard Generalized Markup Language , or SGML. The earlier s

Table of Contents

Section 2.4.1.1: Strings

Section 2.9.1.1: Null strings