how to run and compile procedures with cursors

asingh18in@gmail.com wrote:

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

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany