Since Oracle PL/SQL Programming was first published in 1995, it has had a busy and productive history as the "go to" text on how to use the PL/SQL language. For that, we first of all express our appreciation to all of our readers.
Maintaining Oracle PL/SQL Programming as an accurate, readable, and up-to-date reference to PL/SQL has, from the start, been a big (all right, we admit it—sometimes overwhelming) job; it certainly would not have been possible without the help of many Oracle specialists, friends, and family, and of course the incredible staff at O'Reilly & Associates.
You will find below rather detailed thank yous for those who helped pull together the third edition of Oracle PL/SQL Programming. Following that, you will find an acknowledgment of the many people who were instrumental in the first and second editions. If you want to read the full text of the acknowledgments from these earlier editions, please visit the web site.
First and foremost, we thank Jonathan Gennick, Darryl Hurley, Bryn Llewellyn, and Steve Adams, four outstanding Oracle technologists, for their substantial contributions to this book. Jonathan wrote or substantially updated five chapters, and Darryl contributed the fine chapter on database triggers and contributed many insights on Oracle's internationalization features. Bryn Llewellyn, Oracle's PL/SQL Product Manager, provided crucial information on Oracle9i's new features, answered endless questions about various PL/SQL features with bottomless patience, and reviewed a large number of chapters. Steve gave us excellent and detailed feedback on PL/SQL's runtime architecture.
With such a big book, we needed lots of reviewers, especially because we asked them to test each code snippet and program in the book to keep to an absolute minimum the number of errors that made it into the printed version. We are deeply grateful to the following men and women of the Oracle PL/SQL world, who took time away from the rest of their lives to help make Oracle PL/SQL Programming the best book it could be: Don Bales, Dick Bolz, Dan Clamage, Steve Cosner, Gerard Hartgers, Dwayne King, Larry Elkins, Chandrasekharan Iyer, Vadim Loevski, Giovanni Jaramillo, Rakesh Patel, James Padfield, Peter Linsley, Christopher Racicot, Alex Romankevich, Scott Sowers, JT Thomas, Edward Van Hatten, Simon St. Laurent, Tony Crawford, Geoff Chester, and Andre Vergison.
Once we felt good about the technical content, it was time for the remarkable crew at O'Reilly & Associates, led by our good friend, Deborah Russell, to transform our many chapters and code examples into a book worthy of the O'Reilly imprint. Many thanks to Emily Quill, production manager for the book; Rob Romano, who created the excellent figures; Julie Flanagan, able editorial assistant who helped throughout the project; and the rest of the crew.
And here are the many people we thanked (and continue to be grateful to) for their contributions to the first and second editions of this book:
Jennifer Blair, Eric Camplin, Joe Celko, Avery Cohen, Thomas Dunbar, R. James Forsythe, Mike Gangler, Gabriel Hoffman, Karen Peiser, Pete Schaffer, David Thompson, Cailein Barclay, Sunil Bhargava, Boris Burshteyn, Gray Clossman, Radhakrishna Hari, James Mallory, Nimesh Mehta, Jeff Muller, Dave Posner, Chris Racicot, Peter Vasterd, Zona Walcott, Sohaib Abassi, Per Brondum, Ivan Chong, Bill Dwight, Steve Ehrlich, Bushan Fotedar, Ken Jacobs, Nimish Mehta, Steve Muench, Sri Rajan, Mark Richter, Bill Hinman, Tony Ziemba, John Cordell, Beverly Gibson, Mike Sierra, Gigi Estabrook, Edie Freedman, Donna Woonteiler, Chris Reilley, Michelle Willey, Debby Cunha, Michael Deutsch, John Files, Juliette Muellner, Cory Willing, Seth Maislin, Kismet McDonough Chan, Clairemarie Fisher O'Leary, Eric Givler, Bert Scalzo, John Beresniewicz, Tom White, Steve Hilker, Thomas Kurian, Radhakrishna Hari, Shirish Puranik, Kannan Muthukkaruppan, Jane Ellin, Kimo Carter, Madeleine Newell, Rob Romano, Nancy Priest, Seth Maislin, Fred Polizo, Donald Herkimer, Ervan Darnell, Gary Cernosek, Patrick Pribyl, Bill Watkins, Debra Luik, and Leo Lok.
Finally, Steven thanks his wife, Veva Silva, and two sons, Chris Silva and Eli Feuerstein, for their support and tolerance of so much of his time and attention.
And Bill extends his most heartfelt gratitude to his immediate family, for putting up with writing into the wee hours ("Mommy, Daddy fell asleep at the computer again!") and remaining a constant source of love and good humor.
Part I: Programming in PL/SQL
This first part of the book introduces PL/SQL, explains how to create and run PL/SQL code, and presents language fundamentals. Chapter 1 asks the fundamental questions: Where did PL/SQL come from? What is it good for? What are the main features of the PL/SQL language? Chapter 2 is designed to get you and up and running PL/SQL programs as quickly as possible; it contains clear, straightforward instructions for executing PL/SQL code in SQL*Plus and a few other common environments. Chapter 3 answers basic questions about the language structure and keywords: What makes up a PL/SQL statement? What is the PL/SQL block structure all about? How do I write comments in PL/SQL?
Chapter 1
Chapter 2
Chapter 3
Chapter 1. Introduction to PL/SQL
This chapter introduces PL/SQL, its origins, and its various versions. It also offers a guide to additional resources for PL/SQL developers and some words of advice.
1.1 What Is PL/SQL?
PL/SQL stands for "Procedural Language extensions to the Structured Query Language."SQL is the now-ubiquitous language for both querying and updating—never mind the name—of relational databases. Oracle Corporation introduced PL/SQL to overcome some limitations in SQL and to provide a more complete programming solution for those who sought to build mission-critical applications to run against the Oracle database.
PL/SQL has several defining characteristics:
It is a highly structured, readable, and accessible language
Modeled after the Ada language, PL/SQL incorporates some of the latest and greatest in language design. If you are new to programming, PL/SQL is a great place to start. If you are experienced in other programming languages, you will very easily adapt to the new syntax. The accessibility of PL/SQL also means that you can write code that is easily maintained and enhanced over time, a critical aspect of high-quality software development.
PL/SQL is a standard and portable language for Oracle development
If you write a PL/SQL procedure or function to execute from within the Personal Oracle database sitting on your laptop, you can move that same procedure to a database on your corporate network and execute it there without any changes (assuming compatibility of Oracle versions, of course!). "Write once, run everywhere" was the mantra of PL/SQL long before Java appeared. For PL/SQL, though, "everywhere" means "everywhere there is an Oracle database."
PL/SQL is an embedded language
PL/SQL was not designed to be used as a "standalone" language, but instead to be invoked from within a "host" environment. So, for example, you can run PL/SQL programs from within the database (through, say, the SQL*Plus interface). Alternatively, you can define and execute PL/SQL programs from within an Oracle Developer form or report (this approach is called client-side PL/SQL). You cannot, however, create a PL/SQL "executable" that runs all by itself. Chapter 2 provides all the information you need to get up and running with this embedded language.
PL/SQL is a high-performance, highly integrated database language
These days, you have a number of choices when it comes to writing software to run against the Oracle database. You can use Java and JDBC (or SQLJ); you can use Visual Basic and ODBC; you can go with Delphi, C++, and so on. You will find, however, that it is easier to write highly efficient code to access the Oracle database in PL/SQL than it is in any other language. In particular, Oracle offers certain PL/SQL-specific enhancements such as the FORALL statement that can improve database performance by an order of magnitude or more.
1.2 The Origins of PL/SQL
Oracle has a history of leading the software industry in providing declarative, nonprocedural approaches to designing both databases and applications. The Oracle Server technology is among the most advanced, powerful, and stable relational databases in the world. Its application development tools, such as Oracle Forms, can offer high levels of productivity by relying heavily on a "paint-your-screen" approach in which extensive default capabilities allow developers to avoid heavy customized programming efforts.
1.2.1 The Early Years of PL/SQL
In Oracle's early years, this declarative approach, combined with its groundbreaking relational technology, was enough to satisfy developers. But as the industry matured, expectations rose and requirements became more stringent. Developers needed to get "under the skin" of the products. They needed to build complicated formulas, exceptions, and rules into their forms and database procedures.
In 1988, Oracle Corporation released Oracle Version 6.0, a major advance in its relational database technology. A key component of Oracle Version 6.0 was the so-called "procedural option" or PL/SQL. At roughly the same time, Oracle released its long-awaited upgrade to SQL*Forms Version 2.3 (the original name for the product now known as Oracle Forms or Forms Developer). SQL*Forms V3.0 incorporated the PL/SQL engine for the first time on the tools side, allowing developers to code their procedural logic in a natural, straightforward manner.
This first release of PL/SQL was very limited in its capabilities. On the server side, you could use PL/SQL only to build "batch-processing" scripts of procedural and SQL statements. In other words, you could not store procedures or functions for execution at some later time. You could not construct a modular application or store complex business rules in the server. On the client side, SQL*Forms V3.0 did allow you to create procedures and functions, although support for functions was not documented, and was therefore not used by many developers for years. In addition, this release of PL/SQL did not implement array support and could not interact with the operating system (for input or output). It was a far cry from a full-fledged programming language.
But for all its limitations, PL/SQL was warmly, even enthusiastically, received in the developer community. The hunger for the ability to code a simple IF statement inside SQL*Forms was strong. The need to perform multi-SQL statement batch processing was overwhelming.
What few developers realized at the time was that the original motivation and driving vision behind PL/SQL extended beyond the desire for programmatic control within products like SQL*Forms. Very early in the life cycle of Oracle's database and tools, Oracle Corporation had recognized two key weaknesses in their architecture: lack of portability and problems with execution authority.
