Thousands of application developers and database administrators around the world use software provided by Oracle Corporation to build complex systems that manage vast quantities of data. At the heart of much of Oracle's software is PL/SQL -- a programming language that provides procedural extensions to the SQL relational database language and an ever-growing range of Oracle development tools.
PL/SQL figures prominently as an enabling technology in almost every new product released by Oracle Corporation. Developers can use PL/SQL to perform many kinds of programming functions, including:
Implementing crucial business rules in the Oracle Server with PL/SQL-based stored procedures and database triggers
Enhancing powerful and easy-to-use GUI interfaces of products like Oracle Developer/2000 with detailed, programmatic control
Employing object-oriented design principles in Oracle-based applications
Linking a World Wide Web page to an Oracle database
Perhaps most importantly, PL/SQL plays a crucial role in the design of successful client-server applications because it provides the foundation for the code used to distribute processing and transactions across the network.
PL/SQL was modeled after Ada, a programming language designed for the United States Department of Defense. Ada is a high-level programming language which emphasizes data abstraction, information hiding, and other key elements of modern design strategies.
 The language was named "Ada" in honor of Ada Lovelace, a mathematician who is regarded by many to have been the world's first computer programmer.
PL/SQL is a powerful language which incorporates many of the most advanced elements of procedural languages, including:
A full range of datatypes
Explicit block structures
Conditional and sequential control statements
Loops of various kinds
Exception handlers for use in event-based error handling
Constructs that help in the development of modular code -- functions, procedures, and packages (collections of related programs and variables); packages are often referred to as "poor man's objects"
User-defined datatypes such as objects and nested tables (with the Oracle objects option)
PL/SQL is integrated tightly into Oracle's SQL language: you can execute SQL statements directly from your procedural program. Conversely, you can also call PL/SQL functions from within a SQL statement.
Oracle developers who want to be successful in the 1990s and beyond must learn to use PL/SQL to full advantage. This is a two-step process: first, learn how to use the language's full set of features; and second, after mastering those individual features, learn how to put these constructs together to build complex applications.
How can you best learn PL/SQL? The usual way is to struggle with the software tool and, through trial and error, discover the best implementation techniques. With PL/SQL, a slow and uncertain process is made worse by several factors:
PL/SQL is still a relatively new language; there are few resources outside of Oracle manuals that will help you learn more. Classes tend to focus on the flashy side of the new GUI tools, and they ignore the more complicated programming that is so necessary in production applications. Books on Oracle technology try to cover too much territory and as a result cannot support developers as they move past the most basic requirements.
PL/SQL is just now maturing to the point where it offers and is supported by a comprehensive set of features and programmer utilities. For years, developers have complained about a lack of a debugger, the inability to read from and write to operating system files, no support for arrays, and other issues. Oracle Corporation has finally released versions of PL/SQL which address these complaints and are robust enough to support large-scale application development. To complement this robustness, third-party vendors are also beginning to offer useful programming environments.
PL/SQL is an island of procedurality in a sea of declarative or "fill-in-the-form" products; you have to figure out how to blend these two different approaches when you develop your code. It is one thing to click on a button in a designer interface and create a widget. It is quite another thing to learn how to manipulate that widget and handle the event-based interactions between widgets with procedural code.
For all of these reasons and more, Oracle developers need a solid, comprehensive resource for all things PL/SQL. You need to know about the basic building blocks of PL/SQL, but you also need to learn by example, so you can skip as much of the trial-and-error as possible. As with any programming language, there is a right way and many wrong ways (or at least "not as right" ways) in PL/SQL to handle just about every requirement you will meet. It's my hope that this book will help you learn how to use the PL/SQL language in the most effective and efficient way possible.
What, specifically, will this book help you do?
Take full advantage of PL/SQL. The reference manuals may describe all the features of the PL/SQL language, but they don't tell you how to apply the technology. In fact, in some cases, you'll be lucky to even understand how to use a given feature after you've made your way through the railroad diagrams. Books and training courses tend to cover the same standard topics in the same limited way. In this book, we'll venture beyond to the edges of the language, to the nonstandard ways in which a particular feature can be tweaked to achieve a desired result.
Use PL/SQL to solve your problems. You don't spend your days and nights writing PL/SQL modules so that you can rise to a higher plane of existence. You use PL/SQL to solve problems for your company or your customers. In this book, I try hard to help you tackle real-world problems, the kinds of issues developers face on a daily basis (at least those problems that can be solved with mere software). To do this, I've packed the book with examples -- not just small code fragments, but complete application components you can apply immediately to your own situations. There is a good deal of code in the book itself, and much more on the disk that accompanies the book. In this book I guide you through the analytical process used to come up with a solution. In this way I hope you'll see, in the most concrete terms, how to apply PL/SQL features and undocumented applications of those features to a particular situation.
Write efficient, maintainable code. PL/SQL and the rest of the Oracle products offer the potential for incredible development productivity. If you aren't careful, however, this rapid development capability will simply let you dig yourself into a deeper, darker hole than you've ever found yourself in before. I would consider this book a failure if it only ended up helping programmers write more code in less time than ever before. I want to help you develop the skills and techniques that give you the time to build modules which readily adapt to change and are easily understood and maintained. I want to teach you to use comprehensive strategies and code architectures which allow you to apply PL/SQL in powerful, general ways to many of the problems you will face.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.