Prayer

    本文中的存儲(chǔ)過(guò)程示例，預(yù)計(jì)的循環(huán)次數(shù)是yh表行數(shù)，由于SELECT IID INTO PINT FROM YH WHERE 0=1;不返回任何行，所以at_end后會(huì)立刻等于1（只循環(huán)一次就退出）。
 

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

請(qǐng)問(wèn)，我想讓這個(gè)循環(huán)執(zhí)行完，但是由于SET salary = 2150 * v_years
        WHERE id = -1 更新的數(shù)據(jù)為0條，at_end變量就被置為1，從而導(dǎo)程序跳出循環(huán)，我如何做，能夠及時(shí)更新數(shù)據(jù)為0行
，而循環(huán)還能繼續(xù)下去呢，請(qǐng)高手指點(diǎn)
                                                              

解決方案：

可以先計(jì)算循環(huán)次數(shù)，根據(jù)這個(gè)數(shù)值進(jìn)行循環(huán) ：

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