cursor syntax in Mysql

Question

I followed the example here to a T. But I'm getting syntax errors all over the place. Aside from the fact my code sucks, where is the syntax error?

set @deviation = 30;
set @average = 200000;

DECLARE cur1 CURSOR FOR SELECT distinct subindustry FROM     referraldb.report_referral_db_viz_qa;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
open cur1;

read_loop: LOOP
fetch cur1 into sub;
if done then
    leave read_loop;
end if;

select @d := max(date) from referraldb.report_referral_db_viz_qa;
select a.subindustry, a.report_time_id, a.dimension_id, a.brand_id, a.referral_source, a.date, a.pre, a.current_test, a.create_dt
    from 
        (select distinct cur.subindustry, cur.report_time_id, cur.dimension_id, cur.brand_id, cur.referral_source, cur.date, cur.pre, cur.current_test, cur.create_dt
        from 
            referraldb.report_referral_db_viz_qa cur
            inner join referraldb.report_referral_db_viz_qa prv 
                on cur.report_time_id = prv.report_time_id 
                    and cur.dimension_id = prv.dimension_id 
                    and cur.brand_id = prv.brand_id 
                    and cur.referral_source = prv.referral_source
                    and cur.date = date_add(LAST_DAY(DATE_SUB(@d, INTERVAL 1 month)), interval 1 day) 
                    and prv.date = date_add(LAST_DAY(DATE_SUB(@d, INTERVAL 2 month)), interval 1 day)
            inner join referraldb.dim_all_dimensions dims
                on dims.dimension_id = prv.dimension_id
            inner join referraldb.dim_brand brand  
                on brand.brand_id = prv.brand_id
        where 
            dims.lag = 'immediate' 
            and dims.measure_type = 'visits'
            and prv.subindustry = sub
            and prv.report_time_id = 1 
            and abs((((cur.current_test - prv.current_test)/cur.current_test) * 100)) >= @deviation) a
inner join
    (select  distinct fact.subindustry, fact.report_time_id, fact.dimension_id, fact.brand_id, fact.referral_source, fact.date, fact.pre, fact.current_test, fact.create_dt
        from 
            referraldb.report_referral_db_viz_qa fact inner join
            referraldb.dim_brand brand
                on brand.brand_id = fact.brand_id inner join
            referraldb.dim_report_time t
                on t.report_time_id = fact.report_time_id inner join
            referraldb.dim_all_dimensions dims
                 on dims.dimension_id = fact.dimension_id         
        where dims.lag = 'Immediate' and dims.measure_type = 'Visits' 
        and fact.subindustry = sub
        and fact.report_time_id = 1 and fact.date > DATE_SUB(@d, INTERVAL 13 month)
        group by fact.referral_source, brand.Industry, fact.Subindustry,  brand.Brand, dims.Activity, dims.Detail 
        having avg(current_test) > @average ) b
    on a.subindustry = b.subindustry and a.report_time_id = b.report_time_id and a.dimension_id = b.dimension_id and a.brand_id = b.brand_id and a.referral_source= b.referral_source and a.date = b.date and a.pre = b.pre and a.current_test = b.current_test and a.create_dt = b.create_dt
end loop;

close cur1;

Where exactly is error? What does MySQL say?

Vladislav Rastrusny
– Vladislav Rastrusny

2011-03-21 14:17:11 +00:00
Commented Mar 21, 2011 at 14:17 — Vladislav Rastrusny
– Vladislav Rastrusny, Commented Mar 21, 2011 at 14:17

a1ex07 · Accepted Answer · 2011-03-21 14:13:02Z

1

How do you execute this block? It should be inside a body of Stored procedure/function (or maybe a trigger). For example,

DELIMITER $$
CREATE PROCEDURE MyProc (//list of parameters)
BEGIN
   // your code goes here
END $$
DELIMITER ;

answered Mar 21, 2011 at 14:13

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ramy Over a year ago

I'm coming from a ms sql-server shop. In sql-server, you don't have to create a stored procedure to use cursors. Is that not true in MySQL?

a1ex07 Over a year ago

yes, that's right. SQL Server lets you execute commands directly, Mysql doesn't.

a1ex07 Over a year ago

Also, a side notice - in Mysql @deviation creates a session variable, you might want to use DECLARE deviation INT DEFAULT 30 instead.

Ramy Over a year ago

thanks for the tip. what an odd word to use to create a session variable.

Matas Vaitkevicius · Accepted Answer · 2014-07-24 11:16:06Z

0

You should add delimiter before and after procedure

DELIMITER $$

//procedure 

DELIMITER ;

edited Jul 24, 2014 at 11:16

Matas Vaitkevicius

61.9k37 gold badges253 silver badges278 bronze badges

answered Jul 24, 2014 at 10:58

user2586714

1491 gold badge1 silver badge7 bronze badges

Collectives™ on Stack Overflow

cursor syntax in Mysql

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related