Question

一般在sql中,可用于逐行提取游标中数据的语句是什么

Answer 1

给你一个过程来参考了

--/
CREATE PROCEDURE UPDATE_SERVICE_CODE_TMP
(
OUT "V_RET_STR" VARCHAR(400)
)
BEGIN
DECLARE CUR_PLYINFO_FOUND INTEGER DEFAULT 0;
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE VPLYNO VARCHAR(30);
DECLARE VC_SERVICE_CODE_NEW VARCHAR(100);
DECLARE VC_SERVICE_CODE_TYPE VARCHAR(100);
DECLARE V_COUNT INTEGER DEFAULT 0;
DECLARE CUR_PLY_INFO CURSOR WITH HOLD FOR SELECT C_PLY_NO,C_SERVICE_CODE_NEW FROM T_TMP_SERVICE_CODE_0616 WITH UR;
SET V_COUNT =0;
OPEN CUR_PLY_INFO;
LOOP_LABEL_BASE:
LOOP
BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND SET CUR_PLYINFO_FOUND = SQLCODE;
SET CUR_PLYINFO_FOUND = 0;
SET VPLYNO ='---';
SET VC_SERVICE_CODE_NEW ='---';
SET VC_SERVICE_CODE_TYPE='---';
FETCH FROM CUR_PLY_INFO INTO VPLYNO,VC_SERVICE_CODE_NEW;
END;
IF CUR_PLYINFO_FOUND = 100 THEN
LEAVE LOOP_LABEL_BASE;
END IF;
SELECT C_CHA_TYPE
INTO VC_SERVICE_CODE_TYPE
FROM T_SERVICE_CODE
WHERE C_SERVICE_NO=VC_SERVICE_CODE_NEW WITH UR;
SET VC_SERVICE_CODE_TYPE=COALESCE(VC_SERVICE_CODE_TYPE,'---');

UPDATE RPT_PLYEDR
SET C_SERVICE_CODE=VC_SERVICE_CODE_NEW,C_SERVICE_CODE_TYPE=VC_SERVICE_CODE_TYPE
WHERE C_PLY_NO=VPLYNO;
SET V_COUNT =V_COUNT+1;
IF(V_COUNT=5000) THEN
COMMIT;
SET V_COUNT=0;
END IF;
END LOOP
LOOP_LABEL_BASE;
COMMIT;
CLOSE CUR_PLY_INFO;
COMMIT;
SET V_RET_STR ='SUCCESS';
END
/
commit;