Oracle PL/SQL Tutorial: What, when, and where

This is Chapter 1 from the O'Reilly book "Learning Oracle PL/SQL" by Bill Pribyl & Steven Feuerstein. You can purchase the book here.

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.

What Is PL/SQL?

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:

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.

Structured Query Language is a language based on set theory, so it is all about manipul

To continue reading for free, register below or login

Requires Membership to View

To read more you must become a member of SearchOracle.com

RELATED RESOURCES

	2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems
	Search Bitpipe.com for the latest white papers and business webcasts
	Whatis.com, the online computer dictionary

ating sets of data. SQL consists of a relatively small number of main commands such as SELECT, INSERT, CREATE, and GRANT; in fact, each statement accomplishes what might take hundreds of lines of procedural code to accomplish. That's one reason SQL-based databases are so widely used. The big joke about the name "SQL" is that it is not really structured, is not just for queries, and (some argue) is not even a real language. Nevertheless, it's the closest thing there is to a lingua franca for relational databases such as Oracle's database server, IBM's DB2, and Microsoft's SQL Server.

A language extension is a set of features that somehow enhance an existing language. This phrase might imply, incorrectly, that PL/SQL is a special version of SQL. That isn't the case, however. PL/SQL is a programming language in its own right; it has its own syntax, its own rules, and its own compiler. You can write PL/SQL programs with or without any SQL statements. Some authors assert that PL/SQL is a superset of SQL, but that's a bit of an overstatement, because only the most common SQL statements can be used easily in a PL/SQL program.

PL/SQL, then, is a language that is closely related to SQL, but one that allows you to write programs as an ordered series of statements. Or, if you want a definition of PL/SQL that befits a programmer:

But if that doesn't make any sense, don't worry about it! You'll get the same message in plain English in the forthcoming pages.

Why SQL Is Not Enough

As a beginner in the world of relational databases, you might wonder why SQL, which is supposed to be so wonderful, isn't always enough. It is true that SQL's high-level operations are a big boon to programmers dealing with relational databases, but the real world of programming includes many tasks other than straight database manipulation. SQL is not a general-purpose language for expressing computer algorithms. Although you can build a SQL "program" that consists of a sequence of SQL statements, such a program could not have any "conditional" statements. That is, SQL has no convenient way to say, "IF something-is-true THEN do-this OTHERWISE do-something-else."[1] But PL/SQL handles such logic with ease (as shown in Example 1-1).

In addition to the IF-THEN-ELSE statement, this PL/SQL code fragment shows BEGIN...END delimiters, none of which you'll find in SQL. Borrowed from SQL, though, are the SYSDATE function, which returns the date and time on the system clock at the moment that you call it, and TO_CHAR, which converts the date bytes to something understandable such as the day of the week. The statements

are the names of stored procedures [2] we have presumably created. Inside a PL/SQL program, putting a procedure's name alone on a line like this causes it to execute. Of course PL/SQL is much more than IF statements and procedure calls. PL/SQL replaces those procedural ingredients that SQL took out: sequential, conditional, and iterative statements, variables, named programs, and more.

In addition, SQL comes up lacking when you need to protect and secure your data in a sophisticated way. If you try to rely only on SQL to enforce security, your database administrator (DBA) has some control over who can change the data, but no control over how they can change it. So Herman in accounting might receive UPDATE privilege on a receivables table. You might try to control what operations he can perform by programming some business rules in a Visual Basic program that he uses. Well, he's supposed to use it, anyway! If he happens to have, say, Microsoft Excel on his desktop computer, and if he happens to also have connector software [3] to let it talk to Oracle, boom! Herman can bypass all your carefully programmed security checks!

Without PL/SQL, it is quite easy to expose your data to intentional or unintentional tampering. Using PL/SQL as a programming tool (particularly in combination with a feature introduced in Oracle8i called "Fine Grained Access Control") can help lock up this "back door" into the database. Chapter 7 examines PL/SQL's security features.

A Meaty PL/SQL Example

