Condition handling in SQL persistent stored modules

• Authors: Jeff Richey
• Venue: ACM SIGMOD Record
• Year: 1995

The national and international standards committees responsible for Database Language SQL have proposed a candidate extension for SQL Persistent Stored Modules (SQL/PSM). The purpose of this extension is to provide a computationally complete language for the declaration and invocation of SQL stored modules and routines. Typically, such routines are stored in a database Server and executed from an application Client in a Client/Server environment.The proposed SQL/PSM consists of syntax and semantics for variable and cursor declarations, function and procedure (routines) invocations, condition handling, and control statements for looping and branching. An SQL routine is block structured, with each block consisting of local variable and condition handler declarations, a list of SQL statements, and local condition handler execution.Condition handling is a major new feature of SQL/PSM (henceforth referred to as PSM), although the style and comprehensiveness of the specification is still an issue in further progression of the standard. The specification currently under ballot includes conditions for exceptions, warnings, and other completions such as success of no data, and handlers for Continue, Exit, Redo, and Undo.Condition handling allows the user to separate condition handling code from the main flow of a routine, thereby eliminating the need to write numerous short and redundant code fragments to handle each unique condition. In some database products, one cannot even resolve the condition in the Server and must instead resort to the Client application program for resolution. Such approaches are often tedious, error-prone, and inflexible. Condition handling in the SQL module avoids these expensive alternatives, instead allowing the procedure to resolve its own conditions and then resume processing.Condition handling allows one to centralize the handling of conditions and gives users control over two major areas: run-time recovery from failures, and effects of conditions on transactions.Run-time recovery from failures has the following characteristics:• allows a user to handle any run-time condition, either by exiting gracefully or by attempting recovery• provides a recovery mechanism that includes the ability to resolve a condition and then resume action at the statement that caused the condition to be raised (if it was resolved)• provides the ability to define what "code" will handle each conditionAfter a condition has been resolved, what is the state of the transaction? Condition handling must ensure that the SQL-data, schemas, and SQL-variables are all maintained in an appropriate stable state and can be committed or rolled back. Additionally, the transaction must comply with the ACID test rules.Thus, the benefits of condition handling include:• allows reduction of error recovery code• creates a model for trapping and resolving conditions• provides the ability to resolve the condition and if possible to continue on• avoids the cost of requiring the SQL-client to resolve the condition• provides for greater data and path consistency in handling conditions• separates one condition from another