![]() |
![]() |
|
|
![]() |
![]() |
ALLBASE/SQL FORTRAN Application Programming Guide: HP 9000 Computer Systems > Chapter 8 Processing with Cursors![]() Sequential Table Processing Commands |
|
The SQL commands used for sequential table processing are:
Refer to the ALLBASE/SQL Reference Manual for the complete syntax and semantics of these commands. Ensure that all the commands listed above for any single cursor are contained within the same transaction. The DECLARE CURSOR command syntax names a cursor and associates with it a particular SELECT command:
Note that the DECLARE CURSOR command has two optional clauses:
The SELECT command syntax for cursor declarations that do not include the FOR UPDATE clause can consist of any of the SELECT command clauses except the INTO clause:
A SELECT command associated with a cursor does not name output host variables, but may name input host variables in the select list, the WHERE clause, and the HAVING clause. In the following example, the rows qualifying for the query result will be those with a CountCycle matching that specified by the user in input host variable CountCycle:
When performing sequential table processing, the ORDER BY clause may be useful. In the example above, the rows in the query result will be in order by ascending bin number to help the program user, who will be moving from bin to bin, taking a physical inventory. The DECLARE CURSOR command is actually a preprocessor directive. When the FORTRAN preprocessor parses this command, it stores a section in the target DBEnvironment. At runtime, the section is not executed when the DECLARE CURSOR command is encountered, but when the OPEN command is executed. Because the DECLARE CURSOR command is not executed at runtime, you do not need to perform status checking in your program following this command. The OPEN command syntax examines any input host variables and determines the active set:
The following command defines the active set associated with the cursor defined earlier:
You use the FETCH command to retrieve a row at a time from the active set. You can use the KEEP CURSOR WITH NOLOCKS option for a cursor that involves sorting, whether through the use of a DISTINCT, GROUP BY, or ORDER BY clause, or as the result of a union or a join operation. However, for kept cursors involving sorting, ALLBASE/SQL does not ensure data integrity. See the ALLBASE/SQL Reference Manual for more information on ensuring data integrity. The FETCH command syntax defines a current row and delivers the row into output host variables:
Remember to include indicator variables when one or more columns in the query result may contain a null value:
The first time you execute the FETCH command, the first row in the query result is the current row. With each subsequent execution of the FETCH command, each succeeding row in the query result becomes current. After the last row in the query result has been fetched, ALLBASE/SQL sets SQLCA.SQLCode to 100. ALLBASE/SQL also sets SQLCA.SQLCode to 100 if no rows qualify for the active set. You should test for an SQLCA.SQLCode value of 100 after each execution of the FETCH command to determine whether to re-execute this command.
When a row is current, you can update it by using the UPDATE WHERE CURRENT command or delete it by using the DELETE WHERE CURRENT command. This command can be used to update the current row when the SELECT command associated with the cursor does not contain one of the following:
The UPDATE WHERE CURRENT command syntax identifies the active set to be updated by naming the cursor and the column(s) to be updated:
Any columns you name in this command must also have been named in a FOR UPDATE clause in the related DECLARE CURSOR command:
In this example, the order of the rows in the query result is not important. Therefore the SELECT command associated with cursor AdjustQtyOnHand does not need to contain an ORDER BY clause and the UPDATE WHERE CURRENT command can be used. In cases where order is important and the ORDER BY clause must be used, you can use the form of the UPDATE command described in the chapter, "Overview of Data Manipulation," to update values in the current row. In this case, if more than one row qualifies for the search condition in the UPDATE command, more rows than just the current row will be changed:
After either the UPDATE WHERE CURRENT or the UPDATE command is executed, the current row remains the same until the FETCH command is re-executed. This command can be used to delete the current row when the SELECT command associated with the cursor does not contain one of the following:
The DELETE WHERE CURRENT command has a very simple syntax:
The DELETE WHERE CURRENT command can be used in conjunction with a cursor declared with or without the FOR UPDATE clause:
Even though the SELECT command associated with cursor Inventory names only some of the columns in table PurchDB.Inventory, the entire current row is deleted. After the DELETE WHERE CURRENT command is executed, there is no current row. You must re-execute the FETCH command to obtain another current row. As in the case of the UPDATE WHERE CURRENT command, if the SELECT command associated with the cursor contains an ORDER BY clause or other components listed earlier, you can use the DELETE command to delete a row:
If you use the DELETE command to delete a row while using a cursor to examine an active set, remember that more than one row will be deleted if multiple rows satisfy the conditions specified in the WHERE clause of the DELETE command. In addition, the row that is current when the DELETE command is executed remains the current row until the FETCH command is re-executed. When you no longer want to operate on the active set, you use CLOSE command syntax:
The CLOSE command frees up ALLBASE/SQL internal buffers used to handle cursor operations. This command does not release any locks obtained since the cursor was opened; to release locks, you must terminate the transaction:
When a transaction terminates, any cursors opened during that transaction are automatically closed unless you are using the KEEP CURSOR option of the OPEN command. To avoid possible confusion, it is good programming practice to always use the CLOSE command followed by COMMIT WORK to explicitly close any open cursors before ending a transaction. |
![]() |
||
![]() |
![]() |
![]() |
|||||||||
|