Using SQL Stored Procedures

The procedure defined in Figure 20-1 illustrates several of the basic structures common to all SPL dialects. Nearly all dialects use a CREATE PROCEDURE statement to initially define a stored procedure. A corresponding DROP PROCEDURE statement is used to discard procedures that are no longer needed. The CREATE PROCEDURE statement defines the following.

  • The name of the stored procedure
  • The number and data types of its parameters
  • The names and data types of any local variables used by the procedure
  • The sequence of statements executed when the procedure is called

The following sections describe these elements and the special SQL statements that are used to control the flow of execution within the body of a stored procedure.

1. Creating a Stored Procedure

In many common SPL dialects, the CREATE PROCEDURE statement is used to create a stored procedure and specify how it operates. The CREATE PROCEDURE statement assigns the newly defined procedure a name, which is used to call it. The name must typically follow the rules for SQL identifiers. (The procedure in Figure 20-1 is named ADD_CUST.) A stored procedure accepts zero or more parameters as its arguments. (This one has six parameters: C_NAME, C_NUM, CRED_LIMI, TGT_SLS, C_REP, and C_OFFC.) In all of the common SPL dialects, the values for the parameters appear in a comma-separated list, enclosed in parentheses, following the procedure name when the procedure is called. The header of the stored procedure definition specifies the names of the parameters and their data types. The same SQL data types supported by the DBMS for columns within the database can be used as parameter data types.

In Figure 20-1, all of the parameters are input parameters (signified by the IN keyword in the procedure header in the Oracle PL/SQL dialect). When the procedure is called, the parameters are assigned the values specified in the procedure call, and the statements in the procedure body begin to execute. The parameter names may appear within the procedure body (and particularly within standard SQL statements in the procedure body) anywhere that a constant may appear. When a parameter name appears, the DBMS uses its current value. In Figure 20-1, the parameters are used in the INSERT statement and the UPDATE statement, both as data values to be used in column calculations and search conditions.

In addition to input parameters, some SPL dialects also support output parameters. These allow a stored procedure to pass back values that it calculates during its execution. Output parameters aren’t useful for stored procedures invoked from interactive SQL, but they provide an important capability for passing back information from one stored procedure to another stored procedure that calls it. Some SPL dialects support parameters that operate as both input and output parameters. In this case, the parameter passes a value to the stored procedure, and any changes to the value during the procedure execution are reflected in the calling procedure.

Figure 20-2 shows the same ADD_CUST procedure definition, expressed in the Sybase Transact-SQL dialect. (The Transact-SQL dialect is also used by Microsoft SQL Server; its basics are largely unchanged since the original Sybase SQL Server version, which was the foundation for both the Microsoft and Sybase product lines.) Note the differences from the Oracle dialect:

  • The keyword PROCEDURE can be abbreviated to PROC.
  • No parenthesized list of parameters follow the procedure name. Instead, the parameter declarations immediately follow the name of the stored procedure.
  • The parameter names all begin with an at sign (@), both when they are declared at the beginning of the procedure and when they appear within SQL statements in the procedure body.
  • There is no formal end-of-procedure body marker. Instead, the procedure body is a single Transact-SQL statement. If more than one statement is needed, the Transact-SQL block structure is used to group the statements.

Figure 20-3 shows the ADD_CUST procedure again, this time expressed in the Informix stored procedure dialect. The declaration of the procedure head itself and the parameters more closely follow the Oracle dialect. Unlike the Transact-SQL example, the local variables and parameters use ordinary SQL identifiers as their names, without any special identifying symbols. The procedure definition is formally ended with an END PROCEDURE clause, which makes the syntax less error-prone.

In all dialects that use the CREATE PROCEDURE statement, the procedure can be dropped when no longer needed by a corresponding DROP PROCEDURE statement:

DROP PROCEDURE ADD_CUST

2. Calling a Stored Procedure

Once defined by the CREATE PROCEDURE statement, a stored procedure can be used. An application program may request execution of the stored procedure, using the appropriate SQL statement. Another stored procedure may call it to perform a specific function. The stored procedure may also be invoked through an interactive SQL interface.

