循環(huán)操作是數(shù)據(jù)庫中經(jīng)常遇到的操作，那么如何在SQL中使用循環(huán)結(jié)構(gòu)呢？下文將帶您尋找答案。

FOR,LOOP,WHILE,REPEAT是UDB/400的一種內(nèi)部循環(huán)控制，用于遍歷表中符合條件的每一行記錄。
例如：
目的：更新employee庫，把所有北京籍員工的工資提高10%

例一：使用FOR循環(huán)
--------------------------------------------
CREATE PROCEDURE QGPL/TEST_FOR
LANGUAGE SQL
BEGIN
FOR each_record AS
---cur01 CURSOR FOR
------SELECT * FROM code,salary,city from employee where city="Beijing"
---------DO
------------UPDATE employee
------------SET salary=salary * 1.1
------------WHERE CURRENT OF cur01;
ENDFOR;
END;

例二：使用LOOP循環(huán)
----------------------------------------
CREATE PROCEDURE QGPL/TEST_LOOP
LANGUAGE SQL
BEGIN
DECLARE code_v char(10);
DECLARE salary_v integer;
DECLARE city_v char(20);

DECLARE C1 CURSOR FOR
---SELECT code,salary,city FROM employee WHERE city="Beijing";
OPEN C1;
loop_label:
LOOP
- FETCH C1 INTO code_v,salary_v,city_v;
--IF SQLCODE=0 THEN
------SET salary_v=salary_v*1.1;
------UPDATE employee SET salary=salary_v#p#
---------WHERE CURRENT OF C1;
--ELSE
------LEAVE loop_label;
--END IF;
END LOOP loop_label;
CLOSE C1;
END;

例三：使用WHILE循環(huán)
---------------------------------------
CREATE PROCEDURE QGPL/TEST_WHILE
LANGUAGE SQL
BEGIN
DECLARE code_v char(10);
DECLARE salary_v integer;
DECLARE city_v char(20);
DECLARE at_end integer;

DECLARE C1 CURSOR FOR
---SELECT code,salary,city FROM employee WHERE city="Beijing";
OPEN C1;

SET at_end=0;
WHILE at_end = 0 DO
--FETCH C1 INTO code_v,salary_v,city_v;
--IF SQLCODE=0 THEN
------SET salary_v=salary_v*1.1;
------UPDATE employee SET salary=salary_v
---------WHERE CURRENT OF C1;
--ELSE
------SET at_end=1;
--END IF;
END WHILE;
CLOSE C1;
END;#p#

例四：使用REPEAT循環(huán)
------------------------------------------------
CREATE PROCEDURE QGPL/TEST_REPEAT
LANGUAGE SQL
BEGIN
DECLARE code_v char(10);
DECLARE salary_v integer;
DECLARE city_v char(20);

DECLARE C1 CURSOR FOR
---SELECT code,salary,city FROM employee WHERE city="Beijing";
OPEN C1;

repeat_label:
REPEAT
--FETCH C1 INTO code_v,salary_v,city_v;
--IF SQLCODE=0 THEN
------SET salary_v=salary_v*1.1;
------UPDATE employee SET salary=salary_v
---------WHERE CURRENT OF C1;
--END IF;
--UNTIL SQLCODE<>0;
END REPEAT repeat_loop;
CLOSE C1;
END;

總結(jié)：四種循環(huán)結(jié)構(gòu)實現(xiàn)的功能基本相同，用戶可以根據(jù)自己的習(xí)慣選擇使用。
 

【編輯推薦】

sql多數(shù)據(jù)庫查詢批量插入問題循環(huán)

SQL中游標(biāo)嵌套循環(huán)的示例

sql循環(huán)插入的語句寫法

SQL循環(huán)語句的妙用

SQL中UNION指令的用法