MySQL Forums
Forum List  »  Stored Procedures

how to declare cursor in middle of stored procedure
Posted by: BHANU PRATAP MUNAGALA
Date: October 19, 2016 03:36AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
how to declare cursor in middle of stored procedure
6896
October 19, 2016 03:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.