在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

转载于:https://www.cnblogs.com/pfs1314/archive/2010/04/06/1705734.html