The various SQL dialects differ in the specific syntax used to call a stored procedure. Here is a call to the ADD_CUST procedure in the PL/SQL dialect:

EXECUTE ADD_CUST(‘XYZ Corporation’, 2137, 30000.00, 50000.00, 103, ‘Chicago’)

The values to be used for the procedure’s parameters are specified, in order, in a list that is enclosed by parentheses. When called from within another procedure or a trigger, the EXECUTE statement may be omitted, and the call becomes simply:

ADD_CUST(‘XYZ Corporation’, 2137, 30000.00, 50000.00, 103, ‘Chicago’)

In the Transact-SQL dialect, the call to the stored procedure becomes:

EXECUTE ADD_CUST ‘XYZ Corporation’, 2137, 30000.00, 50000.00, 103, ‘Chicago’

The parentheses aren’t required, and the values to be used for parameters again form a comma-separated list. The keyword EXECUTE can be abbreviated to EXEC, and the parameter names can be explicitly specified in the call, allowing you to specify the parameter values in any order you wish. Here is an alternative, equivalent Transact-SQL call to the ADD_CUST stored procedure:

EXEC ADD_CUST @C_NAME = ‘XYZ Corporation’,
               @C_NUM = 2137,
            @CRED_LIM = 30000.00,
              @C_OFFC = ‘Chicago’,
               @C_REP = 103,
             @TGT_SLS = 50000.00

The Informix SPL form of the same EXECUTE command is:

EXECUTE PROCEDURE ADD_CUST(‘XYZ Corporation’, 2137, 30000.00, 50000.00, 103, ‘Chicago’)

Again, the parameters are enclosed in a comma-separated, parenthesized list. This form of the EXECUTE statement may be used in any context. For example, it may be used by an embedded SQL application program to invoke a stored procedure. Within a stored procedure itself, another stored procedure can be called using this equivalent statement:

CALL ADD_CUST(‘XYZ Corporation’, 2137, 30000.00, 50000.00,103, ‘Chicago’)

3. Stored Procedure Variables

In addition to the parameters passed into a stored procedure, it’s often convenient or necessary to define other variables to hold intermediate values during the procedure’s execution. All stored procedure dialects provide this capability. Usually, the variables are declared at the beginning of the procedure body, just after the procedure header and before the list of SQL statements. The data types of the variables can be any of the SQL data types supported as column data types by the DBMS.

Figure 20-4 shows a simple Transact-SQL stored procedure fragment that computes the total outstanding order amount for a specific customer number, and sets up one of two messages depending on whether the total order amount is over or under $30,000. Note that Transact-SQL local variable names, like parameter names, begin with an at sign (@). The DECLARE statement declares the local variables for this procedure. In this case, there are two variables: one with the MONEY data type and one VARCHAR.

In Transact-SQL, the SELECT statement assumes the additional function of assigning values to variables. A simple form of this use of SELECT is the assignment of the message text:

SELECT @MSG_TEXT = “high order total”

The assignment of the total order amount at the beginning of the procedure body is a more complex example, where the SELECT is used both to assign a value and as the introducer of the query that generates the value to be assigned.

Figure 20-5 shows the Informix SPL version of the same stored procedure. There are several differences from the Transact-SQL version:

  • Local variables are declared using the DEFINE statement. This example shows only a very limited subset of the options that are available.
  • Variable names are ordinary SQL identifiers; there is no special first character.
  • A specialized SELECT INTO statement is used within SPL to assign the results of a singleton SELECT statement into a local variable.
  • The LET statement provides simple assignment of variable values.

Figure 20-6 shows the Oracle PL/SQL version of the same stored procedure. Again, there are several differences to note from the Transact-SQL and Informix SPL examples:

  • Local variable declarations occur in a separate DECLARE section. This section is actually an integral part of the Oracle BEGIN.. .END block structure; it declares local variables for use within the block.
  • The SELECT INTO statement has the same form as the Informix procedure; it is used to select values from a single-row query directly into local variables.
  • The assignment statements use Pascal-style (:=) notation instead of a separate LET statement.

Local variables within a stored procedure can be used as a source of data within SQL expressions anywhere that a constant may appear. The current value of the variable is used in the execution of the statement. In addition, local variables may be destinations for data derived from SQL expressions or queries, as shown in the preceding examples.

