关于这个错误处理DECLARE CONTINUE HANDLER FOR NOT FOUND

显示全部楼层 · 2014-2-18 16:41:11

CREATE PROCEDURE UPDATE_LEVEL4
(IN RATING INTEGER
)
DYNAMIC RESULT SETS 2
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN

DECLARE varlevel CHAR(12) default '10';

--DECLARE rs2 CURSOR WITH RETURN TO CALLER FOR

DECLARE RS1 CURSOR WITH RETURN TO CALLER FOR

SELECTREGION_ID FROM dim_region WHERE REGION_ID=1109040302;

DECLARECONTINUE HANDLER FOR NOT FOUND

--UPDATE dim_region SET region_level='555589' WHERE REGION_ID=1109052401;

BEGIN

UPDATE dim_region SET region_level='66678' WHERE REGION_ID=1109040302;

END;

UPDATE dim_region SET region_level='66678' WHERE REGION_ID=1109052401;

OPEN RS1;
SELECT region_level INTO varlevel FROM dim_region WHERE REGION_ID=1109052401;
-- open rs2;

--IF rtrim(varlevel)='100' THEN

BEGIN

DECLARE RS2 CURSOR WITH RETURN TO CALLER FOR

SELECTregion_level ,REGION_ID FROM dim_region WHERE REGION_ID=1109052401 OR REGION_ID=1109040302;

OPEN RS2;

CLOSE RS2;

END;
-- END IF;
close RS1;

END;
不管我这个语句 DECLARE RS1 CURSOR WITH RETURN TO CALLER FOR

SELECTREGION_ID FROM dim_region WHERE REGION_ID=1109040302有没有返回记录，都不会执行下面的 BEGIN

UPDATE dim_region SET region_level='66678' WHERE REGION_ID=1109040302;

END;
语句，为什么呢，十万火急，求助！

千问 · 2014-2-18 16:41:11

应该执行fetch之后才会发生异常;
还有你cursor的条件跟update的条件是一样的，这样有意义吗?