功能说明 游标在使用完成后需要关闭,否则游标所占用的服务器的资源不会被释放;如果 没有明确的关闭,游标则在声明它的复合语句结束处被关闭。 语法 CLOSE cursor_name 表5- 187 参数说明 参数名称 描 述 cursor_name 要关闭的游标名称。 示例 示例1:以下代码是包含在游标代码块中的。 DECLARE s_region CHAR(16); DECLARE region INT; DECLARE cur REF CURSOR; OPEN cur FOR SELECT DISTINCT c_region,1 FROM ssbm.customer ORDER BY c_region LIMIT 1000; FETCH cur INTO s_region, region; // CLOSE cur; //
SET AUTOFREE 语句允许您在 GBase 8s ESQL/C 程序中采取下列行动: 为所有游标启用 AUTOFREE 特性: EXEC SQL set autofree; EXEC SQL set autofree enabled; 这些语句是等同的,因为 SET AUTOFREE 语句的缺省行为是启用所有游标。
为所有游标禁用 AUTOFREE 特性: EXEC SQL set autofree disabled;
为指定的游标标识符或游标变量启用 AUTOFREE 特性: EXEC SQL set autofree for cursor_id; EXEC SQL set autofree for :cursor_var;
SET AUTOFREE 语句覆盖 IFX_AUTOFREE 环境变量的任何值。
下列代码段使用 SET AUTOFREE 语句的 FOR 子句来仅为 curs1 游标启用 AUTOFREE 特性。在数据库服务器为 curs1 执行 CLOSE 语句之后,它自动地释放该游 标和准备好的语句。不自动地释放 curs2 游标及其准备好的语句。 EXEC SQL BEGIN DECLARE SECTION; int a_value; EXEC SQL END DECLARE SECTION;
/* Declare the curs1 cursor for the slct1 prepared * statement */ EXEC SQL prepare slct1 from 'select a_col from tab1'; EXEC SQL declare curs1 cursor for slct1;
/* Enable AUTOFREE for cursor curs1 */
GBase 8s ESQL/C 编程指南 南大通用数据技术股份有限公司 - 431 -
EXEC SQL set autofree for curs1;
/* Open the curs1 cursor and fetch the contents */ EXEC SQL open curs1; while (SQLCODE == 0) { EXEC SQL fetch curs1 into :a_value; printf("Value is: %d\n", a_value); }
/* Once the CLOSE completes, the curs1 cursor is freed and * cannot be used again. */ EXEC SQL close curs1;
/* Declare the curs2 cursor for the slct2 prepared * statement */ EXEC SQL prepare slct2 from 'select a_col from tab1'; EXEC SQL declare curs2 cursor for slct2;
/* Open the curs2 cursor and fetch the contents */ EXEC SQL open curs2; while (SQLCODE == 0) { EXEC SQL fetch curs2 into :a_value; printf("Value is: %d\n", a_value); }
/* Once this CLOSE completes, the curs2 cursor is still * available for use. It has not been automatically freed. */ EXEC SQL close curs2;
/* You must explicitly free the curs2 cursor and slct2 * prepared statement. */
下列代码段展示脱离的准备好的语句可如何发生: /****************************************************************** * Declare curs1 and curs2. The slct1 prepared statement is * * associated curs1 because curs1 is declared first. */ EXEC SQL prepare slct1 'select a_col from tab1'; EXEC SQL declare curs1 cursor for slct1; EXEC SQL declare curs2 cursor for slct1;
/****************************************************************** * Enable the AUTOFREE feature for curs2 */ EXEC SQL set autofree for curs2;
GBase 8s ESQL/C 编程指南 南大通用数据技术股份有限公司 - 433 -
/***************************************************************** * Open the curs1 cursor and fetch the contents */ EXEC SQL open curs1; { EXEC SQL fetch curs1 into :a_value; printf("Value is: %d\n", a_value); }
EXEC SQL close curs1;
/* Because AUTOFREE is enabled only for the curs2 cursor, this * * CLOSE statement frees neither the curs1 cursor nor the slct1 * * prepared statement. The curs1 cursor is still defined so the * * slct1 prepared statement does not become detached. *
/***************************************************************** * Open the curs2 cursor and fetch the contents */ EXEC SQL open curs2; while (SQLCODE == 0) { EXEC SQL fetch curs2 into :a_value; printf("Value is: %d\n", a_value); }
EXEC SQL close curs2;
/* This CLOSE statement frees the curs2 cursor but does not free * * slct1 prepared statement because the prepared statement is not* * associated with curs2. *
/***************************************************************** * Reopen the curs1 cursor. This open is possible because the * * AUTOFREE feature has not been enabled on curs1. Therefore, the* * database server did not automatically free curs1 when it closed it.*/ EXEC SQL open curs1; while (SQLCODE == 0) { EXEC SQL fetch curs1 into :a_value; printf("Value is: %d\n", a_value); }
EXEC SQL close curs1; EXEC SQL free curs1;
/* Explicitly freeing the curs1 cursor, with which the slct1 * * statement is associated, causes slct1 to become detached. It * * is no longer associated with a cursor. *
/*************************************************************** * This DECLARE statement causes the slct1 prepared statement * * to become reassociated with a cursor. Therefore, the slct1 * * statement is no longer detached. */ EXEC SQL declare curs3 cursor for slct1; EXEC SQL open curs3;
/* Enable the AUTOFREE feature for curs */ EXEC SQL set autofree for curs3;
GBase 8s ESQL/C 编程指南 南大通用数据技术股份有限公司 - 435 -
/* Open the curs3 cursor and fetch the content */ EXEC SQL open curs3; while (SQLCODE == 0) { EXEC SQL fetch curs3 into :a_value; printf("Value is: %d\n", a_value); }
EXEC SQL close curs3;
/* Because AUTOFREE is enabled for the curs3 cursor, this CLOSE* * statement frees the curs3 cursor and the slct1 PREPARE stmt.*
/*************************************************************** * This DECLARE statement would generate a run time error * * because the slct1 prepared statement has been freed. */ EXEC SQL declare x4 cursor for slct1;
/************************************* * Prepare an INSERT statement *************************************/ EXEC SQL prepare ins_stmt from 'insert into table_x values(?)';
/************************************* * Declare the insert cursor for the * prepared INSERT. *************************************/ EXEC SQL declare ins_curs cursor for ins_stmt; /*********************************************************** * OPEN the insert cursor. Because the Deferred-PREPARE feature * is enabled, the PREPARE is executed at this time **********************************************************/ EXEC SQL open ins_curs; a = 2; while (a<100) { EXEC SQL put ins_curs from :a; a++; }
要对准备好的语句执行 DESCRIBE 语句, 您必须在该游标的初始 OPEN 语句之后执
GBase 8s ESQL/C 编程指南 南大通用数据技术股份有限公司 - 439 -
行该 DESCRIBE。在下列代码段中,第一个 DESCRIBE 语句失败,是因为它在该游标上 的第一个 OPEN 语句之前执行。第二个 DESCRIBE 语句成功,是因为它跟在 OPEN 语 句之后。 EXEC SQL BEGIN DECLARE SECTION; int a, a_type; EXEC SQL END DECLARE SECTION; EXEC SQL allocate descriptor 'desc'; EXEC SQL create database test; EXEC SQL create table table_x (col1 integer);
/********************************************** * Prepare an INSERT statement ****************************************************/ EXEC SQL prepare ins_stmt from 'insert into table_x values (?)';
/******************************************************************** * The DESCRIBE results in an error, because the description of the * statement is not determined until after the OPEN. The OPEN is what * actually sends the PREPARE statement to the database server and * requests a description for it.
**********************************************************************/ EXEC SQL describe ins_stmt using sql descriptor 'desc'; /* fails */ if (SQLCODE) printf("DESCRIBE : SQLCODE is %d\n", SQLCODE);
* Now DECLARE a cursor for the PREPARE statement and OPEN it.
**********************************************************************/ EXEC SQL declare ins_cursor cursor for ins_stmt; EXEC SQL open ins_cursor;
/********************************************************************* * Now the DESCRIBE returns the information about the columns to the * system-descriptor area.
**********************************************************************/ EXEC SQL describe ins_stmt using sql descriptor 'desc'; /* succeeds */ if (SQLCODE) printf("DESCRIBE : SQLCODE is %d\n", SQLCODE); a = 2; a_type = SQLINT; while (a<100) { EXEC SQL set descriptor 'desc' values 1 type = :a_type, data = :a; EXEC SQL put ins_curs using sql descriptor 'desc'; a++; }