Track 1: In-Depth PL/SQL
Presenter: Steven Feuerstein
PL/SQL is a very mature and robust language, excelling particularly at integration with the underlying Oracle Database. Oracle has expanded and deepened the functionality of PL/SQL over the years, as well as greatly improved its performance. Since it is not possible to explore all aspects of PL/SQL in depth in a day's set of presentations, Steven Feuerstein has identified those aspects of PL/SQL which he thinks are most critical to helping you make the most of PL/SQL and be as successful as possible in deploying PL/SQL-based applications.
Session 1. PL/SQL Collections
The PL/SQL collection is a very handy data structure. Using collections, you can create and manage sets, lists, stacks, arrays and more. You can avoid mutating table trigger errors, improve query performance through session-based caching, emulate bi-directional cursors, and simplify your code by hiding complexity inside collection structures. Topics in this session include:
- An overview of collections: the different types of collections, how to define collection types, how to use the collection methods to manipulate the contents of collections.
- Multi-level collections, including emulation of multi-dimensional arrays, nesting collections within objects and more.
- String-based indexing, which allows us to emulate a relational table's unique indexes for a collection, and also offers many new and interesting ways to store program data.
- Set operations on nested tables: new to Oracle Database 10g, you can now perform high-level set operations on nested tables (union, intersection, minus, etc.) as well as use the = and <> operators to compare nested tables.
Session 2. SQL Optimizations in PL/SQL
If you can do it in pure SQL, please do so, as it will likely be the most efficient implementation possible. But in many situations, the SQL language simply isn't flexible enough, which is why Oracle created PL/SQL. And over the years, Oracle has added a number of features into PL/SQL to optimize the way that SQL statements are processed from within this language. Topics in this session include:
- Bulk processing in PL/SQL: a close examination of the FORALL and BULK COLLECT statements, which will dramatically improve the performance of SQL statements that process multiple rows of data. First introduced in Oracle8i, these statements have been enhanced in later versions with
- Table functions: you can now call a function in the FROM clause of a query as if it were a table. Table functions offer a wide range of possibilities for enhancing your code, from making it easier to pass complex result sets back to non-PL/SQL host environments to parallelizing the execution of PL/SQL functions inside queries.
- Dynamic SQL in PL/SQL: Oracle offers two mechanisms for dynamic SQL: native dynamic SQL (NDS) and DBMS_SQL. NDS is generally the preferred option, but there are times when DBMS_SQL also comes in very handy. We will review how to use dynamic SQL inside PL/SQL and highlight the scenarios that still call for use of DBMS_SQL.
Session 3. Key Best Practices for PL/SQL Development
All of the greatest new features in a language don't help much if we write our code so that it is impossible to understand, debug or maintain. This session will offer guidelines for the most important best practices as you write your PL/SQL applications, from recommendations on how to write (and avoid writing) SQL statements to exception handling, block structure and more. Topics in this session include:
- SQL in PL/SQL: One of the most wonderful features of PL/SQL is that it is so easy to write SQL statements inside a PL/SQL block. And one of the most dangerous features of PL/SQL is that it is so easy to write SQL statements inside a PL/SQL block. That very ease means that most developers take SQL statements entirely for granted, and write them over and over again wherever they are needed. That leads to redundancy and serious obstacles to optimization of your code. We will talk about how to organize the SQL statements inside your application to avoid these problems.
- Exception handling: a well-designed application will include robust, consistent error handling, so that information is reported back to users in a clear manner and all the necessary data is stored in error logs for the support and debug teams. You can do all of that in PL/SQL, but it is important to understand both the strengths and weaknesses of PL/SQL's exception handling architecture. Then you should build a reusable package to do as much of the work as possible for you.
- Block structure and construction: we all know the horror stories about "spaghetti code" (particularly the ones we wrote ourselves): enormous blocks of code that go on for page after page, with little internal structure or standards. The best PL/SQL applications are built out of tightly-focused packages that themselves contain highly-modularized programs. We will explore techniques available in PL/SQL to build such applications in ways that make them very readable and maintainable.