Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1062070
  • 博文数量: 106
  • 博客积分: 9093
  • 博客等级: 中将
  • 技术积分: 2770
  • 用 户 组: 普通用户
  • 注册时间: 2006-06-01 17:22
文章分类

全部博文(106)

文章存档

2014年(1)

2012年(29)

2011年(32)

2010年(20)

2008年(24)

分类:

2008-07-17 14:03:44

如何在SQL中使用循环结构

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

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

例一:使用FOR循环
--------------------------------------------
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循环
----------------------------------------
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
WHERE CURRENT OF C1;
ELSE
LEAVE loop_label;
END IF;
END LOOP loop_label;
CLOSE C1;
END;

例三:使用WHILE循环
---------------------------------------
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;

例四:使用REPEAT循环
------------------------------------------------
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;

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

阅读(1937) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~