4. Statement Blocks

In all but the very simplest stored procedures, it is often necessary to group a sequence of SQL statements together so that they will be treated as if they were a single statement. For example, in the IF…THEN…ELSE structure typically used to control the flow of execution within a stored procedure, most stored procedure dialects expect a single statement following the THEN keyword. If a procedure needs to perform a sequence of several SQL statements when the tested condition is true, it must group the statements together as a statement block, and this block will appear after THEN.

In Transact-SQL, a statement block has this simple structure:

/* Transact-SQL block of statements */ begin

/* Sequence of SQL statements appears here */

end

The sole function of the BEGIN.. .END pair is to create a statement block; they do not impact the scope of local variables or other database objects. The Transact-SQL procedure definition, conditional execution and looping constructs, and others, are all designed to operate with single SQL statements, so statement blocks are frequently used in each of these contexts to group statements together as a single unit.

In Informix SPL, a statement block includes not only a statement sequence, but may optionally declare local variables for use within the block and exception handlers to handle errors that may occur within the block. Here is the structure of an Informix SQL statement block:

/* Informix SPL block of statements */

/* Declaration of any local variables */

define . . .

/* Declare handling for exceptions */

on exception . . .

/* Define the sequence of SQL statements */

begin. . .

end

The variable declaration section is optional; we have already seen an example of it in the Informix stored procedure body in Figure 20-5. The exception-handling section is also optional; its role is described later in the “Handling Error Conditions” section. The BEGIN.END sequence performs the same function as it does for Transact-SQL. Informix also allows a single statement to appear in this position, if the block consists of just the other two components and a single SQL or SPL statement.

The Informix SQL language structures don’t require the use of statement blocks as often as the Transact-SQL structures. In the Informix dialect, the looping conditional execution statements each include an explicit termination (IF.END IF, WHILE.END WHILE, FOR.END FOR). Within the structure, a single SQL statement or a sequence of statements (each ending with a semicolon) may appear. As a result, an explicit block structure is not always needed simply to group together a sequence of SQL statements.

The Oracle PL/SQL block structure has the same capabilities as the Informix structure. It offers the capability to declare variables and exception conditions, using this format:

/* Oracle PL/SQL statement block */

/* Declaration of any local variables */

declare …

/* Specify the sequence of statements */

begin …

/* Declare handling for exceptions */

exception …

end;

All three sections of the block structure are optional. It’s common to see the structure used with only the BEGIN.. .END sequence to define a statement sequence, or with a DECLARE…BEGIN…END sequence to declare variables and a sequence of statements.

As with Informix, the Oracle structures that specify conditional execution and looping have a self-defining end-of-statement marker, so sequences of statements within these structures do not necessarily need an explicit BEGIN. END statement block structure.

5. Returning a Value

In addition to stored procedures, most SPL dialects support a stored function capability. The distinction is that a stored function returns a value while a stored procedure does not. Here’s a simple example of a stored function. Assume you want to define a stored procedure that, given a customer number, calculates the total current order amount for that customer. If you define the procedure as a function, the total amount can be returned as its value.

Figure 20-7 shows an Oracle stored function that calculates the total amount of current orders for a customer, given the customer number. Note the RETURNS clause in the procedure definition, which tells the DBMS the data type of the value being returned.

In most DBMS products, if you enter a function call via the interactive SQL capability, the function value is displayed in response. Within a stored procedure, you can call a stored function and use its return value in calculations or store it in a variable.

Many SPL dialects also allow you to use a stored function as a user-defined function within SQL value expressions. This is true of the Oracle PL/SQL dialect, so this use of the function defined in Figure 20-7 within a search condition is legal:

SELECT COMPANY, NAME

FROM CUSTOMERS, SALESREPS WHERE CUST_REP = EMPL_NUM

AND GET TOT ORDS(CUST NUM) > 10000.00

