I am not able to create following store procedure.
-
CREATE PROCEDURE DBSAMBA.InsertDeleteBatch(OUT norows INT )
-
RESULT SETS 1
-
LANGUAGE SQL
-
BEGIN part1
-
DECLARE TOTAL_LEFT INT DEFAULT 0;
-
SELECT COUNT(*)INTO TOTAL_LEFT FROM DBSAMBA.REPORTEDTRANSACTION_S;
-
WHILE (TOTAL_LEFT > 0)
-
DO
-
BEGIN part2
-
-
INSERT INTO DBSAMBA.REPORTEDTRANSACTION_D(
-
TRANSACTION_REFERENCE,
-
VERSION,
-
CDM
-
)(
-
SELECT TRANSACTION_REFERENCE,
-
VERSION,
-
CDM
-
FROM DBSAMBA.REPORTEDTRANSACTION_S
-
FETCH FIRST 1000 ROWS ONLY);
-
-
BEGIN part3
-
DECLARE Trans_Ref VARCHAR(20);
-
DECLARE EXIT HANDLER FOR NOT FOUND SET norows = 1000;
-
DECLARE c1 CURSOR
-
FOR
-
SELECT TRANSACTION_REFERENCE
-
FROM DBSAMBA.REPORTEDTRANSACTION_S
-
FETCH FIRST 1000 ROWS ONLY;
-
-
-
-
SET norows=0;
-
OPEN c1;
-
-
while norows <1000
-
FETCH c1 INTO Trans_Ref;
-
DELETE
-
FROM DBSAMBA.REPORTEDTRANSACTION_S
-
WHERE TRANSACTION_REFERENCE =Trans_Ref;
-
-
END WHILE;
-
close c1;
-
END part3;
-
-
COMMIT;
-
SELECT COUNT(*)INTO TOTAL_LEFT
-
FROM DBSAMBA.REPORTEDTRANSACTION_S;
-
END part2;
-
END WHILE;
-
END part1;
-
I get following error :-