GBase 8s SQL 指南:语法 南大通用数据技术股份有限公司 522 使用 FREE 释放为游标分配的资源。 使用 FOR READ ONLY 选项 使用 FOR READ ONLY 关键字可将游标定义为只读游标。声明为只读的游标不 可以用于更新(或删除)任何它得到的行。 对 FOR READ ONLY 关键字的需要取决于数据库是符合 ANSI 的还是不符合 ANSI 。 因为在不兼容 ANSI 的数据库中,DECLARE 语句定义的游标在缺省情况下是只 读游标,所以如果想将游标成为一个只读游标,您不需要指定 FOR READ ONLY 关键字。显式地指定 FOR READ ONLY 关键字的唯一好处是为了更好地编制程 序文档。 在兼容 ANSI 的数据库中,如果 SELECT 语句符合与游标相关联的 SELECT 语 句的子集 中列出的对更新游标的所有限制,那么通过 DECLARE 语句与 SELECT 语句相关联的游标在缺省情况下是一个更新游标。如果想让一个 Select 游标成为只读,则必须在声明该游标时使用 FOR READ ONLY 关键字。 数据库服务器可以针对只读游标使用比针对更新游标更为宽松的锁定。 下面的示例创建了一个只读游标: EXEC SQL declare z_curs cursor for select * from customer_ansi for read only; 使用 FOR UPDATE 选项 使用 FOR UPDATE 选项可声明更新游标。您可以使用更新游标修改(更新或删 除)当前的行。 在兼容 ANSI 的数据库中,如果一个 Select 游标不是使用 FOR READ ONLY 关 键字声明的,并且该游标遵守在与游标相关联的 SELECT 语句的子集中描述的对 更新游标的限制,那么可以使用该 Select 游标更新或删除数据。在声明游标时不 需要使用 FOR UPDATE 关键字。 下面的示例声明一个更新游标: EXEC SQL declare new_curs cursor for
GBase 8s SQL 指南:语法 南大通用数据技术股份有限公司 523 select * from customer_notansi for update; 在更新游标中,可以更新或是删除活动集中的行。在创建更新游标之后,可以通 过使用带 WHERE CURRENT OF 子句的 UPDATE 或 DELETE 语句更新或删除 当前选择的行。单词 CURRENT OF 是指最近取得的行;它们替代 WHERE 子句 中通常的测试表达式。 更新游标能让您执行使用 UPDATE 语句不可能实现的更新操作,因为做出更新 的决定以及新数据项的值取决于行的原始内容。您的程序可以在决定是否更新之 前评估或操纵选择的数据。UPDATE 语句无法询问正在更新的表。 您可以指定那些可以被更新的特殊列。这些列不需要出现在 Projection 子句的 Select 列表中。 连同列的列表使用 FOR UPDATE 当声明更新游标时,您可以通过包含 OF 关键字和列的列表将更新操作限制在特 定的列。您只可以修改随后 UPDATE 语句中的那些命名的列。列不需要在 SELECT 子句的选择列表中。 下一个示例声明了一个更新游标,并指定该游标只可以更新 customer_notansi 表中的 fname 和 lname 列: EXEC SQL declare name_curs cursor for select * from customer_notansi for update of fname, lname; 缺省情况下,除非声明为 FOR READ ONLY ,否则在兼容 ANSI 的数据库中的 Select 游标是一个更新游标,因此 FOR UPDATE 关键字是可选的。但是,如果 希望更新游标只能够修改表中的一些列,则必须在 FOR UPDATE OF column 列 表中指定这些列。 指定列的主要好处是便于文档的编制并防止编程出错。(数据库服务器拒绝更新 任何其它列。)另一个好处是在 SELECT 语句满足以下条件时性能得到增强: 可以使用索引处理 SELECT 语句。 列出的列不是用于处理 SELECT 语句的索引的一部分。 如果打算更新的列是用于处理 SELECT 语句的索引的一部分,则数据库服务器会 保留一个列表,其中列有每个更新的行,从而确保没有任何行被更新两次。如果
GBase 8s SQL 指南:语法 南大通用数据技术股份有限公司 524 OF 关键字指定了可以更新的列,则数据库服务器便会确定是否要保留更新行的 列表。如果数据库服务器确定保留该列表的工作已不再需要,则性能随之提高。 如果不使用 OF column 列表,数据库服务器通常会维护一个更新行的列表,尽管 此列表可能并不需要。 下面的示例包含将更新游标同 DELETE 语句使用删除当前行的 GBase 8s ESQL/C 代码。 每当删除行时,游标仍处于行之间。在删除数据之后,必须在可以引用 DELETE 或 UPDATE 语句中的游标之前使用 FETCH 语句将游标推进到下一行。 EXEC SQL declare q_curs cursor for select * from customer where lname matches :last_name for update;
EXEC SQL open q_curs; for (;;) { EXEC SQL fetch q_curs into :cust_rec; if (strncmp(SQLSTATE, "00", 2) != 0) break;
/* Display customer values and prompt for answer */ printf("\n%s %s", cust_rec.fname, cust_rec.lname); printf("\nDelete this customer? "); scanf("%s", answer);
if (answer[0] == 'y') EXEC SQL delete from customer where current of q_curs; if (strncmp(SQLSTATE, "00", 2) != 0) break; } printf("\n"); EXEC SQL close q_curs; 使用 Update 游标进行锁定 FOR UPDATE 关键字告知数据库服务器更新操作是可能的,并会引发数据库服务 器使用比对 Select 游标更严格的锁定。您声明一个更新游标可让数据库服务器知 道程序可能更新(或删除)它取得的作为 SELECT 语句一部分的任何行,更新游
GBase 8s SQL 指南:语法 南大通用数据技术股份有限公司 525 标为程序取得的行使用可升级锁定(也称写锁)。在程序修改行之前,会将行锁 提升为互斥锁。 使用 WITH HOLD 关键字声明一个更新游标是可能的,但是这样做的唯一原因是 将一长串的更新打断成较小的事务。您必须在同一个事务中取得并更新特定的 行。 如果操作涉及取和更新大量的行,则数据库服务器维护的锁表会溢出。防止这种 溢出的通常方式是锁定整个正在更新的表。如果此操作是不可能的,那么一种替 换的方式是通过 Hold 游标来更新,并按时间间隔执行 COMMIT WORK 。但是, 您必须谨慎地计划这样的应用程序,因为 COMMIT WORK 会释放所有的锁,甚 至是那些通过 Hold 游标放置的锁。 与顺序游标相关联的 INSERT 语句的子集 如 DECLARE 语句的程序中所述,要创建一个 Insert 游标,应将顺序游标与 INSERT 语句的限制格式相关联。INSERT 语句必须包括 VALUES 子句;它不 可以包含嵌入的 SELECT 语句。 下面的示例包含声明 Insert 游标的 GBase 8s ESQL/C 代码: EXEC SQL declare ins_cur cursor for insert into stock values (:stock_no,:manu_code,:descr,:u_price,:unit,:u_desc); Insert 游标简单地插入数据行;它不可以用于取得数据。当打开 Insert 游标时, 会在内存中创建一个缓冲区。该缓冲区在程序执行 PUT 语句时接收数据行。只 有在缓冲区满的时候才会将这些行写入磁盘。您可以在缓冲区还未满时使用 CLOSE 、FLUSH 或 COMMIT WORK 语句刷新缓冲区(即将它的内容写入数据 库)。该主题会在 CLOSE 、FLUSH 和 PUT 语句下做进一步讨论。 您必须在程序结束之前关闭 Insert 游标,从而将任何已缓冲的行插入数据库。如 果没有正确关闭游标,则会丢失数据。有关对 INSERT 语法和使用的完整讨论, 请参阅 INSERT 语句。 Insert 游标 当您将 INSERT 语句与一个游标相关联的时候,该游标称为 Insert 游标。Insert 游标是一个数据结构,它代表 INSERT 语句将要添加到数据库的行。INSERT 游
GBase 8s SQL 指南:语法 南大通用数据技术股份有限公司 527 游标特性 您可以将一个游标声明为顺序游标(缺省值)、滚动游标(通过使用 SCROLL 关键字)或保持游标(通过使用 WITH HOLD 关键字)。SCROLL 和 WITH HOLD 关键字并不互相排斥。后面的章节解释了这些结构特征。 Select 或 Function 游标可以是一个顺序游标或滚动游标。Insert 游标只可以是一 个顺序游标。在 ESQL/C 例程中,选择游标、函数游标和 Insert 游标可以有选择 地成为保持游标。(在 SPL 例程中,所有的游标都是顺序游标,但是只有 Select 游标可以是保持游标。) 缺省创建顺序游标 如果只使用 CURSOR 关键字,则会创建一个顺序游标,它只可以从活动集合按 顺序取得下一行。每次打开顺序游标时,顺序游标只可以从活动集合读一次。 如果正在为一个 Select 游标使用顺序游标,则在每次执行 FETCH 语句时,数据 库服务器会返回当前行的内容,并且找到活动集合中的下一行。 以下示例在一个不兼容 ANSI 的数据库中创建了一个只读顺序游标,在一个兼容 ANSI 的数据库汇总创建了一个更新顺序游标: EXEC SQL declare s_cur cursor for select fname, lname into :st_fname, :st_lname from orders where customer_num = 114; Insert 游标也具有顺序游标的特征。要创建一个 Insert 游标,请将顺序游标与 INSERT 语句的限制格式相关联。(有关更多信息,请参阅 Insert 游标。)以下 示例声明了一个 Insert 游标: EXEC SQL declare ins_cur cursor for insert into stock values (:stock_no,:manu_code,:descr,:u_price,:unit,:u_desc); 使用 SCROLL 关键字创建滚动游标 使用 SCROLL 关键字创建滚动游标,它可以任何顺序取活动集合的行。
GBase 8s SQL 指南:语法 南大通用数据技术股份有限公司 528 数据库服务器将游标的活动集合保留为一个临时表,直到关闭游标。您可以取得 活动集合的第一行、最后一行或任何一个中间行,以及在不必关闭再重新打开游 标的情况下重复取得一些行。(请参阅 FETCH。) 多用户系统中,在派生出活动集合的行的表中的行可能在打开游标之后有所更 改,并且会在临时表中生成一个副本。如果在一个事务内使用滚动游标,那么通 过将隔离级别设置为“可重复读取”或通过在事务期间以共享方式锁定整个表可以 防止更改复制的行。(请参阅 SET ISOLATION 和 LOCK TABLE。) 以下示例为 SELECT 语句创建了一个滚动游标: DECLARE sc_cur SCROLL CURSOR FOR SELECT * FROM orders; 您可以将 Select 和 Function 游标创建滚动游标,但不可为 Insert 游标创建滚动 游标。不可将滚动游标声明为 FOR UPDATE。 使用 WITH HOLD 关键字创建保持游标 使用 WITH HOLD 关键字创建 hold 游标。Hold 游标允许跨多个事务对行的集合 进行不中断的访问。 通常,所有的游标在事务结束时关闭。Hold 游标不会关闭;它在事务结束之后仍 保持打开状态。 Hold 游标可以是一个顺序游标或(在 ESQL/C 中)滚动游标。 在 SPL 例程中 WITH HOLD 关键字只对 Select 游标有效。有关 DECLARE 语 句在 SPL 例程中的语法,请参阅 在 SPL 例程中声明动态游标。 在 ESQL/C 中,可以使用 WITH HOLD 关键字声明 Select 和函数游标(具有顺序 和滚动属性)也可以声明 Insert 游标。这些关键字在 DECLARE 语句中跟在 CURSOR 关键字之后。以下示例为 SELECT 创建了一个顺序 Hold 游标: DECLARE hld_cur CURSOR WITH HOLD FOR SELECT customer_num, lname, city FROM customer; 您可以如下面的 GBase 8s ESQL/C 代码示例所示使用选择保持游标。此代码段使 用一个 Hold 游标作为主游标来扫描一个记录集合,使用一个顺序游标作为细节 游标俩指向位于不同表中的记录。主游标扫描的记录是更新细节游标指向的记录 的基础。在第一个 WHILE 循环的每个迭代的结束处的 COMMIT WORK 语句将 Hold 游标 c_master 保留为打开状态,但关闭顺序游标 c_detail 并释放所有锁。
GBase 8s SQL 指南:语法 南大通用数据技术股份有限公司 529 这种技术最小化了数据库服务器必须分配给锁和未完成的事务的资源,并且它使 其它用户能够立即访问更新的行。 EXEC SQL BEGIN DECLARE SECTION; int p_custnum, int save_status; long p_orddate; EXEC SQL END DECLARE SECTION;
EXEC SQL prepare st_1 from 'select order_date from orders where customer_num = ? for update'; EXEC SQL declare c_detail cursor for st_1; EXEC SQL declare c_master cursor with hold for select customer_num from customer where city = 'Pittsburgh';
EXEC SQL open c_master; if(SQLCODE==0) /* the open worked */ EXEC SQL fetch c_master into :p_custnum; /* discover first customer */ while(SQLCODE==0) /* while no errors and not end of pittsburgh customers */ { EXEC SQL begin work; /* start transaction for customer p_custnum */ EXEC SQL open c_detail using :p_custnum; if(SQLCODE==0) /* detail open succeeded */ EXEC SQL fetch c_detail into :p_orddate; /* get first order */ while(SQLCODE==0) /* while no errors and not end of orders */ { EXEC SQL update orders set order_date = '08/15/94' where current of c_detail; if(status==0) /* update was ok */ EXEC SQL fetch c_detail into :p_orddate; /* next order */ } if(SQLCODE==SQLNOTFOUND) /* correctly updated all found orders */ EXEC SQL commit work; /* make updates permanent, set status */ else /* some failure in an update */ { save_status = SQLCODE; /* save error for loop control */ EXEC SQL rollback work; SQLCODE = save_status; /* force loop to end */ } if(SQLCODE==0) /* all updates, and the commit, worked ok */
GBase 8s SQL 指南:语法 南大通用数据技术股份有限公司 530 EXEC SQL fetch c_master into :p_custnum; /* next customer? */ } EXEC SQL close c_master; 使用 CLOSE 语句显式地关闭 Hold 游标,或使用 CLOSE DATABASE 或 DISCONNECT 语句隐式地关闭 Hold 游标。CLOSE DATABASE 语句关闭所有 游标。 使用保持 Insert 游标 如果您将保持游标与 INSERT 语句相关联,则可以使用事务将一长串的 PUT 语 句打断为较小的 PUT 语句集合。除了等待 PUT 语句填充缓冲区并引发自动写 入数据库,您可以执行 COMMIT WORK 语句刷新行缓冲区。使用 Hold 游标, COMMIT WORK 会提交插入的行,但将游标保留为打开状态以供进一步插入。 当插入大量的行时,这样方法是期望的,因为暂时的未提交的工作会消耗数据库 服务器的资源。 与游标相关联的 SELECT 语句的子集 如 DECLARE 语句的语法表所示,不是所有的 SELECT 语句都可以与只读或更 新游标相关联。 如果 DECLARE 语句包含 FOR READ ONLY 或 FOR UPDATE 选项,则必须遵 守对 DECLARE 语句中包括的 SELECT 语句的某些限制(直接地或作为准备好 的语句)。 如果 DECLARE 语句包括 FOR READ ONLY 选项,则 SELECT 语句不可以有 FOR READ ONLY 或 FOR UPDATE 选项。(有关 SELEC 语法和用法的描述, 请参阅 SELECT 语句。) 如果 DECLARE 语句包括 FOR UPDATE 选项,则 SELECT 语句必须服从下列限制: 该语句只能从一个表中选择数据。 该语句不能包含任何聚集函数。 该语句还不能包含 FOR UPDATE 关键字。 该语句不可以包括任何以下子句或关键字:DISTINCT 、EXCEPT 、 FOR READ ONLY 、FOR UPDATE 、GROUP BY 、INTERSECT 、 INTO TEMP 、MINUS 、ORDER BY 、UNION 或 UNIQUE。
GBase 8s SQL 指南:语法 南大通用数据技术股份有限公司 531 不兼容 ANSI 的数据库中的游标的示例 在不兼容 ANSI 的数据库中,与 SELECT 语句相关联的游标在缺省情况下是一 个只读游标。以下的示例在一个不兼容 ANSI 的数据库中声明了一个只读游标: EXEC SQL declare cust_curs cursor for select * from customer_notansi; 如果想要在程序代码中明确此游标为只读游标,应指定 FOR READ ONLY 选 项,如下面的示例所示: EXEC SQL declare cust_curs cursor for select * from customer_notansi for read only; 如果要将这个游标变为一个更新游标,可在 DECLARE 语句中指定的 FOR UPDATE 选项。此示例声明了一个更新游标: EXEC SQL declare new_curs cursor for select * from customer_notansi for update; 如果要使一个更新游标只能够修改表中的一些列,则必须在 FOR UPDATE 子句 中指定那些列。下面的示例声明了一个只可以更新 customer_notansi 表中 fname 和 lname 列的更新游标: EXEC SQL declare name_curs cursor for select * from customer_notansi for update of fname, lname; 兼容 ANSI 的数据库中的游标的示例 在兼容 ANSI 的数据库中,与 SELECT 语句相关联的游标在缺省情况下是一个 更新游标。 下面的示例声明了一个兼容 ANSI 的数据库中的更新游标: EXEC SQL declare x_curs cursor for select * from customer_ansi; 要在程序文档中声明此游标为更新游标,您可以指定 FOR UPDATE 选项,如下 面的示例所示: EXEC SQL declare x_curs cursor for select * from customer_ansi for update; 如果希望一个更新游标只能够修改表中的一些列,则必须在 FOR UPDATE 选项 中指定这些列。下面的示例声明了一个更新游标,并指定该游标只可以更新 customer_ansi 表中的 fname 和 lname 列:
GBase 8s SQL 指南:语法 南大通用数据技术股份有限公司 532 EXEC SQL declare y_curs cursor for select * from customer_ansi for update of fname, lname; 如果要将一个游标变为只读游标,则必须通过在 DECLARE 语句中指定 FOR READ ONLY 选项来覆盖 DECLARE 语句的缺省行为。下面的示例声明了一个只读游标: EXEC SQL declare z_curs cursor for select * from customer_ansi for read only; 将游标与准备好的语句相关联 PREPARE 语句让您在运行时聚集 SQL 语句的文本,并将语句文本传送到数据 库服务器以供执行。如果您期望一个能返回值的动态准备好的 SELECT 、 EXECUTE FUNCTION(或 EXECUTE PROCEDURE)语句可生成多行数据,在 必须将该准备好的语句与一个游标相关联。(请参阅 PREPARE。) PREPARE 语句的结果是一个语句标识符(statement id 或 id variable),它是一 个代表准备好的语句文本的数据结构,要为语句文本声明一个游标,应将该游标 与语句标识符相关联。 您可以将一个顺序游标与任何准备好的 SELECT 或 EXECUTE FUNCTION (或 EXECUTE PROCEDURE)语句相关联。您不可以将一个滚动游标与准备好的 INSERT 语句或准备好包括 FOR UPDATE 子句的 SELECT 语句相关联。 在打开、使用和关闭游标之后,可以在相同的语句标识符下准备一个不同的语 句。以这种方式,一个单独的游标可能在不同的时刻与不同的语句使用。再次使 用游标之前,必须重新声明该游标。 下面的示例包含准备 SELECT 语句并为准备好的语句文本声明顺序游标的 GBase 8s ESQL/C 代码。首先通过返回值的 SELECT 语句准备语句标识符 st_1 ;然后 为 st_1 声明游标 c_detail。 EXEC SQL prepare st_1 from 'select order_date from orders where customer_num = ?'; EXEC SQL declare c_detail cursor for st_1; 如果想要使用准备好的 SELECT 语句修改数据,可将 FOR UPDATE 子句添加 到想要准备的语句文本,如下面的 GBase 8s ESQL/C 示例所示: EXEC SQL prepare sel_1 from
GBase 8s SQL 指南:语法 南大通用数据技术股份有限公司 539 . . . DATABASE stores_demo; LET first = "select * from state"; LET lsst = "where code < ?"; PREPARE stmt_1 FROM first || last; DECLARE cursor_1 FOR stmt_1; OPEN cursor_1 . . . CLOSE cursor_1; FREE cursor_1; FREE stmt_1; ... END FUNCTION;