As the DBMS evaluates the search condition for each row of prospective query results, it uses the customer number of the current candidate row as an argument to the GET_TOT_ORDERS function and checks to see if it exceeds the $10,000 threshold. This same query could be expressed as a grouped query, with the ORDERS table also included in the FROM clause, and the results grouped by customer and salesperson. In many implementations, the DBMS carries out the grouped query more efficiently than the preceding one, which probably forces the DBMS to process the orders table once for each customer.

Figure 20-8 shows the Informix SPL definition for the same stored function shown in Figure 20-7. Except for stylistic variations, it differs very little from the Oracle version.

Transact-SQL does not have a stored function capability like the one illustrated in Figures 20-7 and 20-8. Transact-SQL stored procedures can explicitly return a status code, and they use a RETURN statement for this purpose. However, the returned value is always an integer status value. A zero return value indicates successful completion of the stored procedure; negative return values are used to indicate various types of errors. The system-defined stored procedures in Sybase Adaptive Server and Microsoft SQL Server all use this return status value convention. The return status of a called procedure can be stored into a local variable by using this assignment form of the EXECUTE statement:

declare sts_val int

execute sts_val = add_cust ‘XYZ Corporation’, 2137, 30000.00, 50000.00, 103, ‘Chicago’

6. Returning Values via Parameters

The stored function capability provides only the ability to return a single value from a stored routine. Several stored procedure dialects provide a method for returning more than one value, by passing the values back to the calling routine through output parameters. The output parameters are listed in the stored procedure’s parameter list, just like the input parameters seen in the previous examples. However, instead of being used to pass data values into the stored procedure when it is called, the output parameters are used to pass data back out of the stored procedure to the calling procedure.

Figure 20-9 shows a PL/SQL stored procedure to retrieve the name of a customer, his or her salesperson, and the sales office to which the customer is assigned, given a supplied customer number. The procedure has four parameters. The first one, CNUM, is an input parameter and supplies the requested customer number. The other three parameters are output parameters, used to pass the retrieved data values back to the calling procedure. In this simple example, the SELECT INTO form of the query places the returned variables directly into the output parameters. In a more complex stored procedure, the returned values might be calculated and placed into the output parameters with a PL/SQL assignment statement.

When a stored procedure with output parameters is called, the value passed for each output parameter must be an acceptable target that can receive a returned data value. The target may be a local variable, for example, or a parameter of a higher-level procedure that is calling a lower-level procedure to do some work for it. Here is a fragment of an Oracle PL/SQL procedure that makes an appropriate call to the GET_CUST_INFO procedure in Figure 20-9:

/* Get the customer info for customer 2111 */

declare the_name varchar(20),

    the_rep varchar(15),

   the_city varchar(15);

execute get_cust_info(2111, the_name, the_rep, the_city);

Of course, it would be unusual to call this procedure with a literal customer number, but it’s perfectly legal since that is an input parameter. The remaining three parameters have acceptable data assignment targets (in this case, they are PL/SQL variables) passed to them so that they can receive the returned values. Here is an illegal call to the same procedure:

/* Get the customer info for customer 2111 */

execute get_cust_info(2111, “XYZ Co”, the_rep, the_city)

because the second parameter is an output parameter and cannot receive a literal value. In addition to input and output parameters, Oracle allows you to specify procedure parameters that are both input and output (INOUT) parameters. They must obey the same previously cited restrictions for output parameters, but in addition, their values are used as input by the procedure.

Figure 20-10 shows a version of the GET_CUST_INFO procedure defined in the Transact-SQL dialect. The way in which the output parameters are identified in the procedure header differs slightly from the Oracle version, and the single-row SELECT statement has a different form. Otherwise, the structure of the procedure and its operation are identical to the Oracle example.

When this procedure is called from another Transact-SQL procedure, the fact that the second, third, and fourth parameters are output parameters must be indicated in the call to the procedure, as well as in its definition. Here is the Transact-SQL syntax for calling the procedure in Figure 20-10:

/* Get the customer info for customer 2111 */

declare the_name varchar(20);

declare the_rep varchar(15);

declare the_city varchar(15);

exec get_cust_info @c_num = 2111,

@c_name = the_name output,

@r_name = the_rep output,

@c_offc = the_city output

