how to declare cursor in middle of stored procedure
Hi
I am trying to DECLARE cursor based in middle of the stored procedure and getting the below error.
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE fc
st_cur CURSOR FOR SELECT t,used_size from fcst;
DECLARE CONTINUE HANDL' at line 91
Enclosing is the PL/SQL Code.
CREATE PROCEDURE get_avg_lm_data3(IN p_start_date date, IN p_end_date date, IN p_db_name VARCHAR(50))
BEGIN
DECLARE v_sw INT;
DECLARE v_ew INT;
DECLARE v_sy INT;
DECLARE v_ey INT;
DECLARE v_wcnt INT;
DECLARE v_rcnt INT;
DECLARE v_dbcnt INT;
DECLARE i1 INT;
DECLARE i2 INT;
DECLARE i3 INT;
DECLARE str1 varchar(255);
DECLARE str2 varchar(255);
DECLARE str3 decimal(10,5);
DECLARE v_mxwk INT;
DECLARE val_wno INT;
DECLARE val_usg DECIMAL(10,5);
DECLARE n INT;
DECLARE slope DECIMAL(10,5);
DECLARE intercept DECIMAL(10,5);
DECLARE meanT DECIMAL(10,5);
DECLARE meanY DECIMAL(10,5);
DECLARE st22 DECIMAL(10,5);
DECLARE st2 DECIMAL(10,5);
DECLARE st DECIMAL(10,5);
DECLARE sY DECIMAL(10,5);
DECLARE stY DECIMAL(10,5);
DECLARE Y DECIMAL (10,5);
DECLARE loop_cntr1 INT;
DECLARE nmax INT;
DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
SELECT WEEK(p_start_date) INTO v_sw from dual;
SELECT WEEK(p_end_date) INTO v_ew from dual;
SELECT year(p_start_date) INTO v_sy from dual;
SELECT year(p_end_date) INTO v_ey from dual;
SELECT MAX(AVGWEEK) INTO v_mxwk FROM WEEKLY_DB_SIZE;
SELECT count(*) into v_dbcnt from weekly_db_size where database_name = p_db_name;
if(v_dbcnt = 0) then
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'ERROR !!! NO DATABASE FOUND BY NAME';
end if;
SELECT COMPUTE_WEEKS(p_start_date,p_end_date) into v_wcnt from dual;
if(v_wcnt < 0) then
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'ERROR !!! WRONG DATES CHOOSEN AS INPUTS';
end if;
SELECT COUNT(*) INTO v_rcnt from weekly_db_size where avgweek between v_sw and v_ew and database_name = p_db_name and avgyear between v_sy and v_ey;
if(v_rcnt = 0) then
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT ='ERROR !!! NO DATA PRESENT IN THE SELECTED TIME INTERVAL';
end if;
DROP TABLE FCST;
CREATE TABLE FCST(t int,wno int, used_size decimal(10,5),tY decimal(10,5), t2 int(11),projected varchar(5));
SET i1 = 1;
SET i2 = v_sw;
LOOP_LABEL : LOOP
IF i1 > v_wcnt THEN
LEAVE LOOP_LABEL;
END IF;
SET str3 = (SELECT IFNULL(sum(USED_GB),0) FROM WEEKLY_DB_SIZE WHERE AVGWEEK = i2);
INSERT INTO FCST VALUES(i1,i2,str3,0,0,'N');
SET i1 = i1 + 1;
SET i2 = i2 + 1;
IF i2 = v_mxwk THEN
SET i1 = v_wcnt;
END IF;
ITERATE LOOP_LABEL;
END LOOP;
SELECT * FROM FCST;
DECLARE fcst_cur CURSOR FOR SELECT t,used_size from fcst;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;
OPEN fcst_cur;
SELECT FOUND_ROWS() INTO n;
the_loop: LOOP
FETCH fcst_cur INTO val_wno, val_usg;
IF no_more_rows THEN
CLOSE fcst_cur;
LEAVE the_loop;
END IF;
UPDATE fcst SET tY = (val_wno * val_usg) , t2 = (power(val_wno,2)) WHERE t = val_wno;
SELECT val_wno, val_usg;
SET loop_cntr = loop_cntr + 1;
END LOOP the_loop;
select num_rows, loop_cntr;
select * from fcst1;
-- Compute varailbles required Slope and Intercept calcuclation
SELECT SUM(tY) INTO stY from fcst1;
SELECT SUM(used_size) INTO sY from fcst1;
SELECT SUM(t) INTO st from fcst1;
SELECT SUM(t2) INTO st2 from fcst1;
SELECT POWER(SUM(t),2) INTO st22 from fcst1;
SELECT AVG(t) INTO meanT from fcst1;
SELECT AVG(used_size) into meanY from fcst1;
-- Compute Slope
SELECT (n*sty - st*sY)/(n*st2 - st22) INTO slope;
-- Compute Intercept
SELECT (meanY - slope*meanT) INTO intercept;
SELECT slope,intercept;
-- Computes forecast or predictes next 4 weeks future values
SET loop_cntr1 = loop_cntr + 1;
SET i3 = i2 + 1;
SELECT (n+4) INTO nmax;
SELECT nmax;
fcst_loop : LOOP
IF loop_cntr1 > nmax THEN
LEAVE fcst_loop;
END IF;
SELECT ( intercept + (slope*loop_cntr1)) INTO Y;
INSERT INTO FCST1 VALUES (loop_cntr1,i3,Y,0,0,'Y');
SET loop_cntr1 = loop_cntr1 + 1;
SET i3 = i3 + 1;
ITERATE fcst_loop;
END LOOP;
SELECT * FROM FCST;
END //