在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
