# Prayer

posts - 1256, comments - 190, trackbacks - 0, articles - 0
C++博客 :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理

本文中的存储过程示例，预计的循环次数是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

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
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