Prayer

    本文中的存储过程示例，预计的循环次数是yh表行数，由于SELECT IID INTO PINT FROM YH WHERE 0=1;不返回任何行，所以at_end后会立刻等于1（只循环一次就退出）。
 

DECLARE  at_end  INT  DEFAULT  0; 
DECLARE PIID INTEGER DEFAULT 0 ;
DECLARE PINT INTEGER DEFAULT 0 ;
DECLARE  not_found  CONDITION  FOR  SQLSTATE  '02000';
--DECLARE PCOUNT INTEGER;
DECLARE  c1  CURSOR  FOR   
SELECT IID FROM YH; 
DECLARE  CONTINUE  HANDLER  FOR  not_found 
SET  at_end  =  1; 
OPEN  c1; 
SET PCOUNT=0;
ins_loop:
LOOP 
FETCH  c1  INTO  PIID; 
IF  at_end  <>0    THEN 
LEAVE  ins_loop; 
END  IF; 
SET PCOUNT=PCOUNT+1;
SELECT IID INTO PINT FROM YH WHERE 0=1;
END LOOP;

http://www.souzz.net/html/database/DB2/69884.html

CREATE PROCEDURE bump_salary_iftest (IN deptnumber SMALLINT)
LANGUAGE SQL
BEGIN
  DECLARE SQLSTATE CHAR(5);
  DECLARE v_salary DOUBLE;
  DECLARE v_years SMALLINT;
  DECLARE v_id SMALLINT;
  DECLARE at_end INT DEFAULT 0;
  DECLARE not_found CONDITION FOR SQLSTATE '02000';

  DECLARE C1 CURSOR FOR
    SELECT id, CAST(salary AS DOUBLE), years
    FROM staff;
  DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;

  OPEN C1;
  FETCH C1 INTO v_id, v_salary, v_years;
  WHILE at_end = 0 DO

      UPDATE staff
        SET salary = 2150 * v_years
        WHERE id = -1;

    FETCH C1 INTO v_id, v_salary, v_years;
  END WHILE;
  CLOSE C1;
END

请问，我想让这个循环执行完，但是由于SET salary = 2150 * v_years
        WHERE id = -1 更新的数据为0条，at_end变量就被置为1，从而导程序跳出循环，我如何做，能够及时更新数据为0行
，而循环还能继续下去呢，请高手指点
                                                              

解决方案：

可以先计算循环次数，根据这个数值进行循环 ：

CREATE  PROCEDURE  bump_salary_iftest(IN deptnumber SMALLINT,out iReturn int)
LANGUAGE  SQL
BEGIN
      DECLARE  SQLSTATE  CHAR(5);
      DECLARE  v_salary  DOUBLE;
      DECLARE  v_years  SMALLINT;
      DECLARE  v_id  SMALLINT;
      DECLARE  at_end  INT  DEFAULT  0;
      DECLARE  not_found  CONDITION  FOR  SQLSTATE  '02000';
     
      Declare  v_temp int;

      DECLARE  C1  CURSOR  FOR
          SELECT  id,  CAST(salary  AS  DOUBLE),  years
          FROM  staff;
      DECLARE  CONTINUE  HANDLER  FOR  not_found
          SET  at_end  =  1;
         
      -- get the loop number
  select count(*) into v_temp from staff;
  set iReturn =0;
      OPEN  C1;
      FETCH  C1  INTO  v_id,  v_salary,  v_years;
      WHILE  v_temp>0  DO
            -- out the iReturn to check the result
set iReturn =iReturn+1;
            UPDATE  staff
                  SET  salary  =  2150  *  v_years
                  WHERE  id  =  -1;
          FETCH  C1  INTO  v_id,  v_salary,  v_years;
          set v_temp = v_temp-1;
      END  WHILE;
      CLOSE  C1;
END@
 
 
http://topic.csdn.net/u/20071214/14/2c93b395-76c6-4a9d-b017-6733562edcfa.html