8. Using Cursor#
Overview#
When it is expected that a query will return multiple records, a cursor can be declared and used to manipulate the records.
APRE supports the use of various embedded SQL statements for declaring and managing cursors.
Briefly, the Cursor-related SQL statements that are available in APRE are the DECLARE CURSOR statement, the OPEN statement, the FETCH statement, the CLOSE statement, and the CLOSE RELEASE statement, each of which provides a different cursor-related functionality.
Order of Execution of Cursor-Related SQL Statements#
The order in which SQL statements for managing cursors are executed is as follows:
- DECLARE CURSOR
- OPEN
- FETCH
The FETCH statement is repeatedly executed to retrieve all records that satisfy the given conditions until the result of execution is SQL_NO_DATA. - CLOSE or CLOSE RELEASE
Notes#
If an OPEN, FETCH, CLOSE, or CLOSE RELEASE statement that references a cursor that has not been declared is executed, an error indicating that the cursor does not exist will be raised.
It is possible to declare more than one cursor with the same name within one application. When doing so, only the most recently declared cursor will be valid. This means that OPEN, FETCH, CLOSE, and CLOSE RELEASE statements will apply to the most recently declared cursor.
Cursor-Related SQL Statements#
Each cursor-related SQL statement is defined and described below in detail.
DECLARE CURSOR#
This statement is used to declare a cursor.
Syntax#
EXEC SQL DECLARE <cursor name>
[SENSITIVE | INSENSITIVE] [SCROLL]
CURSOR [WITH HOLD] FOR <cursor specification>;
Arguments#
- <cursor name>
This is the name of the cursor. It can be a maximum of 50 bytes long. It must start with an alphabetic character (a ~ z, A ~ Z), the underscore character ("_"), or a dollar sign ("$"). - SENSITIVE | INSENSITIVE
A sensitive cursor reflects data changes in the result set of the cursor. If there is a need to repeatedly retrieve a row set, the latest data of the database is retrieved. An insensitive cursor maintains the data of the time point the cursor was opened in the result set of the cursor. - SCROLL
This argument enables the user to randomly move the cursor in the result set. SCROLL and SENSITIVE must be used together for effectiveness. - WITH HOLD
This argument keeps the cursor open after the transaction is complete. This argument is only valid if the session is in non-autocommit mode. For more detailed information on cursor types such as SENSITIVE, SCROLL and WITH HOLD, please refer to CLI User's Manual. - <cursor specification>
This is a SQL SELECT statement of Altibase. For complete information on authoring SELECT statements, please refer to the Altibase SQL Reference.
Description#
The DECLARE CURSOR statement must be executed before any other cursor-related SQL statements. If another statement that references a cursor that has not been declared is executed, an error indicating that the cursor does not exist will be raised.
When the DECLARE CURSOR statement is executed, all SQL statement preparation tasks, such as syntax checking, semantics checking, optimization, and execution plan creation, are conducted on the server. (This is similar to the functionality of the SQLPrepare ODBC statement.) The SQL statement need only be prepared in advance one time in order to be executed multiple times using OPEN CURSOR (which is like the SQLExecute ODBC statement).
Limitations#
All of the limitations that apply to the SELECT Altibase SQL statement also apply to the DECLARE CURSOR statement.
Example#
The following example shows how to declare a cursor for retrieving all of the records in the departments table.
< Sample Program : cursor1.sc >
EXEC SQL DECLARE DEPT_CUR CURSOR FOR
SELECT *
FROM DEPARTMENTS;
OPEN#
This statement is used to open a cursor.
Syntax#
EXEC SQL OPEN <cursor name>;
Argument#
<cursor name>: This is the name of the cursor to open.
Description#
The OPEN statement executes the SQL statement that was specified using the DECLARE CURSOR statement.
The SELECT statement that is executed using the OPEN statement was previously prepared for execution on the database server when the DECLARE CURSOR statement was executed. When the OPEN statement is executed, the server searches the corresponding table(s) for records that satisfy the conditions in the WHERE clause.
A cursor in the OPEN state can be opened without the CLOSE execution, which is identical to OPEN after executing CLOSE.
Example#
The following example shows the statement that is used to open a cursor called DEPT_CUR.
< Sample Program : cursor1.sc >
EXEC SQL OPEN DEPT_CUR;
FETCH#
This statement is used to read column values from an open cursor and store them in corresponding host variables.
Syntax#
EXEC SQL FETCH [<fetch_orientation>]
<cursor name> INTO <host_var_list>;
Arguments#
- <cursor name>: This is the name of the cursor.
- <host_var_list>: This is a list of output host variables and output indicator variables.
- <fetch_orientation>
This argument specifies the position of the cursor within the result set and retrieves its value. The following values are available for specification:- FIRST: The value of the first row of the result set is retrieved.
- LAST: The value of the last row of the result set is retrieved.
- PRIOR: The value of the row prior to the current cursor position is retrieved.
- NEXT: The value of the row next to the current cursor position is retrieved.
- CURRENT: The value of the current cursor position is retrieved.
- RELATIVE n: The value of the nth row from the current cursor position is retrieved.
- ABSOLUTE n: The value of the nth row of the result set is retrieved.
Description#
The FETCH statement first instructs the cursor to move to the next record, and then stores that record's column values in corresponding host variables.
Result of Execution#
The two possible results of execution of a FETCH statement are SQL_SUCCESS and SQL_NO_DATA, each of which is described below.
- SQL_SUCCESS
This result indicates that the value retrieved by the FETCH operation was successfully stored in the corresponding host variable, and that there are still data waiting to be returned on the database server.
Applications are typically written such that they will continue to fetch additional records when the result of a FETCH operation is SQL_SUCCESS. - SQL_NO_DATA
This result indicates that nothing was retrieved by the FETCH operation and that no data are stored in the corresponding host variable. The contents of the host variable are therefore meaningless (i.e. a garbage value). This result means one of two things: either that all records that satisfy the given conditions have been returned from the database server, or that there were originally no records that satisfied the given conditions.
Example#
This example shows the use of the previously declared and opened DEPT_CUR cursor to fetch records. Each of the returned column values is stored in a corresponding element of the s_department structure. The s_dept_ind structure-type indicator variable can be used to check whether any of the returned column values is NULL. The while loop continues to perform the FETCH operation and retrieve records that satisfy the conditions until SQL_NO_DATA is returned.
< Sample Program : hostvar.h >
EXEC SQL BEGIN DECLARE SECTION;
typedef struct department
{
short dno;
char dname[30+1];
char dep_location[9+1];
int mgr_no;
} department;
typedef struct dept_ind
{
int dno;
int dname;
int dep_location;
int mgr_no;
} dept_ind;
EXEC SQL END DECLARE SECTION;
< Sample Program : cursor1.sc >
/* specify path of header file */
EXEC SQL OPTION (INCLUDE=./include);
/* include header file for precompile */
EXEC SQL INCLUDE hostvar.h;
EXEC SQL BEGIN DECLARE SECTION;
/* declare host variables */
department s_department;
/* structure indicator variables */
dept_ind s_dept_ind;
EXEC SQL END DECLARE SECTION;
while(1)
{
EXEC SQL FETCH DEPT_CUR
INTO :s_department :s_dept_ind;
if (sqlca.sqlcode == SQL_SUCCESS)
{
printf("%d %s %s %d\n",
s_department.dno, s_department.dname,
s_department.dep_location,
s_department.mgr_no);
}
else if (sqlca.sqlcode == SQL_NO_DATA)
{
break;
}
else
{
printf("Error : [%d] %s\n", SQLCODE,
sqlca.sqlerrm.sqlerrmc);
break;
}
}
CLOSE#
This statement is used to close a cursor.
Syntax#
EXEC SQL CLOSE <cursor name>;
Argument#
- <cursor name>: This is the name of the cursor to close.
Description#
If the CLOSE statement is executed when there are still data left to return on the database server (i.e. when not all of the records have been fetched), then the unfetched results will be discarded. In other words, once the CLOSE statement has been executed, the FETCH statement cannot be executed using that cursor. If it is desired to use the cursor to perform another FETCH operation, it will be necessary to open the cursor before executing the FETCH statement again.
When the CLOSE statement is executed, the resources allocated to the cursor are not released. Additionally, the results of the SQL statement preparation tasks that were conducted when the DECLARE CURSOR statement was executed are saved. To use this prepared SQL statement after the CLOSE statement has been executed, omit the DECLARE CURSOR statement and execute the OPEN statement using the same cursor name.
Example#
The following example shows the use of the CLOSE statement to close the DEPT_CUR cursor.
< Sample Program : cursor1.sc >
EXEC SQL CLOSE DEPT_CUR;
CLOSE RELEASE#
This statement is used to close a cursor and release all of the resources that were allocated to the cursor.
Syntax#
EXEC SQL CLOSE RELEASE <cursor name>;
Argument#
- <cursor name>: This is the name of the cursor.
Description#
The CLOSE RELEASE statement releases the resources allocated to the cursor and deletes the results of the SQL statement preparation tasks that were conducted when the DECLARE CURSOR statement was executed. If there are still data left to return on the database server when the CLOSE RELEASE statement is executed, then the unfetched results will be discarded. If it is desired to use the same cursor name after the CLOSE RELEASE statement has been executed, it will be necessary to execute the DECLARE CURSOR statement, followed by the OPEN statement. In other words, once the CLOSE RELEASE statement has been executed, the OPEN statement cannot be executed using that cursor name.
Example#
In the following example, the CLOSE RELEASE statement is executed on the EMP_CUR cursor. At this time, the SQL statement preparations that were made when the DECLARE EMP_CUR CURSOR statement was executed will be discarded, and the resources allocated to the EMP_CUR cursor will be released.
< Sample Program : cursor2.sc >
EXEC SQL CLOSE RELEASE EMP_CUR;
Reusing a Cursor Name#
This section explains how to use the same cursor name repeatedly. It sets forth the order in which to perform tasks and highlights some important considerations to keep in mind when reusing a cursor name.
Relationships between Cursor-Related Statements#
The following describes the order in which cursor-related statements must be executed when reusing a cursor name. In detail, it sets forth the cursor-related statements that can precede each cursor-related statement.
- DECLARE CURSOR
When reusing a cursor name, the DECLARE CURSOR statement must be executed after the CLOSE statement or the CLOSE RELEASE statement. - OPEN
When reusing a cursor name, the OPEN statement must be executed after the CLOSE statement. If all records have been fetched, it can also be executed after the FETCH statement. - FETCH
When reusing a cursor name, the FETCH statement must be executed after the OPEN statement. It can also be executed after another FETCH statement if the result of execution of the previous FETCH statement was SQL_SUCCESS. - CLOSE
When reusing a cursor name, the CLOSE statement can be executed after the DECLARE CURSOR statement, the OPEN statement, or the FETCH statement. When it is executed after a FETCH statement, it doesn't matter whether the result of execution of the FETCH statement was SQL_SUCCESS or SQL_NO_DATA. - CLOSE RELEASE
When reusing a cursor name, the CLOSE RELEASE statement can be executed after any other statement. When it is executed after a FETCH statement, it doesn't matter whether the result of execution of the previous FETCH statement was SQL_SUCCESS or SQL_NO_DATA.
Cursor-Related SQL Statements and Host Variables#
The following describes how cursor-related SQL statements must be used depending on whether the input host variables used to declare the cursor are global or local in scope.
-
If the input host variables in the DECLARE CURSOR statement are global in scope, then the OPEN statement can be executed after the CLOSE statement when reusing a cursor name.
-
If a host variable used in a DECLARE CURSOR statement is local in scope, that is, if the host variable was declared inside a function, then in order to use the same cursor name, the DECLARE CURSOR statement must be executed after the CLOSE statement. The reason for this limitation is that the values of the host variable pointers used in the DECLARE CURSOR statement are stored internally when the DECLARE CURSOR statement is executed, and these stored values are used when the OPEN statement is executed. Therefore, if these host variables are local, then if the function is exited and then called again, the pointer values may be changed, and thus their values may no longer be valid when the OPEN statement is executed. This means that it is necessary to execute the DECLARE CURSOR statement to save the pointer values every time the function is called. Additionally, the local host variables must be declared in the function containing the DECLARE CURSOR and OPEN statements.
The CLOSE and CLOSE RELEASE Statements#
The difference between the CLOSE and CLOSE RELEASE statements is described below:
- To reuse the name of a cursor that was released using the CLOSE RELEASE statement, it is necessary to execute the DECLARE CURSOR statement again. The reason for this is that when the CLOSE RELEASE statement is executed, all of the information and resources related to the cursor are deleted, so it is necessary to execute the DECLARE CURSOR statement to allocate necessary resources and prepare for the execution of the SQL statement. Therefore, when it is desired to reuse a cursor, in most cases it is more appropriate to execute the CLOSE statement, rather than the CLOSE RELEASE statement.
- After all results have been fetched (i.e. when the result returned by the FETCH statement is SQL_NO_DATA), it is possible to execute either the CLOSE statement or the CLOSE RELEASE statement. When planning to reuse the cursor, the CLOSE statement is more appropriate, whereas the CLOSE RELEASE statement is more appropriate when it is not expected that the cursor will be reused. It is possible to execute the CLOSE RELEASE statement after the CLOSE statement has been executed, but this is somewhat wasteful because the cursor closing operation is performed twice.
- In summary, the CLOSE statement is more appropriate when expecting to reuse the cursor, and the CLOSE RELEASE statement is more appropriate when not expecting to reuse it. In practice, however, there are few cases in which a cursor is not used more than once, so the CLOSE RELEASE statement is almost never used. Reusing a cursor name by repeatedly executing the CLOSE RELEASE, DECLARE CURSOR and OPEN statements has a negative impact on performance.
Sample Programs#
cursor1.sc#
This example can be found at $ALTIBASE_HOME/sample/APRE/cursor1.sc
Result of Execution#
$ is -f schema/schema.sql
$ make cursor1
$ ./cursor1
<CURSOR 1>
------------------------------------------------------
[Declare Cursor]
------------------------------------------------------
Success declare cursor
------------------------------------------------------
[Open Cursor]
------------------------------------------------------
Success open cursor
------------------------------------------------------
[Fetch Cursor]
------------------------------------------------------
DNO DNAME DEP_LOCATION MGR_NO
------------------------------------------------------
1001 RESEARCH DEVELOPMENT DEPT 1 New York 16
1002 RESEARCH DEVELOPMENT DEPT 2 Sydney 13
1003 SOLUTION DEVELOPMENT DEPT Japan 14
2001 QUALITY ASSURANCE DEPT Seoul 17
3001 CUSTOMER SUPPORT DEPT London 4
3002 PRESALES DEPT Peking 5
4001 MARKETING DEPT Seoul 8
4002 BUSINESS DEPT LA 7
------------------------------------------------------
[Close Cursor]
------------------------------------------------------
Success close cursor
cursor2.sc#
This example can be found at $ALTIBASE_HOME/sample/APRE/cursor2.sc
Result of Execution#
$ is -f schema/schema.sql
$ make cursor2
$ ./cursor2
<CURSOR 2>
------------------------------------------------------
[Declare Cursor]
------------------------------------------------------
Success declare cursor
------------------------------------------------------
[Open Cursor]
------------------------------------------------------
Success open cursor
------------------------------------------------------
[Fetch Cursor]
------------------------------------------------------
ENO DNO SALARY
------------------------------------------------------
2 -1 1500000.00
3 1001 2000000.00
4 3001 1800000.00
5 3002 2500000.00
6 1002 1700000.00
7 4002 500000.00
9 4001 1200000.00
10 1003 4000000.00
11 1003 2750000.00
12 4002 1890000.00
13 1002 980000.00
14 1003 2003000.00
15 1003 1000000.00
16 1001 2300000.00
17 2001 1400000.00
18 4001 1900000.00
19 4002 1800000.00
------------------------------------------------------
[Close Release Cursor]
------------------------------------------------------
Success close release cursor