How You Can Open and Position a Cursor
You can use the open statement to perform the following operations:
• Open a cursor in the database, specifying whether:
– The cursor can update or delete rows that are fetched
– Updates should be deferred or direct
• Associate a select statement with the cursor and position the cursor immediately before the first row in the select statement's result table
• Set the cursor's State attribute to CS_OPEN
To access the rows in the result table, use the fetch statement. This statement performs the following tasks:
• Advances the cursor one row in the result table
• Places the values in the indicated columns into the specified variables
• Sets the cursor's State and RowCount attributes
Each execution of a fetch statement fetches one row of the result table. The syntax lets you retrieve the values from every column in the result table or from some subset of the columns.
If you fetch all of the retrieved values in the order specified in the select statement, you do not need to name the columns in the fetch statement.
For example, assume you issued the following open statement to retrieve the values in eight columns from the customer table:
open cust_cursor for select acctno, cphone,
cname, caddr, ccity, cdistrict, cstatus,
cacctbal
from customer for direct update of acctno,
cphone, cname, caddr, ccity, cdistrict,
cstatus, cacctbal;
To fetch the values from all eight columns in the same order specified in the select statement, use the following statement:
fetch cust_cursor into :acctno, :cphone,
:cname, :caddr, :ccity, :cdistrict,
:cstatus, :cacctbal;
Because the variables in the fetch statement match the number, data type, and order of specification of the select statement's expressions, you need not specify column names. The fetch statement puts the retrieved values (one row at time) into the specified variables.
Similarly, if you fetch a subset that begins with the first column returned by the select, continues in the order listed in the select, and stops before all are listed, it is not necessary to specify column names. For example, the following statement fetches the first, second, and third columns (acctno, cphone, cname) returned by the select:
fetch cust_cursor into :acctno, :cphone, :cname;
However, whenever you fetch column values out of order (that is, do not start with the first selected column and continue in the order of the select), you must identify the column. For example, to fetch only the customer's name and status using the example open statement, specify the column names:
fetch cust_cursor into :cname=cname,
:cstatus=cstatus;
Whether you fetch all or some of the selected column values, the variables that receive the column values and their associated columns must have compatible data types.
For more information about the open and fetch cursor statements, see the Language Reference Guide.