how to run and compile procedures with cursors

[a***@gmail.com]

how to run this procedure and compile it

PROCEDURE
CREATE PROCEDURE CANALLIST()
LANGUAGE SQL
BEGIN
DECLARE SUM1, SUM2, SUM3 DOUBLE;
SUM1=0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE c1 CURSOR FOR VAR_CANAL_ID FROM M_CANAL WHERE
VAR_DIVISION_ID=? AND VAR_CANAL_TYPE IN ('br', 'dy');
DECLARE CONTINUE HANDLER FOR not_found (2) SET at_end = 1;
OPEN c1;
ftch_loop1: LOOP
FETCH c1 INTO :V_CANAL_ID;
SUM2=0;
(1) IF at_end = 1 THEN
SUM1=SUM1+(SUM2-SUM3);
LEAVE ftch_loop1; (3) ELSEIF DECLARE c2 CURSOR FOR VAR_CANAL_ID FROM
M_CANAL WHERE
VAR_PARENT_CANAL_ID=V_CANAL_ID;
DECLARE CONTINUE HANDLER FOR not_found (2) SET at_end1 = 1;
OPEN c2;
ftch_loop2: LOOP
SUM3=0;
SUM2=SUM2 + SUM( S.DBL_DISCHARGE * ( S. DTM_TIME_TO - S.DTM_TIME_FORM
) * 86400 ) AS VOL_WATER_CONVEY
FROM DB2ADMIN.T_IRRIG_WATER_SUPPLIED S
WHERE S.VAR_CANAL_ID=V_CANAL_ID AND C.VAR_DIVISION_ID=S.VAR_DIVISION_ ID
GROUP BY S.VAR_DIVISION_ID, S.VAR_SEASON_ID;

FETCH c2 INTO :V1_CANAL_ID; (1) IF at_end = 1 THEN LEAVE ftch_loop2; (3) ELSEIF
SUM3=SUM3 + SUM( S.DBL_DISCHARGE * ( S. DTM_TIME_TO - S.DTM_TIME_FORM
) * 86400 ) AS VOL_WATER_CONVEY
FROM DB2ADMIN.T_IRRIG_WATER_SUPPLIED S
WHERE S.VAR_CANAL_ID=V1_CANAL_ID AND C.VAR_DIVISION_ID=S.VAR_DIVISION_ ID
GROUP BY S.VAR_DIVISION_ID, S.VAR_SEASON_ID;

END IF; END LOOP; CLOSE c1;
END IF; END LOOP; CLOSE c2;
THEN INSERT INTO TEMP1 SET VOL_CONVEY= SUM1 WHERE SEASON=SEA AND
DIVISION=VAR_DIVISION_ID;
END

[Knut Stolze]

Post by a***@gmail.com
how to run this procedure and compile it
PROCEDURE
CREATE PROCEDURE CANALLIST()
LANGUAGE SQL
BEGIN
DECLARE SUM1, SUM2, SUM3 DOUBLE;
SUM1=0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE c1 CURSOR FOR VAR_CANAL_ID FROM M_CANAL WHERE
VAR_DIVISION_ID=? AND VAR_CANAL_TYPE IN ('br', 'dy');
DECLARE CONTINUE HANDLER FOR not_found (2) SET at_end = 1;
OPEN c1;
ftch_loop1: LOOP
FETCH c1 INTO :V_CANAL_ID;
SUM2=0;
(1) IF at_end = 1 THEN
SUM1=SUM1+(SUM2-SUM3);
LEAVE ftch_loop1; (3) ELSEIF DECLARE c2 CURSOR FOR VAR_CANAL_ID FROM
M_CANAL WHERE
VAR_PARENT_CANAL_ID=V_CANAL_ID;
DECLARE CONTINUE HANDLER FOR not_found (2) SET at_end1 = 1;
OPEN c2;
ftch_loop2: LOOP
SUM3=0;
SUM2=SUM2 + SUM( S.DBL_DISCHARGE * ( S. DTM_TIME_TO - S.DTM_TIME_FORM
) * 86400 ) AS VOL_WATER_CONVEY
FROM DB2ADMIN.T_IRRIG_WATER_SUPPLIED S
WHERE S.VAR_CANAL_ID=V_CANAL_ID AND C.VAR_DIVISION_ID=S.VAR_DIVISION_ ID
GROUP BY S.VAR_DIVISION_ID, S.VAR_SEASON_ID;
FETCH c2 INTO :V1_CANAL_ID; (1) IF at_end = 1 THEN LEAVE ftch_loop2; (3)
ELSEIF SUM3=SUM3 + SUM( S.DBL_DISCHARGE * ( S. DTM_TIME_TO -
S.DTM_TIME_FORM
) * 86400 ) AS VOL_WATER_CONVEY
FROM DB2ADMIN.T_IRRIG_WATER_SUPPLIED S
WHERE S.VAR_CANAL_ID=V1_CANAL_ID AND C.VAR_DIVISION_ID=S.VAR_DIVISION_ ID
GROUP BY S.VAR_DIVISION_ID, S.VAR_SEASON_ID;
END IF; END LOOP; CLOSE c1;
END IF; END LOOP; CLOSE c2;
THEN INSERT INTO TEMP1 SET VOL_CONVEY= SUM1 WHERE SEASON=SEA AND
DIVISION=VAR_DIVISION_ID;
END

I haven't looked at all the code because it is very badly formatted and
syntactically incorrect. (What are all the "(1)" things doing in the code?

What are your problems (exact error message) when you try to create the
procedure? A procedure is executed using the CALL statement. Does that
work or do you face some problem there?

Also, you may want to use FOR loops and get rid of the explicit end-of-table
checking. It should make the code simpler and easier to understand.