南大通用数据库-Gbase-8a-学习-03-存储过程及函数之游标、WHILE、REPEAT

一、小例子

（1）存储过程实现内容

将表czg的数据查询出来再插入到tmp表中。

存储过程定义（REPEAT循环）：

CREATE PROCEDURE TestRefCursorRepeat() 
BEGIN 
    DECLARE s_region int; 
    DECLARE ExitFlag INT; 
    DECLARE cur REF CURSOR; 
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET ExitFlag = 1;
    
    set ExitFlag = 0;
    
    OPEN cur FOR SELECT a FROM czg; 
    REPEAT FETCH cur INTO s_region; 
        if !ExitFlag then
            insert into tmp values(s_region);
        end if; 
    UNTIL ExitFlag END REPEAT;
    CLOSE cur; 
    commit;
end;

存储过程定义（WHILE循环）：

CREATE PROCEDURE TestRefCursor(in TableName varchar) 
BEGIN 
    DECLARE s_region int; 
    DECLARE ExitFlag INT; 
    DECLARE cur REF CURSOR; 
    #DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET ExitFlag = 1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET ExitFlag = 1;
    #declare SelectSql varchar(100);
    
    set @SelectSql = 'SELECT a FROM '||TableName;
    set ExitFlag = 0;
    
    OPEN cur FOR @SelectSql; 
    while ExitFlag = 0 do
        FETCH cur INTO s_region;
        #SELECT s_region; 
        if ExitFlag = 0 then
            insert into tmp values(s_region);
        end if;
    end while;
    CLOSE cur; 
    commit;
end;

（2）测试步骤

生成czg表测试数据请查看之前的链接：《南大通用数据库-Gbase-8a-学习-01-存储过程及函数之IF、LOOP、LEAVE、SET》

gbase> show tables;
+---------------+
| Tables_in_czg |
+---------------+
| czg           |
+---------------+
1 row in set (Elapsed: 00:00:00.00)


gbase> show create table czg;
+-------+-------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------+
| czg   | CREATE TABLE "czg" (
  "a" int(11) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+-------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)


gbase> create table tmp(a int);
Query OK, 0 rows affected (Elapsed: 00:00:00.05)


gbase> drop procedure if exists TestRefCursorRepeat;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)


gbase> DELIMITER //
gbase> CREATE PROCEDURE TestRefCursorRepeat() 
    -> BEGIN 
    ->     DECLARE s_region int; 
    ->     DECLARE ExitFlag INT; 
    ->     DECLARE cur REF CURSOR; 
    ->     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET ExitFlag = 1;
    ->     
    ->     set ExitFlag = 0;
    ->     
    ->     OPEN cur FOR SELECT a FROM czg; 
    ->     REPEAT FETCH cur INTO s_region; 
    ->         if !ExitFlag then
    ->             insert into tmp values(s_region);
    ->         end if; 
    ->     UNTIL ExitFlag END REPEAT;
    ->     CLOSE cur; 
    ->     commit;
    -> end; //
Query OK, 0 rows affected (Elapsed: 00:00:00.00)


gbase> select * from czg;
    -> //
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
|   99 |
+------+
11 rows in set (Elapsed: 00:00:00.00)


gbase> call TestRefCursorRepeat();//
Query OK, 0 rows affected (Elapsed: 00:00:00.10)


gbase> select * from tmp;//
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
|   99 |
+------+
11 rows in set (Elapsed: 00:00:00.00)


gbase> truncate table tmp;//
Query OK, 11 rows affected (Elapsed: 00:00:00.01)


gbase> drop procedure if exists TestRefCursor;//
Query OK, 0 rows affected, 1 warning (Elapsed: 00:00:00.00)


gbase> CREATE PROCEDURE TestRefCursor(in TableName varchar) 
    -> BEGIN 
    ->     DECLARE s_region int; 
    ->     DECLARE ExitFlag INT; 
    ->     DECLARE cur REF CURSOR; 
    ->     #DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET ExitFlag = 1;
    ->     DECLARE CONTINUE HANDLER FOR NOT FOUND SET ExitFlag = 1;
    ->     #declare SelectSql varchar(100);
    ->     
    ->     set @SelectSql = 'SELECT a FROM '||TableName;
    ->     set ExitFlag = 0;
    ->     
    ->     OPEN cur FOR @SelectSql; 
    ->     while ExitFlag = 0 do
    ->         FETCH cur INTO s_region;
    ->         #SELECT s_region; 
    ->         if ExitFlag = 0 then
    ->             insert into tmp values(s_region);
    ->         end if;
    ->     end while;
    ->     CLOSE cur; 
    ->     commit;
    -> end;//
Query OK, 0 rows affected (Elapsed: 00:00:00.00)


gbase> call TestRefCursor('czg');//
Query OK, 0 rows affected (Elapsed: 00:00:00.13)


gbase> select * from tmp;//
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
|   99 |
+------+
11 rows in set (Elapsed: 00:00:00.00)

二、涉及的语法

（1）REPEAT

语法格式：

REPEAT
    具体逻辑
UNTIL 退出条件 END REPEAT;

语法解释：
类似于C语言的do…while语法，先把中间的具体逻辑执行一遍，再判断是否进入循环，但UNTIL后面跟的是退出条件，而不是循环条件，这一点要注意哦。

（2）WHILE

语法格式：

WHILE 循环条件 DO
    具体逻辑
END WHILE;

语法解释：
类似于C语言的while语法，先判断是否具备进入循环的条件，是再进入内部进行具体逻辑的执行。

（3）特殊用法

语法格式：

DECLARE ExitFlag INT; 
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET ExitFlag = 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET ExitFlag = 1;

语法解释：
通常是结合游标、循环fetch数据的，如果没有fetch到数据，则将ExitFlag重置为1，以便我们来判断什么时候推出循环。