Enough talk, let's code! Drawing from the world of the neighborhood library, here is a PL/SQL stored procedure that might run when a patron returns a book to the library. The example in Figure 1-1 expresses a lot of ideas about PL/SQL. At this point, you will probably just want to scan it for pieces that seem interesting, and then proceed to the discussion that follows.

The idea behind this program is that it would support a library clerk who checks in books by scanning them with a barcode reader. (There would be some other program to supply the barcode identifier and, optionally, the date when the book was returned to this return_book procedure.) The overall arrangement and flow of the example is as follows:

In this prose summary, the program should make at least some sense. I won't discuss the details of the code here, but there are a few things I would like to emphasize that might not be apparent in the figure:

Now that we've seen a short but rich example of PL/SQL, let's take a look at how PL/SQL fits into the big picture of the Oracle database.

What, exactly, does it mean that PL/SQL executes "inside the database server"? To understand the answer, it's helpful to know a bit about how the database works.

As illustrated in Figure 1-2, client programs can make calls to a PL/SQL program running inside the Oracle database server. Virtually any database-aware programming environment can invoke PL/SQL stored procedures: Visual Basic, C, Java, even another Oracle database. The stored routines can, in turn, call others in a very efficient manner, performing manipulations of the database, computations, or lookups as needed by the program that originally made the request. Results and status codes then pass back out to the calling program. The figure also shows that in an Oracle database server, all contact with the data on disk goes through a core set of background processes, and PL/SQL runs quite intimately alongside these processes. The net result is a high-performance database that can have a lot of "smarts" supplied by the programmer.

PL/SQL can also run on client machines that are not running a database server but that can talk to the database server machine over a network. This kind of arrangement would use Oracle's application development tools like Oracle Forms. [4] However, this book concentrates on server programming rather than client programming. We've chosen to do this because it enables the book to focus on the language features that are common to all PL/SQL programmers. In addition, client-side development with Oracle products is one of several ways to build applications, but server-side PL/SQL is the principal method for programming stored procedures when using Oracle.

What PL/SQL Is Not

As useful as PL/SQL is, there are things it isn't, or can't or won't do--not without a bit of smoke and mirrors, anyway. We'll discuss ways of working with some of these un-features later in the book.

Now just hold on here, you're saying to yourself--if PL/SQL is often only part of a complete application, and only works with the Oracle database, why not just use one of the multi-purpose languages like C or Java for everything? Why bother with PL/SQL at all?

Why Use PL/SQL?

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.

"I'd Rather Use a `Real' Language Like C!"

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:

As Oracle began to mature, though, the database industry began to see the wisdom of moving processing logic into the database server itself. Even though C can be the right answer in many cases, a C program will always execute outside the database server; it cannot be used to program a true stored procedure.

Why Should I Use Stored Procedures at All?

Although there are many arguments in favor of stored procedures, they have evolved a bit over the years. Back when the stored procedure feature was new, you had only two choices for where to locate the Oracle application logic: the client, which was usually a PC, or the database server, which was usually running on a higher-powered minicomputer. It was easy to make a case in favor of using stored procedures by pointing out their help in centralizing complex code, securing the database, reusing software, and increasing performance.

Nowadays, though, a common arrangement is to use one or more middle-tier machines between the client (which is now often a simple web browser) and the server. The middle tier typically runs the application logic on some convenient platform using some convenient language. Many of the benefits of using stored procedures can accrue to this multitiered arrangement, and the arguments in favor of stored procedures have evolved since the early days. I've narrowed them down to only four basic arguments, but they are critical:

Okay, let's assume you like the sound of those four benefits, that you are using Oracle, and that you definitely or possibly want to use stored procedures. It does not automatically follow that you should use PL/SQL; you might prefer to use the Java programming language, which Oracle supports as an alternative. If you have time to learn only one language, which should it be?

"Hey, Isn't Java Where It's At?"

