函数代码如下:
问题:
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;
存储过程的写法需要按照格式要求,例如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;
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。