Authors: Benjamin Rosenzweig and Elena Rakhimov
Publisher: Prentice Hall
Audience: Would-be developers who know relational databases but don’t know PL/SQL
Reviewer: Kay Ewbank
This fifth edition of a well-known title has been updated for Oracle 12c.
As with previous editions, the book is very hands-on; you’re introduced to a concept, shown an example, then asked to work through an example yourself. The book is based on the Introduction to PL/SQL class taught at Columbia University.
The authors, Benjamin Rosenzweig and Elena Rakhimov, start with an introduction to what’s new and improved in Oracle 12c in terms of PL/SQL, before moving on to the core material of the book with a chapter explaining PL/SQL concepts. PL/SQL (Procedural Language Extension to SQL) combines SQL with programming structures and subroutines, which increases the complexity level of learning it, especially as it has its own terminology for some elements, such as program blocks for logical tasks that are then used to create subroutines.
Chapter 2 introduces the ideas of the language fundamentals such as variables, reserved words, identifiers and anchored data types. The authors assume you don’t know about programming concepts, which means the ideas are all explained.
Chapter 3 adds SQL into the mix, introducing DML statements such as Select Into. Transaction control is also introduced at this early stage. The next group of chapters deal with programming concepts – If statements, Case statements, loops, error handling and exceptions. If you already know another programming language, you’ll be able to skim read these chapters.
By Chapter 11 the authors are back onto database specific programming ideas, with an introduction to cursors. As with the other chapters, the material is clearly introduced with a sequence of labs covering the different cursor types – implicit and explicit, cursor For loops and nested cursors. Advanced cursors get another chapter looking at the use of parameters, For Update and Where Current cursors.
Triggers are tackled next, followed by mutating tables and compound triggers. A mutating table is Oracle’s terminology for a table against which you issue a DML statement, but in the case of triggers, it refers to the table on which a trigger is defined.
I find it difficult to imagine anyone who would benefit from the earlier chapters then coping with this group of chapters, but the authors have done a good job of their explanations, and if you already know how to program and skim read the first sections, you should manage without getting to the whimpering stage.
The next two chapters look at data concepts – collections (tables and v-arrays); and records (table and cursor based). You might be wondering why the authors have left it this late in the book to introduce what many would see as the basic building blocks of databases, but these are the PL/SQL versions of tables, arrays and records, which are separate from the database equivalents.
Native Dynamic SQL and Bulk SQL are next on the agenda. Native Dynamic SQL covers SQL statements that are built on the fly based on a set of parameters specified at runtime, so this chapter covers Execute Immediate, Open-For and Fetch statements. The Bulk SQL chapter covers ForAll and Bulk Collect statements. Both are used when you want to improve the performance of your PL/SQL by sending a request from PL/SQL to the SQL engine that applies to multiple records.
The topics of procedures and functions each get a chapter covering concepts such as passing parameters and optimizing function execution in SQL. There’s a good chapter on packages (collections of PL/SQL objects) explaining what the benefits are and how to take advantage of them. A chapter on stored code then follows on looking at more advanced aspects of packages. The final chapters cover object types in Oracle, Oracle-supplied packages, and optimizing PL/SQL.
I found it difficult to reach a single conclusion about this book. It’s clearly written, follows a good step-by-step approach with plenty of examples and exercises (with sample solutions). As such, it’s a good way to begin with PL/SQL. My main reservation is that anyone needing to learn about loops and If statements will really struggle with the later chapters. The authors assume (as they must, of course), that you’re learning the whole of programming, but the thought of learning to program using PL/SQL as my first programming language is not one I’d relish; both the database development and the learning to program are hard enough tasks on their own.
Overall, if you need to learn PL/SQL, you’ll find some sections of this book to be a really useful resource no matter what your experience level. I just doubt whether there’s any single person who could benefit from the whole of it at once.