Complex Data Structures in PL/SQL
Sunday, 10 November 2002
Posted by austin in: Technology, comments closed
- The Shape of the Problem
- The Limitations of the Language
- The Shape of the Solution
- The Solution
- Further Options
- Lessons Learned
- Further Reading
- Source Listings
- Listing 1: Data Split Package (data_split_pkg.sql)
- Listing 2: Data Split Package Body (data_split_pkg_body.sql)
- Listing 3: Schema (data_split_tables.sql)
- Listing 4: Data (test_data_split.sql)
In an ideal software development project, the development environment (that is, the languages, tools, and libraries) will be chosen that best suits the nature of the problem domain and the skills of the professionals involved. In the real world, the development environment is dictated by any number of constraints. The most common is that the project is a modification to or enhancement of an existing project. Just because one cannot – for whatever reason – use the language most suited to the solution does not mean that one can’t take lessons from those languages.
At one of my previous jobs, I worked on a project where the code was written in PL/SQL called by a C driver. A lot of developers, in my experience, don’t think much of PL/SQL because they believe that it performs slowly, or because its functionality is limited. (Most of the time, however, this prejudice is present because the developers involved do not know the language itself.) For this project, PL/SQL performed the tasks (mostly data manipulation) as fast or faster than an equivalent C implementation could have done because there were fewer context switches and no network communication involved. (If you aren’t familiar with PL/SQL, see the accompanying article.)
As useful as it is, PL/SQL is not without its limitations, mostly surrounding complex data structures. These limitations presented what seemed to be an insurmountable roadblock to the project, but the time-frame of this subproject did not allow for the language to be changed to one where the data structures required could be represented more ‘naturally.’
The subsystem in question required that the time-series data be viewed in ways which are not supported by Oracle SQL queries on the original data; it was necessary to restructure the data. This turned out to be more of a problem than I had originally anticipated, and the eventual solution to the problem came from an interesting combination of techniques. In the end, I adapted data structures that are more naturally represented in languages like C, C++, and Java in PL/SQL.
An Extremely Brief Introduction to PL/SQL
Posted by austin in: Technology, comments closed
PL/SQL is Oracle’s “procedural language extensions to SQL.” It is embedded into Oracle server products and client development tools like Oracle Forms or Developer/2000. It is modeled on Ada, although anyone with any familiarity with Pascal will find it easy to understand. It offers most of the features one would expect in a modern procedural language: modularity, loops, conditionals, exceptions, native and user-definable data types (including collection types), and native handling of SQL queries and cursors. The PL/SQL engine is responsible for memory management with no option for user defined memory management; there are no pointers or references of any sort.PL/SQL is block-oriented (all code is organized between BEGIN and END statements) and offers both anonymous blocks and named blocks, which are procedures (may not return a value) and functions (must return a value). Parameters to procedures or functions are passed by value, not by reference (PL/SQL 8 offers a NOCOPY keyword allowing pass by reference parameters). Although PL/SQL isn’t an object-oriented language, it encourages encapsulation through packages. A package is written in two pieces: the package definition that defines the prototypes of the public modules and public data object definitions, and the package body that defines the implementation of the package, including private modules, variable types, and variables.
PL/SQL supports exception handling in named and anonymous blocks, and allows the programmer to define exceptions and associate these with error numbers. Several of the common Oracle errors are given names, and exceptions can be defined within packages (if they are defined in the public portion of the package, then they can be referenced just as a package procedure would be referenced).
PL/SQL supports all of the Oracle built-in types, plus a few for PL/SQL only (BOOLEAN, PLS_INTEGER, BINARY_INTEGER, others). Complex data types can be created as records or, in PL/SQL 8, database object types. Records can be created from references to tables or as an explicit declaration. Collections can be created from native data types, database object types, or records, but may not contain (directly or indirectly) other collections. Two of the collection types (varying arrays and nested tables) are based on Oracle8 object facilities and provide the best flexibility when defined as objects in the schema, as they can provide additional capabilities with CAST … AS statements. The remaining collection type, index-by tables, offers a sparse collection, where the entries do not need to be contiguous and allow for smart indexes. (Entries that have not been set in index-by tables do not exist and have no value, not even NULL, meaning that “random” index selection should test that the value EXISTS before attempting to get a value.)
The performance of PL/SQL is very good on certain sets of tasks, sometimes rivalling compiled C and C++. PL/SQL performs very well on tasks that involve data manipulation, and does not perform as well as tasks that involve significant calculations. Even with these limitations, PL/SQL’s flexibility, ease of use, and portability can make it an ideal choice for wide ranges of business logic and other functionality within Oracle products.