Figure 20-11 shows the Informix SPL version of the same stored procedure example. Informix takes a different approach to handling multiple return values. Instead of output parameters, Informix extends the definition of a stored function to allow multiple return values. Thus, the GET_CUST_INFO procedure becomes a function for the Informix dialect. The multiple return values are specified in the RETURNING clause of the procedure header, and they are actually returned by the RETURN statement.

The Informix CALL statement that invokes the stored function uses a special RETURNING clause to receive the returned values:

/* Get the customer info for customer 2111 */

define the_name varchar(20);

define the_rep varchar(15);

define the_city varchar(15);

call get_cust_info (2111)

returning the_name, the_rep, the_city;

As in the Transact-SQL dialect, Informix also allows a version of the CALL statement that passes the parameters by name:

 call get_cust_info (c_num = 2111)

returning the_name, the_rep, the_city;

7. Conditional Execution

One of the most basic features of stored procedures is an IF.. .THEN.. .ELSE construct for decision making within the procedure. Look back at the original ADD_CUST procedure defined in Figure 20-1 for adding a new customer. Suppose that the rules for adding new customers are modified so that there is a cap on the amount by which a salesperson’s quota should be increased for a new customer. If the customer’s anticipated first-year orders are $20,000 or less, that amount should be added to the quota, but if they are more than $20,000, the quota should be increased by only $20,000. Figure 20-12 shows a modified procedure that implements this new policy. The IF.THEN.ELSE logic operates exactly as it does in any conventional programming language.

All of the stored procedure dialects allow nested IF statements for more complex decision making. Several provide extended conditional logic to streamline multiway branching. For example, suppose you wanted to do three different things within the ADD_CUST stored procedure, depending on whether the customer’s anticipated first- year orders are under $20,000, between $20,000 and $50,000, or over $50,000. In Oracle’s PL/SQL, you could express the three-way decision this way:

/* Process sales target by range */

if tgt_sls < 20000.00

then

/* Handle low-target customers here */

elsif tgt_sls < 50000.00 then

/* Handle mid-target customers here */

else

/* Handle high-target customers here */

end if;

In the Informix dialect, the same multiway branch structure is allowed. The keyword ELSIF becomes ELIF, but all other aspects remain the same.

8. Repeated Execution

Another feature common to almost all stored procedure dialects is a construct for repeated execution of a group of statements (looping). Depending on the dialect, there may be support for Basic-style FOR loops (where an integer loop control value is counted up or counted down) or C-style WHILE loops with a test condition executed at the beginning or end of the loop.

In the sample database, it’s hard to come up with an uncontrived example of simple loop processing. Assume you want to process some group of statements repeatedly, while the value of a loop-control variable, named ITEM_NUM, ranges from 1 to 10. Here is an Oracle PL/SQL loop that handles this situation:

/* Process each of ten items */

for item_num in 1..10 loop

/* Process this particular item */

/* Test whether to end the loop early */

exit when (item_num = special_item);

end loop;

The statements in the body of the loop are normally executed ten times, each time with a larger integer value of the ITEM_NUM variable. The EXIT statement provides the capability to exit an Oracle PL/SQL loop early. It can be unconditional, or it can be used with a built-in test condition, as in this example.

Here is the same loop structure expressed in Informix SPL, showing some of its additional capabilities and the dialectic differences from PL/SQL:

/* Process each of ten items */

for item_num = 1 to 10 step 1

/* Process this particular item */

/* Test whether to end the loop early */

if (item_num = special_item)

then exit for;

end for;

The other common form of looping is when a sequence of statements is executed repeatedly while a certain condition exists or until a specified condition exists. Here is an Oracle PL/SQL loop construct that repeats indefinitely. Such a loop must, of course, provide a test within the body of the loop that detects a loop-terminating condition (in this case, a match of two variable values) and explicitly exits the loop:

/* Repeatedly process some data */ loop

/* Do some kind of processing each time */

/* Test whether to end the loop early */

exit when (test_value = exit_value);

end loop;

A more common looping construct is one that builds the test into the loop structure itself. The loop is repeatedly executed as long as the test is true. For example, suppose you want to reduce targets for the offices in the sample database until the total of the targets is less than $2,400,000. Each office’s target is to be reduced by the same amount, which should be a multiple of $10,000. Here is a (not very efficient) Transact-SQL stored procedure loop that gradually lowers office targets until the total is below the threshold:

