如何在SQL中使用循环结构

1、目的：更新employee库，把所有北京籍员工的工资提高10%例一：使用FOR循环--------------------------------------------CREATE PROCEDURE TEST_FORLANGUAGE SQLBEGINFOR 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;END FOR;END;

2、例二：使用LOOP循环----------------------------------------CREATE PROCEDURE TEST_LOOPLANGUAGE SQLBEGINDECLARE 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;

3、例三：使用WHILE循环---------------------------------------CREATE PROCEDURE TEST_WHILELANGUAGE SQLBEGINDECLARE 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;

4、例四：使用REPEAT循环------------------------------------------------CREATE PROCEDURE TEST_REPEATLANGUAGE SQLBEGINDECLARE 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;