mysql 存储过程不执行

Question

CREATE PROCEDURE `proc_table`(OUT rtn VARCHAR(10)) 
    NOT DETERMINISTIC 
    SQL SECURITY DEFINER 
    COMMENT '' 
begin 
        declare  temp_analyse_sort varchar(10);         declare  analynum int;         -- 声明游标 
        declare cur cursor for select a.analyse_sort from jc_analyse as a,jc_dictionary as b where a.analyse_code = b.dictionary_code group by a.analyse_sort; 
        
        -- 打开游标 
        OPEN cur;         -- 读取一行数据到变量 
        fetch cur into temp_analyse_sort;         -- 这个就是判断是否游标已经到达了最后 
        while temp_analyse_sort is not null do         select count(distinct b.dictionary_units) into analynum from jc_analyse as a,jc_dictionary as b where a.analyse_code = b.dictionary_code and a.analyse_sort='temp_analyse_sort'; 
        
        select analynum; //输出等于3         但是if (analynum > 0) then  却不成立 
        
        下边的语句也不执行？ 
          insert into jc_table SELECT a.analyse_sort as priAB,'-' as priAC,'-' as priAD,'-' as priAE,'-' as priAF,'-' as priAG,'-' as priAH,'-' as priAI,sum(c.revenue_price_e*c.revenue_price_b)/10000 as priAJ,sum(b.dictionary_price_b*c.revenue_price_b)/10000 as priAK,sum(b.dictionary_price_c*c.revenue_price_b)/10000 as priAL,sum(b.dictionary_price_d*c.revenue_price_b)/10000 as priAM,sum(c.revenue_price_d*revenue_price_b)/10000 as priAN,(sum(c.revenue_price_d*revenue_price_b)/10000)-(sum(c.revenue_price_e*c.revenue_price_b)/10000) as priAO,(sum(c.revenue_price_d*revenue_price_b)/10000)-(sum(b.dictionary_price_b*c.revenue_price_b)/10000) as priAP,(sum(c.revenue_price_d*revenue_price_b)/10000)-(sum(b.dictionary_price_c*c.revenue_price_b)/10000) as priAQ,(sum(c.revenue_price_d*revenue_price_b)/10000)-(sum(b.dictionary_price_d*c.revenue_price_b)/10000) as priAR,((sum(c.revenue_price_e*c.revenue_price_b)/10000)-(sum(c.revenue_price_d*revenue_price_b)/10000))/(sum(c.revenue_price_e*c.revenue_price_b)/10000) as priAS,((sum(b.dictionary_price_b*c.revenue_price_b)/10000)-(sum(c.revenue_price_d*revenue_price_b)/10000))/(sum(b.dictionary_price_b*c.revenue_price_b)/10000) as priAT,((sum(b.dictionary_price_c*c.revenue_price_b)/10000)-(sum(c.revenue_price_d*revenue_price_b)/10000))/(sum(b.dictionary_price_c*c.revenue_price_b)/10000) as priAU,((sum(b.dictionary_price_d*c.revenue_price_b)/10000)-(sum(c.revenue_price_d*revenue_price_b)/10000))/(sum(b.dictionary_price_d*c.revenue_price_b)/10000) as priAV FROM jc_analyse as a ,jc_dictionary as b,jc_revenue as c where a.analyse_sort = 'temp_analyse_sort' and a.analyse_code = b.dictionary_code and a.analyse_code = c.revenue_code group by a.analyse_sort;         end if;         if (analynum = 0) then           insert into jc_table SELECT a.analyse_sort as priAB,b.dictionary_units as priAC,sum(c.revenue_price_b) as priAD,sum(c.revenue_price_e) as priAE,sum(b.dictionary_price_b) as priAF,sum(b.dictionary_price_c) as priAG,sum(b.dictionary_price_d) as priAH,sum(c.revenue_price_d) as priAI,sum(c.revenue_price_e*c.revenue_price_b)/10000 as priAJ,sum(b.dictionary_price_b*c.revenue_price_b)/10000 as priAK,sum(b.dictionary_price_c*c.revenue_price_b)/10000 as priAL,sum(b.dictionary_price_d*c.revenue_price_b)/10000 as priAM,sum(c.revenue_price_d*revenue_price_b)/10000 as priAN,(sum(c.revenue_price_d*revenue_price_b)/10000)-(sum(c.revenue_price_e*c.revenue_price_b)/10000) as priAO,(sum(c.revenue_price_d*revenue_price_b)/10000)-(sum(b.dictionary_price_b*c.revenue_price_b)/10000) as priAP,(sum(c.revenue_price_d*revenue_price_b)/10000)-(sum(b.dictionary_price_c*c.revenue_price_b)/10000) as priAQ,(sum(c.revenue_price_d*revenue_price_b)/10000)-(sum(b.dictionary_price_d*c.revenue_price_b)/10000) as priAR,((sum(c.revenue_price_e*c.revenue_price_b)/10000)-(sum(c.revenue_price_d*revenue_price_b)/10000))/(sum(c.revenue_price_e*c.revenue_price_b)/10000) as priAS,((sum(b.dictionary_price_b*c.revenue_price_b)/10000)-(sum(c.revenue_price_d*revenue_price_b)/10000))/(sum(b.dictionary_price_b*c.revenue_price_b)/10000) as priAT,((sum(b.dictionary_price_c*c.revenue_price_b)/10000)-(sum(c.revenue_price_d*revenue_price_b)/10000))/(sum(b.dictionary_price_c*c.revenue_price_b)/10000) as priAU,((sum(b.dictionary_price_d*c.revenue_price_b)/10000)-(sum(c.revenue_price_d*revenue_price_b)/10000))/(sum(b.dictionary_price_d*c.revenue_price_b)/10000) as priAV FROM jc_analyse as a ,jc_dictionary as b,jc_revenue as c where a.analyse_sort = 'temp_analyse_sort' and a.analyse_code = b.dictionary_code and a.analyse_code = c.revenue_code group by analyse_sort; 
        end if;         -- 读取下一行的数据 
        fetch cur into temp_analyse_sort;         end while;  -- 循环结束 
        
        close cur; -- 关闭游标 
        
        set rtn='1'; 
end;

Answer 1

一眼看上去没有问题啊，建议你能提供你的表create table 语句和测试用数据，别人需要模拟你的故障才能调试排错。

Answer 2

楼主给的分太少。mysql默认只有本地可以创建存储过程。如果你想远程创建存储过程需要修改mysql库user表里面host=%的那条记录有几个默认为N的字段，改为Y就可以了。
需要从启mysql服务器。给分谢谢。