What Is Structured Query Language?

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:

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

Language Categories Saying that PL/SQL is a procedural language makes more sense when you understand some other types of programming languages. There are at least four ways to categorize popular languages.[A]. Procedural programming languages Allow the programmer to define an ordered series of steps to follow in order to produce a result. Examples: PL/SQL, C, Visual Basic, Perl, Ada. Object-oriented programming languages Based on the concept of an object, which is a data structure encapsulated with a set of routines, called methods that operate on the data. Examples: Java, C++, JavaScript, and sometimes Perl and Ada 95. Declarative programming languages Allow the programmer to describe relationships between variables in terms of functions or rules; the language executor (interpreter or compiler) applies some fixed algorithm to these relations to produce a result. Examples: Logo, LISP, Prolog. Markup languages Define how to add information into a document that indicates its logical components or that provides layout instructions. Examples: HTML, XML. A. These category definitions are derived from an indispensable resource edited by Denis Howe called The Free On-line Dictionary of Computing, copyright 1993 by Denis Howe.

Structured Query Language is a language based on set theory, so it is all about manipulating 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:

PL/SQL is a procedural (Algol-like) language with support for named program units and packages; much of its syntax is borrowed from Ada, and from Oracle's SQL it derives its datatype space and many built-in functions.

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.

Also New to SQL? If you're completely new to the relational database world, you will also want to learn more about SQL, which is beyond the scope of this book. Fortunately, or perhaps unfortunately, there are hundreds of SQL training materials on the market, including many web sites and books. Although neither of O'Reilly's two books on SQL qualify as tutorials, you may still find them helpful to have on your bookshelf: Oracle SQL: The Essential Reference, and SQL in a Nutshell: A Desktop Quick Reference, the latter of which addresses multiple vendors' versions of SQL (Oracle, Microsoft, PostgreSQL, and MySQL). A popular tutorial-style book is the Oracle SQL Interactive Workbook by Alex Morrison and Alice Rischert. As far as web sites go, you might try "SQL for Web Nerds" at www.arsdigita.com/books/sql.

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

Example 1-1: A Wimpy PL/SQL fragment

BEGIN
   IF TO_CHAR(SYSDATE, 'DAY') = 'TUESDAY'
   THEN
      pay_for_hamburgers;
   ELSE
      borrow_hamburger_money;
   END IF;
END;

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

pay_for_hamburgers; 

and

borrow_hamburger_money;

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.

Figure 1-1. Example PL/SQL stored procedure for handling library book returns

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 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 borrowing_transaction table.


• In the first executable statements, open and fetch from the cursor to attempt to retrieve a record that corresponds to the supplied barcode_id.


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


• 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 trunc_return_date to the value in the table's return_datecolumn.


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

Figure 1-2. Simplified representation of PL/SQL in the Oracle Server

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?

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:

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

Table 1-1: Simple code fragment in PL/SQL and Java

PL/SQL	IF return_date_in > l_due_date THEN days_late := trunc_return_date - TRUNC(l_due_date); UPDATE borrowing_transaction SET fine_amount_usd = NVL(fine_amount_usd,0) + days_late * daily_fine_usd WHERE barcode_id = barcode_id_in; END IF;
Java (using JDBC)	if (returnDate.after(rs.dueDate)) { s = "UPDATE borrowing_transation "; s += "SET fine_amount_usd = NVL(fine_amount_usd,0) "; s += "+ (TRUNC(?) - TRUNC(?)) * ? " s += "WHERE barcode_id = ?"; PreparedStatement ps = aCon.prepareStatement(s); ps.setDate(1, returnDate); ps.setDate(2, dueDate) ps.setInt(3, dailyFineUSD); ps.setString(4, barCodeID); ps.executeUpdate( ); }

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