Digg This!     StumbleUpon     Del.icio.us

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 procedure.) The overall arrangement and flow of the example is as follows:

• In the program specification, declare the program name and the parameters it will accept. Here, we accept a barcode ID and the date the book was returned. If the calling program does not supply a return date, the program defaults to use the current date (based on the database server machine's internal clock).
• In the declaration section, define variables that will be used inside the program, including a "cursor" that will allow us to query the table.
• In the first executable statements, open and fetch from the cursor to attempt to retrieve a record that corresponds to the supplied .
• If no such record exists, log an error message (by raising, and then handling, an "exception") by invoking a separate stored program that we have previously written, .
• If a matching transaction record does exist, update it to reflect the fact that the book has been returned.
• Compare the check-in date to the due date, and assess a fine if the book is returned late.

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:

• The CREATE PROCEDURE statement causes Oracle to load the program into the database server. If everything succeeds, the procedure remains in the database, available to execute later. Chapter 3 discusses more about creating stored procedures.
• PL/SQL uses "blocks" to organize code, and the blocks are delineated by keywords including BEGIN and END. Details are in Chapter 2.
• PL/SQL programs are often populated with many SQL statements such as SELECT and UPDATE. Conveniently, these SQL statements drop right into the code without much fuss.
• When retrieving data through a SELECT statement, you will fetch one row at a time using a thing called a cursor. A detailed discussion of this appears in Chapter 4.
• You can use PL/SQL program variables directly in the embedded SQL statements. In the first UPDATE statement in the example, Oracle assigns the value of the variable to the value in the table's column.
• PL/SQL is a "readable" language. Well, it should be, anyway, if the programmer uses reasonable names and follows simple coding conventions. The value of readability will become apparent the first time you have to make a change to some code that someone else wrote!

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.

Few tools for user interaction

Although it has many constructs and built-in features for interacting with data in the database, PL/SQL has few tools for interacting directly with the user. Yes, there is a rudimentary way to get textual output from a PL/SQL program, but there is no direct way to receive input from the user. You will typically use another language as the front end [5] of your application, and it will pass your input to PL/SQL. In some ways this is a good thing, because it forces you to separate the concern of data management from the concern of user interface design. See Chapter 4 for examples of using a web-based front end to PL/SQL.

Proprietary language

PL/SQL is proprietary to Oracle Corporation and is not useful with any other vendor's database product. While there are some ways to integrate the Oracle database server with other vendors' servers, PL/SQL won't execute anywhere but Oracle. This is unfortunate for independent software vendors who prefer to build their database-aware products to run against different databases. Also, very large companies suffering from "let's get one of everything" syndrome are unlikely to settle on PL/SQL as their standard language for procedural database programs.

Limited object-oriented features

(Beginners, skip this paragraph.) Up until Version 9, PL/SQL was lacking in object-oriented programming language features, although Version 8 did add support for abstract datatypes. Object-based programming was even reasonable to achieve using PL/SQL packages in Version 7. Oracle9i introduced more object-oriented features, such as multi-level collections, inheritance, and runtime polymorphism, although there are still some unfortunate limitations such as no private methods.

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?

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:

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

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.

Why Is PL/SQL As Fast As It Is?

Executing in close proximity to the data in the database, PL/SQL allows for highly efficient database reads and writes. Why?

First, PL/SQL's variables store data in the same internal binary format as the database. For example, when retrieving a numeric value from the database, you don't have to convert it from one set of bits in the database to a different set of bits in the program; you just make an exact copy of the bits. By contrast, if you read numeric data from the database into a Java program, you are likely to need to convert it into something like a Java "BigDecimal." Now, this may seem like a point only a geek could love, but, when multiplied by thousands or millions of occurrences, it can turn out to be a big impact--not only in ease of programming, but also in ease of performance.

Second, server-side PL/SQL gets executed inside the same running program (in the same memory space) as the Oracle server itself. This translates into another performance win because there is extremely little communications overhead required for the program to talk with the database. Normally, this overhead would be either in the form of network bandwidth or in the CPU power and memory required to use the computer's internal messaging system known as inter-process communication.

It is true that PL/SQL has for years been an "interpreted" language rather than a true "compiled" language, resulting in some kinds of operations being slower. Even though millions of users found PL/SQL's interpreted performance to be acceptable, Oracle introduced a native execution feature in Oracle9i that can dramatically accelerate execution speeds. It actually translates your PL/SQL into C and compiles it into machine-specific binary form. See Chapter 9 for more details about compiling and native execution.

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:

Fewer things to break

By relying on stored procedures, there are fewer "moving" parts in the overall system. Controlling a development effort with client, middle-tier, and server-tier components requires not only inspired and skillful managers but also, in many cases, a great deal of luck. In addition, as time goes on, the evolution of a system with fewer components is likely to be simpler and less expensive.

Centralized consistency

Stored procedures provide greater assurance of data integrity. It's just easier when you don't have to secure both the database and a middle tier. The term "secure" here encompasses both privileges (Joe has the privilege to update the table of accounts) and business rules (no transactions permitted on accounts more than 30 days past due).

Performance

Stored procedures can potentially yield greater performance, as discussed in the sidebar "Why Is PL/SQL As Fast As It Is?"

Developer productivity

Stored procedures can facilitate greater productivity when you write applications with products that assume the presence of tables in the database. In Oracle, you can, for example, write stored procedures that allow other programs to insert, update, and delete through database views.

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.
• You can call PL/SQL functions directly from SQL.
• PL/SQL can run without human intervention.
• Many cool features are only accessible via PL/SQL.

Let's look at each one in turn.

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:

That's pretty cool--it means we can create our own extensions to SQL!

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

Lessons From My Father on the "Best" Language

Rewind to the last millennium, back when I was about eight. I used to spend long hours working in my father's workshop, "helping" him with his latest project. A natural tinkerer, my father was always building some gadget or gizmo, and I'd hammer and saw right along with him. Although many of his creations are still in use today, I get more frequent mileage from one of his oft-demonstrated lessons that really stuck with me:

You gotta use the right tool for the job.

In my father's world, this message surfaced repeatedly, forcefully, and unforgettably. When confronted with a Phillips head screw, don't use a flat-bladed screwdriver just because it's the one in your pocket. Use clamps to hold stuff you've glued while it's drying. Only on rare occasions is it okay to install a screw with a hammer. And so forth.

This goes a long way toward explaining my maxim that you shouldn't fight any meaningless battles over what the "best" language is, because each has its uses. In other words, use the right tool for the job.

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.

Criteria

PL/SQL

Java

Suitable for stored procedures in...

Oracle (all currently supported versions)

Oracle8i, DB2, Sybase, Informix, several others (but not Microsoft's SQL Server)

Suitability for use in other tiers

Client-side or middle tier when using Oracle tools

Client-side or middle tier with many vendors' products

Portability of your programs to different DBMSs

None

Excellent if programmer avoids proprietary database features

Conciseness of code for common database tasks

Excellent

Not so great

Portability of your programs to different operating systems

Excellent

Excellent

Overall performance and scalability in database usage

Excellent, particularly using native execution in Oracle9i

Scales well, but some performance problems with lots of database interaction

Well-supported programming styles

Functional decomposition; object-based

Object-oriented programming (OOP)

Ease of learning

Moderately easy

Steep curve; requires knowledge of OOP to use well

Ratio of features to complexity

Excellent

Fair

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.

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:

1. Access to a "big enough" machine running an operating system supported by Oracle
2. A licensed copy of Oracle's server software, available free (with some restrictions) from Oracle's web site
3. A text editor
4. 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.

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:

• 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:

• Pentium 166MHz or better processor
• 96-megabyte RAM (256 megabytes is recommended)
• 2 gigabytes of disk space

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:

• Windows NT, Windows 2000, and Windows XP, Professional (some Oracle versions are even available for Windows 98)
• Intel Linux
• Sun Sparc Solaris (a Unix flavor that runs on Sun and Sun-compatible hardware)
• Some Oracle versions are available for other Unix flavors such as Compaq Tru64 Unix and IBM's AIX

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

The next step is to acquire and install 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:

• A file in the root directory of the installation media called index.htm or index.html. If you find it, open it in your web browser.
• Anything in a relnotes (Release Notes) subdirectory.
• Anything with README in its filename (and also the lowercase readme, if your operating system is case-sensitive), especially files with rdbms in their names.
• Anything in a doc subdirectory, especially if there is a subdirectory rdbms/doc.

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.

Environment

Common text editors

DOS

edit

Windows

Notepad, Wordpad (in text-only mode)

Unix, Linux

vi, GNU emacs

Macintosh

Teachtext, Simpletext

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.

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
• Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

Rate this Tip To rate tips, you must be a member of SearchOracle.com. Register now to start rating these tips. Log in if you are already a member. Submit a Tip DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.