ALLBASE/SQL Reference Manual (36216-90216)

Chapter 4 145
Constraints, Procedures, and Rules
Using Procedures
In addition, you can temporarily use the SET DML ATOMICITY statement to set the DML
error checking level to row level. However, you must handle partially processed statements
yourself, as statements that get errors will not undo their partial execution.
Constraint error checking is part of general error checking but you can override the
checking level by setting constraint checking to deferred. However, when you set
constraint checking back to IMMEDIATE, the level of constraint checking returns to the
current level specified by the most recent SET DML ATOMICITY statement.
Refer to Chapter 12 , “SQL Statements S - Z,” for detailed information on the SET DML
ATOMICITY and SET CONSTRAINTS statements.
Using Procedures
An ALLBASE/SQL procedure consists of control flow and status statements together with
SQL statements that are stored as sections in the system catalog for later execution at the
user's request or through the firing of a rule. You can create a procedure through ISQL or
through an application program; and you can execute the procedure through ISQL,
through an application program, or through rules that are created separately. For more
information about rules, refer to the section “Using Rules,” later in this chapter.
Procedures offer the following features:
• They reduce communication between applications and the DBEnvironment, thereby
improving performance.
• They provide additional security by controlling exactly which operations users can
perform on database objects.
• Along with rules, they enable you to store business rules in the database itself rather
than coding them in application programs.
• They let you protect application programs from changes in the database schema.
Often, procedures are built to accommodate a set of rules defined on particular tables.
Although you can use procedures without rules, rules always operate in conjunction with
procedures. When you create a rule, the referenced procedure must already exist. So you
must create procedures first, then rules.
The following sections describe the use of procedures:
• Understanding Procedures
• Creating Procedures
• Executing Procedures
• Procedures and Transaction Management
• Using SQL Statements in Procedures
• Queries inside Procedures
• Using a Procedure Cursor in ISQL