#
第十二章 使用嵌入式SQL(四) # SQL游标 游标是指向数据的指针,该数据允许嵌入式SQL程序对所指向的记录执行操作。通过使用游标,Embedded SQL可以遍历结果集。嵌入式SQL可以使用游标执行查询,该查询从多个记录返回数据。嵌入式SQL还可以使用游标更新或删除多个记录。 必须首先对SQL游标进行`DECLARE`,并为其命名。在`DECLARE`语句中,提供了`SELECT`语句,该语句标识游标将指向的记录。然后,将此游标名称提供给`OPEN`游标语句。然后,反复发出`FETCH`游标语句以遍历`SELECT`结果集。然后,发出`CLOSE`游标语句。 - 基于游标的查询使用`DECLARE`游标名称`CURSOR FOR SELECT`来选择记录,并(可选)将`select`列值返回到输出主机变量中。 `FETCH`语句遍历结果集,使用这些变量返回选定的列值。 - 基于游标的`DELETE`或`UPDATE`使用`DECLARE`游标名`CURSOR FOR SELECT`选择操作的记录。没有指定输出主机变量。 `FETCH`语句遍历结果集。 `DELETE`或`UPDATE`语句包含`WHERE CURRENT OF`子句,以标识当前光标位置,以便对所选记录执行操作。 请注意,游标不能跨越方法。因此,必须在同一类方法中声明,打开,获取和关闭游标。在生成类和方法的所有代码(例如从`.CSP`文件生成的类)中考虑这一点很重要。 下面的示例使用游标执行查询并将结果显示给主体设备: ```java /// d ##class(PHA.TEST.SQL).CURSOR() ClassMethod CURSOR() { &sql(DECLARE C5 CURSOR FOR SELECT %ID,Name INTO :id, :name FROM Sample.Person WHERE Name %STARTSWITH 'A' ORDER BY Name ) &sql(OPEN C5) QUIT:(SQLCODE'=0) &sql(FETCH C5) While (SQLCODE = 0) { Write id, ": ", name,! &sql(FETCH C5) } &sql(CLOSE C5) } ``` ```java DHC-APP>d ##class(PHA.TEST.SQL).CURSOR() 95: Adams,Diane F. 183: Adams,Susan E. 71: Ahmed,Elmo X. 28: Alton,Martin S. 175: Alton,Phil T. 86: Anderson,Mario L. 131: Anderson,Valery N. ``` 此示例执行以下操作: 1. 声明一个游标`C1`,该游标返回一组按`Name`排序的`Person`行。 2. 打开游标。 3. 游标上调用`FETCH`直到到达数据末尾。每次调用`FETCH`之后,如果有更多数据要提取,则`SQLCODE`变量将设置为0。每次调用`FETCH`后,返回的值都将复制到`DECLARE`语句的`INTO`子句指定的主机变量中。 4. 关闭光标。 ## `DECLARE`游标声明 `DECLARE`语句同时指定了游标名称和定义游标的SQL `SELECT`语句。 `DECLARE`语句必须在例程中出现在使用游标的任何语句之前。 游标名称区分大小写。 **游标名称在类或例程中必须唯一。因此,递归调用的例程不能包含游标声明。在这种情况下,最好使用动态SQL。** 下面的示例声明一个名为`MyCursor`的游标: ```sql &sql(DECLARE MyCursor CURSOR FOR SELECT Name, DOB FROM Sample.Person WHERE Home_State = :state ORDER BY Name ) ``` `DECLARE`语句可以包括一个可选的`INTO`子句,该子句指定在遍历游标时将接收数据的本地主机变量的名称。例如,我们可以在前面的示例中添加一个`INTO`子句: ```sql &sql(DECLARE MyCursor CURSOR FOR SELECT Name, DOB INTO :name, :dob FROM Sample.Person WHERE Home_State = :state ORDER BY Name ) ``` **`INTO`子句可以包含逗号分隔的主机变量列表,单个主机变量数组或两者的组合。如果指定为以逗号分隔的列表,则`INTO`子句宿主变量的数量必须与游标的`SELECT`列表中的列数完全匹配,否则在编译该语句时会收到“基数不匹配”错误。** 如果`DECLARE`语句不包含`INTO`子句,则`INTO`子句必须出现在`FETCH`语句中。通过在`DECLARE`语句而不是`FETCH`语句中指定`INTO`子句,可能会导致性能的小幅提高。 因为`DECLARE`是声明,而不是执行的语句,所以它不会设置或终止`SQLCODE`变量。 如果已经声明了指定的游标,则编译将失败,并显示`SQLCODE -52`错误,游标名称已声明。 **执行`DECLARE`语句不会编译`SELECT`语句。 `SELECT`语句在第一次执行`OPEN`语句时被编译。嵌入式SQL不在常规编译时进行编译,而是在SQL执行时(运行时)进行编译。** ## OPEN游标声明 `OPEN`语句为后续执行准备了一个游标: ```sql &sql(OPEN MyCursor) ``` 执行`OPEN`语句将编译在`DECLARE`语句中找到的Embedded SQL代码,创建优化的查询计划,并生成缓存的查询。执行`OPEN`(在SQL运行时)时,会发出涉及缺少资源(例如未定义的表或字段)的错误。 成功调用`OPEN`后,`SQLCODE`变量将设置为0。 必须先调用`OPEN`才能从游标中获取数据。 ## `FETCH`游标声明 `FETCH`语句获取游标下一行的数据(由游标查询定义): ```java &sql(FETCH MyCursor) ``` 必须先对游标进行`DECLARE`并打开,然后才能在其上调用`FETCH`。 `FETCH`语句可以包含`INTO`子句,该子句指定在游标游标时将接收数据的本地主机变量的名称。例如,我们可以在前面的示例中添加一个`INTO`子句: ```sql &sql(FETCH MyCursor INTO :a, :b) ``` `INTO`子句可以包含逗号分隔的主机变量列表,单个主机变量数组或两者的组合。如果指定为以逗号分隔的列表,则`INTO`子句宿主变量的数量必须与游标的`SELECT`列表中的列数完全匹配,否则在编译该语句时,将收到`SQLCODE -76`“基数不匹配”错误。 通常,`INTO`子句是在`DECLARE`语句中指定的,而不是在`FETCH`语句中指定的。如果`DECLARE`语句中的`SELECT`查询和`FETCH`语句都包含`INTO`子句,则仅设置由`DECLARE`语句指定的主机变量。如果仅`FETCH`语句包含`INTO`子句,则将设置由`FETCH`语句指定的主机变量。 如果`FETCH`检索数据,则将`SQLCODE`变量设置为0;否则,将`SQLCODE`变量设置为0。如果没有数据(或没有更多数据)到`FETCH`,则将`SQLCODE`设置为100(没有更多数据)。主机变量值仅应在`SQLCODE = 0`时使用。 根据查询,第一次调用`FETCH`可能会执行其他任务(例如对临时数据结构中的值进行排序)。 ## CLOSE游标声明 `CLOSE`语句终止游标的执行: ``` &sql(CLOSE MyCursor) ``` **`CLOSE`语句清除查询执行所使用的任何临时存储。无法调用`CLOSE`的程序将遇到资源泄漏(例如,不需要的IRIS TEMP临时数据库增加)。** 成功调用`CLOSE`后,`SQLCODE`变量将设置为0。因此,在关闭游标之前,应检查最终的`FETCH`是否将`SQLCODE`设置为`0`或`100`。