/* Lower targets until total below $2,400,000 */

while (select sum(target) from offices) < 2400000.00

begin

update offices

set target = target – 10000.00

end

The BEGIN.. .END block in this WHILE loop isn’t strictly necessary, but most Transact- SQL WHILE loops include one. Transact-SQL repeats the single SQL statement following the test condition as the body of the WHILE loop. If the body of the loop consists of more than one statement, you must use a BEGIN. END block to group the statements. Here is the Oracle PL/SQL version of the same loop:

/* Lower targets until total below $2,400,000 */

select sum(target) into total_tgt from offices;

while (total_tgt < 2400000.00)

loop

update offices

set target = target – 10000.00;

select sum(target) into total_tgt from offices;

end loop;

The subquery-style version of the SELECT statement from Transact-SQL has been replaced by the PL/SQL SELECT INTO form of the statement, with a local variable used to hold the total of the office targets. Each time the loop is executed, the OFFICES table is updated, and then the total of the targets is recalculated.

Here is the same loop once more, expressed using Informix SPL’s WHILE statement:

/* Lower targets until total below $2,400,000 */

select sum(target) into total_tgt from offices;

while (total_tgt < 2400000.00)

update offices

set target = target – 10000.00;

select sum(target) into total_tgt from offices;

end while;

Other variants of these loop-processing constructs are provided by the various dialects, but the capabilities and syntax are similar to these examples.

9. Other Flow-of-Control Constructs

Some stored procedure dialects provide statements to control looping and alter the flow of control. In Informix, for example, the EXIT statement interrupts the normal flow within a loop and causes execution to resume with the next statement following the loop itself. The CONTINUE statement interrupts the normal flow within the loop but causes execution to resume with the next loop iteration. Both of these statements have three forms, depending on the type of loop being interrupted:

exit for;

continue for;

exit while;

continue while;

exit foreach;

continue foreach;

In Transact-SQL, a single statement, BREAK, provides the equivalent of the Informix EXIT statement variants, and there is a single form of the CONTINUE statement as well. In Oracle, the EXIT statement performs the same function as for Informix, and there is no CONTINUE statement.

Additional control over the flow of execution within a stored procedure is provided by statement labels and the GOTO statement. In most dialects, the statement label is an identifier, followed by a colon. The GOTO statement names the label to which control should be transferred. There is typically a restriction that you cannot transfer control out of a loop or a conditional testing statement, and always a prohibition against transferring control into the middle of such a statement. As in structured programming languages, the use of GOTO statements is discouraged because it makes stored procedure code harder to understand and debug.

10. Cursor-Based Repetition

One common need for repetition of statements within a stored procedure is when the procedure executes a query and needs to process the query results, row by row. All of the major dialects provide a structure for this type of processing. Conceptually, the structures parallel the DECLARE CURSOR, OPEN CURSOR, FETCH, and CLOSE CURSOR statements in embedded SQL or the corresponding SQL API calls. However, instead of fetching the query results into the application program, in this case, they are being fetched into the stored procedure, which is executing within the DBMS itself. Instead of retrieving the query results into application program variables (host variables), the stored procedure retrieves them into local stored procedure variables.

To illustrate this capability, assume that you want to populate two tables with data from the ORDERS table. One table, named BIGORDERS, should contain customer name and order size for any orders over $10,000. The other, SMALLORDERS, should contain the salesperson’s name and order size for any orders under $1000. The best and most efficient way to do this would actually be with two separate SQL INSERT statements with subqueries, but for purposes of illustration, consider this method instead:

  1. Execute a query to retrieve the order amount, customer name, and salesperson name for each order.
  2. For each row of query results, check the order amount to see whether it falls into the proper range for including in the BIGORDERS or SMALLORDERS tables.
  3. Depending on the amount, INSERT the appropriate row into the BIGORDERS or SMALLORDERS table.
  4. Repeat Steps 2 and 3 until all rows of query results are exhausted.
  5. Commit the updates to the database.

