#
第十二章 使用嵌入式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`。