南大通用GBase 8a动态游标样例

GBase 8a数据库集群支持动态游标 REF CURSOR，其具体的SQL在open是指定。如果SQL也是拼接的，可以先复制到一个变量，比如@sql,然后open cur for @sql。

样例

drop procedure if exists p_cursor;
delimiter //

CREATE PROCEDURE p_cursor()
BEGIN
 declare tmp int;
 DECLARE DONE INT DEFAULT(0);
 DECLARE cur REF CURSOR;
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
 OPEN cur FOR SELECT DISTINCT id FROM t1 LIMIT 6;
 REPEAT
 FETCH cur INTO tmp;
   IF NOT done THEN
     SELECT tmp; -- 调试时，这个写这里，确认循环写法没有错误。
   END IF;
 UNTIL DONE END REPEAT;
 CLOSE cur;
END // 
delimiter ;

执行结果

gbase> select distinct id from t1 limit 6;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (Elapsed: 00:00:00.11)

gbase> call p_cursor();
+------+
| tmp  |
+------+
|    1 |
+------+
1 row in set (Elapsed: 00:00:00.07)

+------+
| tmp  |
+------+
|    2 |
+------+
1 row in set (Elapsed: 00:00:00.07)

+------+
| tmp  |
+------+
|    3 |
+------+
1 row in set (Elapsed: 00:00:00.07)

+------+
| tmp  |
+------+
|    4 |
+------+
1 row in set (Elapsed: 00:00:00.08)

Query OK, 0 rows affected (Elapsed: 00:00:00.08)