Mysql中的游标语法

Question

我完全按照这里的示例进行操作。但是我到处都遇到语法错误。除了我的代码很糟糕之外，语法错误在哪里？

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;

Answer 1

你如何执行这个块？它应该位于存储过程/函数的主体内（或者可能是触发器）。例如，

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

Answer 2

您应该在过程之前和之后添加分隔符

DELIMITER $

//procedure 

DELIMITER ;