This table is placed in Virtual Storage. It does not assign values to host variables, though. Returns data from the results table one row at a time and assigns the values to specified host variables.
You can let DB2 automatically close the cursor when the application program terminates but this practice is not recommended. Only one row is updated, the current row. Only one row is deleted, the current row. Remember Me. Create a new account.
Remember Me Forgot Password? Link Text. Each of these distinct SELECT statement types must have access to different sets of authorization privileges in order to execute.
If a statement-name is specified, the available privileges for the prepared SELECT statement are a combination of the privileges held by the AuthID of the application process. Specifies that the cursor is scrollable. For a scrollable cursor, whether the cursor has sensitivity to inserts, updates, or deletes depends on the cursor sensitivity option in effect for the cursor.
The sensitivity options include the following ones:. Specifies the desired sensitivity of the cursor to inserts, updates, or deletes that made to the rows underlying the result table. The sensitivity of the cursor determines whether XDB can materialize the rows of the result into a temporary table. Specifies that the cursor does not have sensitivity to inserts, updates, or deletes that are made to the rows underlying the result table.
As a result, the size of the result table, the order of the rows, and the values for each row do not change after the cursor is opened. In addition, the cursor is read-only. Specifies that the cursor has sensitivity to changes that are made to the database after the result table is materialized. The cursor is always sensitive to updates and deletes that are made using the cursor that is, positioned updates and deletes using the same cursor.
When the current value of a row no longer satisfies the select-statement or statement-name, that row is no longer visible through the cursor. When a row of the result table is deleted from the underlying base table, the row is no longer visible through the cursor.
XDB cannot make changes visible to the cursor when the cursor implicitly becomes read-only. Specifies that the result table of the cursor is dynamic, meaning that the size of the result table may change after the cursor is opened as rows are inserted into or deleted from the underlying table, and the order of the rows may change.
Rows that are inserted, deleted, or updated by statements that are executed by the same application process as the cursor are visible to the cursor immediately. As the programmer, you declare a cursor and define an SQL statement for that cursor.
After that, you can use the cursor in much the same manner as a sequential file. The cursor is opened, rows are fetched from the cursor one row at a time, and then the cursor is closed. Defines the cursor, gives it a name unique to the program in which it is embedded, and assigns an SQL statement to the cursor name. Readies the cursor for row retrieval. OPEN is an executable statement. It does not assign values to host variables , though. Returns data from the results table one row at a time and assigns the values to specified host variables.
You will learn about access paths in Chapter 21, "The Optimizer. When you're processing with cursors, an SQL statement can return zero, one, or many rows. The following list describes the cursor processing that occurs for the different number of retrieved rows:.
Use of the cursor is optional. A result set of one row occurs either because the SQL predicates provided specific qualifications to make the answer set distinct or because a unique index exists for a column or columns specified in the predicates of the WHERE clause.
Cursor processing is mandatory. When multiple rows are returned by an SQL statement, a cursor must be coded. No rows exist for the specified conditions, or the specified conditions are improperly coded. When cursors are used to process multiple rows, a FETCH statement is typically coded in a loop that reads and processes each row in succession. For an example of cursor processing, consult Listing In Listing Values are moved to the host variables, and the cursor is opened.
A loop fetches and processes information until no more rows are available; then the cursor is closed. Often an application program must read data and then, based on its values, either update or delete the data.
How can you then first read the data before modifying it? Refer to Listing The cursor is opened and a row is fetched. These features enable you to perform row-by-row operations on DB2 tables, effectively mimicking sequential file processing. One of the more significant new application development features of DB2 V7 is the scrollable cursor.
Scrollable cursors provide the ability to scroll forward and backward through data in an application program. Contrast this with a "normal" cursor, in which data can only be accessed one row at a time in a forward direction using the FETCH statement.
A scrollable cursor makes navigating through SQL result sets much easier. More details on cursor sensitivity are provided later in this section. Scrollable cursors allow developers to move through the results of a query in multiple ways. The FETCH statement is still used, but it is combined with keywords to move the fetching in a specific direction.
The following keywords are supported when fetching data from a scrollable cursor:. It can be either a positive or a negative number, and it can be represented as a numeric constant or as a host variable. For example, consider the following cursor logic:. Issuing these SQL statements will declare a scrollable cursor named csr1 , open that cursor, and then FETCH the last row from the cursor's results set.
0コメント