开发者社区> 问答> 正文

mysql 自定义函数一直报错语法有问题,找不哪里错了,求大神帮忙 ?报错

函数代码如下:
问题:
1.保存是报错变量申明有问题(其他函数这样申明变量没出过问题),
图片说明

    2. 当把报错的变量申明删掉之后又会,又会报其他语句错误(这语句单独提出来运行是没问题的)
    ![图片说明](https://img-ask.csdn.net/upload/201811/20/1542678225_736322.png)







    我觉应该可能不是那个语句的问题,第一写sql函数的没有定义函数类型,一直报错某个句子语法有问题,当我定义了类函数类型(之前报错这个句子没有做任何修改)之后就没问题










    CREATE DEFINER=`root`@`localhost` FUNCTION `etccalcute`(`date_start` datetime,`date_end` datetime,car_id varchar(7)) RETURNS double
MODIFIES SQL DATA

BEGIN
declare etc_sum double;
declare dis_sum double;
declare detc double(3,2);
declare date_s datetime;
declare date_e datatime;
declare n int;
declare m int;

truncate table etc_base;

insert into etc_base(date_delivery_required,project,distance_real)  select date_delivery_required,project,distance_real from orders 
 where date_delivery_required between date_start and date_end
 and car_number=car_id order by date_delivery_required asc;  

 select count(distinct precject) from etc_base into n;

if(n=1)
    begin
             select max(id) from etc_base into m;

                 select date_delivery_required from etc_base where id=1 into date_s;
                 select date_delivery_required from etc_base where id=m-1 into date_e;

                select sum(consumpation_amount) from etc
                where consumpation_time between date_s and date_e
                and car=car_id into etc_sum;

                select sum(distance_real) from etc_base
                where id between 1 and m-1 into dis_sum;

                set detc=etc_sum/dis_sum;
                RETURN detc;
    end
else
  BEGIN
        RETURN -1   
 END    

END;

展开
收起
爱吃鱼的程序员 2020-06-22 23:57:24 695 0
1 条回答
写回答
取消 提交回答
  • https://developer.aliyun.com/profile/5yerqm5bn5yqg?spm=a2c6h.12873639.0.0.6eae304abcjaIB

    存储过程的写法需要按照格式要求,例如ifcounts=0THEN
    insertintot_dayreport(relationid,day,mantime1,turnover1,mantime2,turnover2,mantime3,turnover3,insertdate,type,transamt,transamt_count)values(relationid_,day_,0,0.00,0,0.00,0,0.00,cdate,'1',transamt_,czcs_);
    else
    updatet_dayreportsettransamt=transamt_,transamt_count=czcs_whererelationid=relationid_andDATE_FORMAT(insertdate,'%Y-%m-%d')=DATE_FORMAT(cdate,'%Y-%m-%d');
    endif;

    2020-06-22 23:57:43
    赞同 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
One Box: 解读事务与分析一体化数据库 HybridDB for MySQL 立即下载
One Box:解读事务与分析一体化数据库HybridDB for MySQL 立即下载
如何支撑HTAP场景-HybridDB for MySQL系统架构和技术演进 立即下载

相关镜像