Millions 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 Oracle’s version of SQL (Structured Query Language) and serves as the programming language within the Oracle Developer toolset (most notably Forms Developer and Reports Developer).
PL/SQL figures prominently as an enabling technology in almost every new product released by Oracle Corporation. Software professionals 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
Generating and managing XML documents entirely within the database
Linking web pages to an Oracle database
Implementing and automating database administration tasks—from establishing row-level security to managing rollback segments within PL/SQL programs
PL/SQL was modeled after Ada, a programming language designed for the U.S. Department of Defense. Ada is a high-level language that emphasizes data abstraction, information hiding, and other key elements of modern design strategies. As a result of this very smart design decision by Oracle, PL/SQL is a powerful language that incorporates many of the most advanced elements of procedural languages, including:
A full range of datatypes from number to string, and including complex data structures such as records (which are similar to rows in a relational table), collections (which are Oracle’s version of arrays), and XMLType (for managing XML documents in Oracle and through PL/SQL)
An explicit and highly readable block structure that makes it easy to enhance and maintain PL/SQL applications
Conditional, iterative, and sequential control statements, including a CASE statement and three different kinds of loops
Exception handlers for use in event-based error handling
Named, reusable code elements such as functions, procedures, triggers, object types (akin to object-oriented classes), and packages (collections of related programs and variables)
PL/SQL is integrated tightly into Oracle’s SQL language: you can execute SQL statements directly from your procedural program without having to rely on any kind of intermediate API (Application Programming Interface) such as JDBC (Java Database Connectivity) or ODBC (Open Database Connectivity). Conversely, you can also call your own PL/SQL functions from within a SQL statement.
Oracle developers who want to be successful in the 21st century must learn to use PL/SQL to full advantage. This is a two-step process. First, you must become familiar with and learn how to use the language’s ever-expanding set of features; and second, after gaining competence in the individual features, you must learn how to put these constructs together to build complex applications.
For these reasons and more, Oracle developers need a solid, comprehensive resource for the base PL/SQL language. You need to know the basic building blocks of PL/SQL, but you also need to learn by example so that you can avoid some of the trial and error. As with any programming language, PL/SQL has a right way and many wrong ways (or at least “not as right” ways) to handle just about any task. It is my hope that this book will help you learn how to use the PL/SQL language in the most effective and efficient way possible.