GaussDB-循环语句
简单LOOP语句
语法图
loop::=语句如图1所示。
label declaration ::=语句如图2所示。
示例
| ``` 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)
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
NOTICE:
该循环必须要结合EXIT使用，否则将陷入死循环。
#### WHILE_LOOP语句
**语法图**
while_loop::=语句如[图3](https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/devg-dist/gaussdb-12-0788.html#ZH-CN_TOPIC_0000001865585816__zh-cn_topic_0000001656219232_zh-cn_topic_0059778638_fc93167bb5ad54010979321d4e0fdbd55)所示。
[]()[]()**图3** while_loop::=
label declaration ::=语句如[图4](https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/devg-dist/gaussdb-12-0788.html#ZH-CN_TOPIC_0000001865585816__zh-cn_topic_0000001656219232_fig96691352705)所示。
[]()[]()**图4** label declaration ::=
只要条件表达式为真，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](https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/devg-dist/gaussdb-12-0788.html#ZH-CN_TOPIC_0000001865585816__zh-cn_topic_0000001656219232_zh-cn_topic_0059778638_f225dca3e3bb8473198031bfc2a49f3e1)所示。
[]()[]()**图5** for_loop::=
label declaration ::=语句如[图6](https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/devg-dist/gaussdb-12-0788.html#ZH-CN_TOPIC_0000001865585816__zh-cn_topic_0000001656219232_fig12963261021)所示。
[]()[]()**图6** label declaration ::=
- 变量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](https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/devg-dist/gaussdb-12-0788.html#ZH-CN_TOPIC_0000001865585816__zh-cn_topic_0000001656219232_zh-cn_topic_0059778638_f4043c6dc0195466388bb2b9b42874e52)所示。
[]()[]()**图7** for_loop_query::=
label declaration ::=语句如如[图8](https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/devg-dist/gaussdb-12-0788.html#ZH-CN_TOPIC_0000001865585816__zh-cn_topic_0000001656219232_fig676375317468)所示。
[]()[]()**图8** label declaration ::=
变量target会自动定义，类型和query的查询结果的类型一致，并且只在此循环中有效。target的取值就是query的查询结果。
**示例**
| ```
--循环输出查询结果。 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
``` |
| ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
#### FORALL批量查询语句
**语法图**
forall::=语句图[图9](https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/devg-dist/gaussdb-12-0788.html#ZH-CN_TOPIC_0000001865585816__zh-cn_topic_0000001656219232_zh-cn_topic_0059778638_f1977a3c1a8284e4da8a52c042293d873)所示。
[]()[]()**图9** forall::=
label declaration ::语句如[图10](https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/devg-dist/gaussdb-12-0788.html#ZH-CN_TOPIC_0000001865585816__zh-cn_topic_0000001656219232_fig1091785614483)所示。
[]()[]()**图10** label declaration ::=
变量index会自动定义为integer类型并且只在此循环里存在。index的取值介于low_bound和upper_bound之间。
**示例**
| ```
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
``` |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
更多详情请参考GaussDB 文档中心：<https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/productdesc/qlh_03_0001.html>