A lot of Oracle programmers wonder whether they would be better off using Java for all their stored procedures. It is true that Java offers some features that are impossible to program directly in PL/SQL. But there are several striking advantages to using PL/SQL. First off, PL/SQL can offer superior performance to Java, as discussed in the earlier sidebar, "Why Is PL/SQL As Fast As It Is?" Another major argument in favor of PL/SQL is that as a companion to SQL, PL/SQL offers uniquely close integration. This section explores four examples of this integration:

PL/SQL is more concise than Java

Using SQL statements within PL/SQL is free of programming "cruft" (programmer's slang for superfluous code). Without getting into the finer points about cursor-FOR loops, automatic declarations, statement parsing, etc. (described in later chapters), suffice it to say that PL/SQL can accomplish more using fewer lines of code than any other SQL-hosting programming language you care to use with Oracle. Well, at least when compared to Java. Take a look at the code fragment in Table 1-1.

In the Java/JDBC [6] version of this code fragment, you have to use question marks as variables, and then bind data to them as separate steps. What a pain. (And keep in mind that these are not complete programs. The actual comparison can be worse than this.)

You can call PL/SQL functions directly from SQL

Calling PL/SQL functions directly from SQL statements can often make your SQL shorter and more manageable. For example, you can define a PL/SQL function that computes some exotic mathematical relationship, and someone can later use that function in a SQL SELECT statement. In a library, maybe they have an algorithm for computing a book's popularity based on how frequently it gets checked out and how many times patrons request that it be held for them. This statistic, combined with the number of copies, helps the library determine whether to purchase any new copies of the book. We could create a PL/SQL function that computes a "scarcity" factor, and then write a relatively simple query to create a report:

PL/SQL can run without human intervention
(and without any obvious divine intervention)

PL/SQL can be triggered automatically by other events in the database. For instance, if you want to get an email when a particular book gets returned to the library, PL/SQL can send it. Examples of trapping database events with table-level or database-level triggers written in PL/SQL appear in Chapter 7 and 8.

Many cool Oracle features are only accessible via PL/SQL

Although Oracle rarely spells out this fact in black and white, there are extremely useful features such as Replication (for automatic copying of data between databases), the Spatial option (for the storage of maps and other location-dependent data), and Time Series (to help manipulate data with a strong time component, such as stock prices) that will require you to learn at least a little about PL/SQL. That's because these features currently have no alternative programming interface. (If truth be told, though, there are a few Oracle features, such as programming for the Internet File System or iFS, that are only available from Java.)

When Is PL/SQL the Right Tool for the Job?

How will you recognize the job for which PL/SQL is the right tool? Wrong answers may not be as obvious as when you pound a screw with a hammer. Right answers sometimes require quite a bit of experience with a number of different languages, plus sufficient scrapes, bruises, and hair-pulls from years of trying things the wrong way (my thumb is healing up nicely, thank you, but my hair is still a little thin).

Table 1-2 summarizes some of the major differences between the two languages, in very high-level and admittedly subjective terms.

Most business programmers (that is, programmers such as MIS staff who work in non-software industries) tend to prefer PL/SQL because it is a lot easier to learn and use than Java, which can be cryptic and verbose. In contrast, many software-industry programmers often prefer Java because of its object-oriented features and wide support. Of course, you have to factor in your existing programming skills and knowledge; are you more likely to build the desired system(s) in the allotted time in PL/SQL or in another language?

The Best of All Worlds

It turns out that you can integrate PL/SQL in conjunction with many other popular languages. If you're already writing in a language like C, Perl, Java, Ada, FORTRAN, or COBOL, you can use it to call your PL/SQL programs. You can pass data and other information (like exceptions, covered in Chapter 3) back and forth. The way you call PL/SQL programs from these other languages is usually a simple extension of the way the other language calls SQL. In fact, in some cases it's easier to call PL/SQL from one of these languages than it is to call SQL, because you can migrate iterative program logic into PL/SQL, thereby replacing (in some cases, anyway) a lot of tedious fetch loops with a single call to a named PL/SQL program.

In addition, with a bit of cleverness, PL/SQL can also invoke programs written in these other languages. See Chapter 8 for an introduction to the use of external procedures.

What You Need to Get Started with PL/SQL

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:

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

Hardware and Operating System

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

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:

As you can see, the actual hardware requirements depend on the Oracle version and options you want to use (and, to a lesser extent, on the operating system). As for the operating system, Oracle generally provides licenses for developers (see the next section) on the following:

It is probably not sufficient to have the version of the operating system that happened to come "out of the box" with the hardware. In addition to matching the exact version number that Oracle supports, you must ensure that the operating system on the machine has the proper patches (or service packs) installed.

What's the "best" hardware and operating system for Oracle? For a beginner, my answer is always "whichever one you are most comfortable with." Too many new toys makes your life unnecessarily complicated.

A Licensed Copy of Oracle

Acquiring Oracle

Oracle offers a single-user, development-only license for free, as long as you agree to a lot of legal fine print. To obtain a copy of the Oracle server software for use by an individual developer, you can visit the Oracle Technology Network (OTN) web site, sometimes known as Technet, at http://otn.oracle.com. If you have a very fast Internet connection or a lot of time, you can actually download a copy of the software itself. Be warned, though--you may have to download more than a gigabyte of stuff!

If a 48+ hour (at 56K) Internet download isn't your idea of fun, you may be able to order what they call a "CD Pack," currently around $40 in the U.S., or possibly a "Technology Track" subscription, which I bought at one point for about US$200/year (but it was not available to order the last time I checked). Maybe they have some new deal by now.

When downloading or ordering, you will at some point have to designate which version of which Oracle server you want. After identifying your hardware, you need to choose a version of the database server. My suggestion for beginners is to get the latest available version of the Enterprise Edition unless your organization has a specific requirement for you to learn or support something else. The Personal Edition is probably okay too; I believe it actually includes almost all the features of the Enterprise Edition.

Installing Oracle

I wish I could include detailed instructions, but the installer varies according to Oracle version and behaves slightly differently on different platforms. Instead, I will point you to the relevant documentation that will help you do the installation properly. In addition to Oracle's Installation Guide (IG) appropriate to your platform, look in particular for:

Some of these documents may be available on the OTN web site, but others might only be available after you download and "expand" the software and start poking around in the resulting directories and files. And some may only be available after you've actually installed the software!

If you've never installed Oracle before, I recommend using as many of the default settings as possible. You can almost always rerun the installer later and add or modify the options. I will also mention that if the installer gives you the choice, be sure to install the built-in web server features, known as "Oracle HTTP Server powered by Apache," or some combination of those words. It may also give a URL to the local web server's administrative page; be sure to write down or copy relevant information.

A Text Editor

A text editor is a program that allows you to create and modify documents such as programs that consist of text only--that is, no fonts, borders, colors, graphics, or other fancy stuff. I've included this requirement as something of a joke, because, as Table 1-3 illustrates, each operating system includes a text editor of some kind.

Of course, in addition to the hundreds of different text editors available, there are also commercial programmer's editors and entire interactive development environments available, some of which are built specifically for PL/SQL. Chapter 6 describes these in more detail, although as something of a traditionalist, I don't recommend that beginners use these tools right away.

1. More advanced readers may correctly point out that Oracle's version of SQL includes a nonstandard though useful function known as DECODE, which provides a crude if-then construct.

2. A stored procedure is a program that resides and executes inside the database server. Most of this book is about stored procedures.

3. A common mechanism for this purpose is known as Open DataBase Connectivity (ODBC), used in widely available tools such as Microsoft Excel. Oracle's ODBC "drivers," as they are called, are freely downloadable from Oracle Technology Network (OTN) web site, sometimes known as Technet, at http://otn.oracle.com.

4. Over the years, these tools have been known by a variety of names, such as Internet Developer Suite, Oracle Developer, and Oracle Developer/2000.

5. Somewhat anthropomorphically, the terms back end and front end generally refer to the server and client parts (or the computational and user interface portions) of an application. As an example, a web browser might display the front end, and a server machine running the application might be the back end.

6. JDBC is the standard Java library for connecting Java programs to SQL-based databases. JDBC doesn't officially stand for anything, but most people think of it as Java DataBase Connectivity.