Figure 20-13 shows an Oracle stored procedure that carries out this method. The cursor that defines the query is defined in the declare section of the procedure and assigned the name O_CURSOR. The variable CURS_ROW, defined in the same section, is defined as an Oracle row type. It is a structured Oracle row variable with individual components (like a C-language structure). By declaring it as having the same row type as the cursor, the individual components of CURS_ROW have the same data types and names as the cursor’s query results columns.

The query described by the cursor is actually carried out by the cursor-based FOR loop. It basically tells the DBMS to carry out the query described by the cursor (equivalent to the OPEN statement in embedded SQL) before starting the loop processing. The DBMS then executes the FOR loop repeatedly, by fetching a row of query results at the top of the loop, placing the column values into the CURS_ROW variable, and then executing the statements in the loop body. When there are no more rows of query results to be fetched, the cursor is closed, and processing continues after the loop.

Figure 20-14 shows an equivalent stored procedure with the specialized FOR loop structure of Informix SPL. In this case, the query results are retrieved into ordinary local variables; there is no special row data type used. The FOREACH statement incorporates several different functions. It defines the query to be carried out, through the SELECT expression that it contains. It marks the beginning of the loop that is to be executed for each row of query results. (The end of the loop is marked by the END FOREACH statement.) When the FOREACH statement is executed, it carries out the query and then fetches rows of query results repeatedly, putting their column values into the local variables as specified in the statement. After each row is fetched, the body of the loop is executed. When there are no more rows of query results, the cursor is automatically closed, and execution continues with the next statement following the FOREACH. Note that in this example, the cursor isn’t even assigned a specific name because all cursor processing is tightly specified within the single FOREACH statement.

The Transact-SQL dialect doesn’t have a specialized FOR loop structure for cursor- based query results processing. Instead, the DECLARE CURSOR, OPEN, FETCH, and CLOSE statements of embedded SQL have direct counterparts within the Transact-SQL language. Figure 20-15 shows a Transact-SQL version of the sort_orders procedure.

Transact-SQL equivalent of the SQLSTATE code. It receives a value of zero when a fetch is successful, and a nonzero value when there are no more rows to fetch.

11. Handling Error Conditions

When an application program uses Embedded SQL or a SQL API for database processing, the application program is responsible for handling errors that arise. Error status codes are returned to the application program, and more error information is typically available through additional API calls or access to an extended diagnostics area. When database processing takes place within a stored procedure, the procedure itself must handle errors.

Transact-SQL provides error handling through a set of global system variables.

The specific error-handling variables are only a few of well over 100 system variables that provide information on the state of the server, transaction state, open connections, and other database configuration and status information. The two most useful global variables for error handling are:

  • @@@ERROR. Contains error status of the most recently executed statement batch
  • @@@SQLSTATUS. Contains status of the last fetch operation

The normal completion values for both variables are zero; other values indicate various errors and warnings. The global variables can be used in the same way as local variables within a Transact-SQL procedure. Specifically, their values can be checked for branching and loop control.

Oracle’s PL/SQL provides a different style of error handling. The Oracle DBMS provides a set of system-defined exceptions, which are errors or warning conditions that can arise during SQL statement processing. Within an Oracle stored procedure (actually, any Oracle statement block), the EXCEPTION section tells the DBMS how it should handle any exception conditions that occur during the execution of the procedure. There are over a dozen different predefined Oracle-detected exception conditions. In addition, you can define your own exception conditions.

Most of the previous examples in this chapter don’t provide any real error-handling capability. Figure 20-16 shows a revised version of the Oracle stored function in Figure 20-7. This improved version detects the specific situation where the supplied customer number does not have any associated orders (that is, where the query to calculate total orders returns a NO_DATA_FOUND exception). It responds to this situation by signaling back to the application program an application-level error and associated message. Any other exception conditions that arise are caught by the WHEN OTHERS exception handler.

The Informix SPL takes a similar approach to exception handling. Figure 20-17 shows the Informix version of the stored function, with Informix-style exception handling.

The ON EXCEPTION statement is a declarative statement and specifies the sequence of SQL statements to be executed when a specific exception arises. A comma-separated list of exception numbers may be specified.

Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.

Leave a Reply

Your email address will not be published. Required fields are marked *