在SQL中使用循环结构(转)

FOR,LOOP,WHILE,REPEAT是UDB/400的一种内部循环控制，用于遍历表中符合条件的每一行记录。

例如：
目的：更新employee库，把所有北京籍员工的工资提高10%

例一：使用FOR循环

代码

 1 --------------------------------------------
 2  CREATE PROCEDURE QGPL/TEST_FOR
 3 LANGUAGE SQL
 4  BEGIN
 5  FOR each_record AS
 6  ---cur01 CURSOR FOR
 7 ------SELECT * FROM code,salary,city from employee where city="Beijing"
 8 ---------DO
 9 ------------UPDATE employee
10 ------------SET salary=salary * 1.1
11 ------------WHERE CURRENT OF cur01;
12 ENDFOR;
13 END;
14

例二：使用LOOP循环

代码

 1 ----------------------------------------
 2 CREATE PROCEDURE QGPL/TEST_LOOP
 3 LANGUAGE SQL
 4 BEGIN
 5 DECLARE code_v char(10);
 6 DECLARE salary_v integer;
 7 DECLARE city_v char(20);
 8 
 9 DECLARE C1 CURSOR FOR
10 ---SELECT code,salary,city FROM employee WHERE city="Beijing";
11 OPEN C1;
12 loop_label:
13 LOOP
14 - FETCH C1 INTO code_v,salary_v,city_v;
15 --IF SQLCODE=0 THEN 
16 ------SET salary_v=salary_v*1.1;
17 ------UPDATE employee SET salary=salary_v
18 ---------WHERE CURRENT OF C1;
19 --ELSE
20 ------LEAVE loop_label;
21 --END IF;
22 END LOOP loop_label;
23 CLOSE C1;
24 END;
25 
26

例三：使用WHILE循环

代码

 1 ---------------------------------------
 2 CREATE PROCEDURE QGPL/TEST_WHILE
 3 LANGUAGE SQL
 4 BEGIN
 5 DECLARE code_v char(10);
 6 DECLARE salary_v integer;
 7 DECLARE city_v char(20);
 8 DECLARE at_end integer;
 9 
10 DECLARE C1 CURSOR FOR
11 ---SELECT code,salary,city FROM employee WHERE city="Beijing";
12 OPEN C1;
13 
14 SET at_end=0;
15 WHILE at_end = 0 DO
16 --FETCH C1 INTO code_v,salary_v,city_v;
17 --IF SQLCODE=0 THEN 
18 ------SET salary_v=salary_v*1.1;
19 ------UPDATE employee SET salary=salary_v
20 ---------WHERE CURRENT OF C1;
21 --ELSE
22 ------SET at_end=1;
23 --END IF;
24 END WHILE;
25 CLOSE C1;
26 END;
27 
28

例四：使用REPEAT循环

代码

 1 ------------------------------------------------
 2 CREATE PROCEDURE QGPL/TEST_REPEAT
 3 LANGUAGE SQL
 4 BEGIN
 5 DECLARE code_v char(10);
 6 DECLARE salary_v integer;
 7 DECLARE city_v char(20);
 8 
 9 DECLARE C1 CURSOR FOR
10 ---SELECT code,salary,city FROM employee WHERE city="Beijing";
11 OPEN C1;
12 
13 repeat_label:
14 REPEAT
15 --FETCH C1 INTO code_v,salary_v,city_v;
16 --IF SQLCODE=0 THEN 
17 ------SET salary_v=salary_v*1.1;
18 ------UPDATE employee SET salary=salary_v
19 ---------WHERE CURRENT OF C1;
20 --END IF;
21 --UNTIL SQLCODE<>0;
22 END REPEAT repeat_loop;
23 CLOSE C1;
24 END;
25 
26

总结：四种循环结构实现的功能基本相同，用户可以根据自己的习惯选择使用。

原文：http://www.cnblogs.com/wildfish/archive/2008/01/09/1031943.html