Principle of DB2 cursor

The principle of DB2 cursor is still unfamiliar to many new users who have just come into contact with the DB2 database. The following describes the principle of DB2 cursor in detail. I hope you can have a better understanding of the principle of DB2 cursor.

DB2 cursor Principle

In general, SQL query results are results set of multiple records, while advanced languages can only process one record at a time. The cursor mechanism is used to read and process multiple records at a time. In this way, the operation on the set is converted to the processing of a single record. The procedure for using a cursor is as follows:

1. indicates the cursor. The select statement is not executed when the cursor is clicked.
Declare <cursor Name> cursor for <select statement>;

2. Open the cursor. When you open a cursor, you actually execute the corresponding select statement to read the query results to the buffer zone. At this time, the cursor is active and the Pointer Points to the first record of the query result set.
Open <cursor Name>;

3. Push the cursor pointer and read the current record. Use the fetch statement to forward the cursor pointer to a record and read the current record in the buffer to the variable. The fetch statement is usually used in a cyclic structure. The fetch statement is executed cyclically to retrieve rows in the result set one by one for processing. In many databases, the cursor pointer can be easily understood at any step in any direction, not just forward the cursor pointer to a row.
Fetch <cursor Name> into <variable 1>, <variable 2>...

4. Close the cursor. Close the cursor with the close statement to release the buffer and other resources occupied by the result set. When the cursor is closed, it is no longer associated with the original query result set. However, the cursor can be opened again, which is associated with the new query results.
Close <cursor Name>;

Cycle Control of DB2 cursors

In DB2, cursor control is not very easy and convenient. You can also use sqlcode or sqlstate to control it, or you can control it yourself. In DB2, SQLCODE and SQLSTATE cannot be used directly and must be declared before use, that is to say, the SQLCODE and SQLSTATE of the system are instantiated locally for one copy ). Generally, the User-Defined cursor switch and sqlcode return information are jointly controlled.

Two ways to store db2 logs

Common DB2 cycle usage

Implementation of creating a database in DB2

DB2 Directory view description

DB2 index syntax