# 循环语句
#### 简单LOOP语句
**语法图**
loop::=语句如[图1]所示。
图1loop::=   
![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/figure/zh-cn_image_0000002527058402.png "点击放大")
label declaration ::=语句如[图2]所示。
图2label declaration ::=   
![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/figure/zh-cn_image_0000002558058245.png)
**示例**
```
gaussdb=# CREATE OR REPLACE PROCEDURE proc_loop(i in integer, count out integer) 
AS 
    BEGIN 
        count:=0; 
        LOOP 
        IF count > i THEN 
            raise info 'count is %. ', count;  
            EXIT; 
        ELSE 
            count:=count+1; 
        END IF; 
        END LOOP; 
    END;
/
CREATE PROCEDURE
gaussdb=# CALL proc_loop(10,5);
INFO:  count is 11. 
 count 
-------
    11
(1 row)
```
![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/public_sys-resources/notice_3.0-zh-cn.png)
该循环必须要结合EXIT使用，否则将陷入死循环。
#### WHILE_LOOP语句
**语法图**
while_loop::=语句如[图3]所示。
图3while_loop::=   
![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/figure/zh-cn_image_0000002526898454.png "点击放大")
label declaration ::=语句如[图4]所示。
图4label declaration ::=   
![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/figure/zh-cn_image_0000002558058289.png)
只要条件表达式为真，WHILE语句就会不停地在一系列语句上进行循环，在每次进入循环体的时候进行条件判断。
**示例**
```
gaussdb=# CREATE TABLE integertable(c1 integer) DISTRIBUTE BY hash(c1); 
CREATE TABLE
gaussdb=# CREATE OR REPLACE PROCEDURE proc_while_loop(maxval in integer) 
AS 
    DECLARE 
    i int :=1;  
    BEGIN 
        WHILE i < maxval LOOP 
            INSERT INTO integertable VALUES(i); 
            i:=i+1; 
        END LOOP; 
    END; 
/
CREATE PROCEDURE
--调用存储过程
gaussdb=# CALL proc_while_loop(10);
 proc_while_loop 
-----------------
(1 row)
--删除存储过程和表
gaussdb=# DROP PROCEDURE proc_while_loop;
DROP PROCEDURE
gaussdb=# DROP TABLE integertable;
DROP TABLE
```
#### FOR_LOOP（integer变量）语句
**语法图**
for_loop::=语句如[图5]所示。
图5for_loop::=   
![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/figure/zh-cn_image_0000002527058368.png "点击放大")
label declaration ::=语句如[图6]所示。
图6label declaration ::=   
![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/figure/zh-cn_image_0000002558218215.png)
![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/public_sys-resources/note_3.0-zh-cn.png)
- 变量name会自动定义为integer类型并且只在此循环里存在。变量name介于lower_bound和upper_bound之间。
- 当使用REVERSE关键字时，lower_bound必须大于等于upper_bound，否则循环体不会被执行。
 
**示例**
```
--从0到5进行循环
gaussdb=# CREATE OR REPLACE PROCEDURE proc_for_loop()
AS
    BEGIN
    FOR I IN 0..5 LOOP
        DBE_OUTPUT.PRINT_LINE('It is '||to_char(I) || ' time;') ;
    END LOOP;
END;
/
CREATE PROCEDURE
--调用存储过程
gaussdb=# CALL proc_for_loop();
It is 0 time;
It is 1 time;
It is 2 time;
It is 3 time;
It is 4 time;
It is 5 time;
 proc_for_loop 
---------------
(1 row)
--删除存储过程
gaussdb=# DROP PROCEDURE proc_for_loop;
DROP PROCEDURE
```
#### FOR_LOOP查询语句
**语法图**
for_loop_query::=语句如[图7]所示。
图7for_loop_query::=   
![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/figure/zh-cn_image_0000002558218239.png "点击放大")
label declaration ::=语句如如[图8]所示。
图8label declaration ::=   
![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/figure/zh-cn_image_0000002527058416.png)
![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/public_sys-resources/note_3.0-zh-cn.png)
- 变量target会自动定义，类型和query的查询结果的类型一致，并且只在此循环中有效。target的取值就是query的查询结果。
- query可以使用EXECUTE增加动态查询，通过USING向查询插入参数。详见[示例]中的动态查询相关内容。
 
**示例**
```
--循环输出查询结果。
gaussdb=# CREATE OR REPLACE PROCEDURE proc_for_loop_query()
AS 
    record VARCHAR2(50);
BEGIN 
    FOR record IN SELECT spcname FROM pg_tablespace LOOP 
    dbe_output.print_line(record); 
    END LOOP; 
END; 
/
CREATE PROCEDURE
--调用存储过程
gaussdb=# CALL proc_for_loop_query();
pg_default
pg_global
 proc_for_loop_query 
---------------------
(1 row)
--删除存储过程
gaussdb=# DROP PROCEDURE proc_for_loop_query;
DROP PROCEDURE
--动态查询
gaussdb=# CREATE TABLE t1(id int);
gaussdb=# INSERT INTO t1 VALUES(1);
gaussdb=# 
DECLARE 
a int := 1;
item t1%rowtype;
BEGIN
    FOR item IN EXECUTE 'SELECT * FROM t1 WHERE id = :1' USING a LOOP
        RAISE INFO '%', item;
    END LOOP;
END;
/
INFO:  (1)
ANONYMOUS BLOCK EXECUTE
gaussdb=# DROP TABLE t1;
```
#### FORALL批量查询语句
**语法图**
forall::=语句图[图9]所示。
图9forall::=   
![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/figure/zh-cn_image_0000002526898416.png "点击放大")
label declaration ::语句如[图10]所示。
图10label declaration ::=   
![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/figure/zh-cn_image_0000002527058356.png)
![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/public_sys-resources/note_3.0-zh-cn.png)
- 变量index会自动定义为integer类型并且只在此循环里存在。index的取值介于low_bound和upper_bound之间。
- 使用FORALL + BULK COLLECT INTO时，INTO的变量仅返回单条DML语句执行结果。
 
**示例**
```
gaussdb=# CREATE TABLE TEST_t1 (
  title NUMBER(6),
  did VARCHAR2(20),
  data_period VARCHAR2(25),
  kind VARCHAR2(25),
  interval VARCHAR2(20),
  time DATE,
  isModified VARCHAR2(10)
)
DISTRIBUTE BY hash(did);
CREATE TABLE
gaussdb=# INSERT INTO TEST_t1 VALUES( 8, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', to_date('21-06-1999', 'dd-mm-yyyy'), 'SH_CLERK' );
INSERT 0 1
gaussdb=# CREATE OR REPLACE PROCEDURE proc_forall()
AS 
BEGIN 
    FORALL i IN 100..120 
        update TEST_t1 set title = title + 100*i;
END; 
/
CREATE PROCEDURE
--调用存储过程
gaussdb=# CALL proc_forall();
 proc_forall 
-------------
(1 row)
--查询存储过程调用结果
gaussdb=# SELECT * FROM TEST_t1;
 title  |  did   | data_period |   kind   |   interval   |        time         | ismodified 
--------+--------+-------------+----------+--------------+---------------------+------------
 231008 | Donald | OConnell    | DOCONNEL | 650.507.9833 | 1999-06-21 00:00:00 | SH_CLERK
(1 row)
--删除存储过程和表
gaussdb=# DROP PROCEDURE proc_forall;
DROP PROCEDURE
gaussdb=# DROP TABLE TEST_t1;
DROP TABLE
```