欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

navicat不能创建函数解决方法分享

程序员文章站 2022-06-24 13:42:37
第一次写mysql function,一直报错, err] 1064 - you have an error in your sql syntax; check th...

第一次写mysql function,一直报错,

err] 1064 - you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near '`company_id` int) returns varchar(20) charset utf8

begin

本来的函数:

create definer=`33323`@`%` function `createsalecode`(`benginstr` varchar,`company_id` int) returns varchar(20) charset utf8 
begin 
  declare nearnum varchar(20);  
 declare nowdatepre varchar(20); 
 declare numout varchar(20);  
 select a.sale_code into nearnum from d_sale a where a.company_id = company_id order by a.sale_id desc limit 1; 
 select concat(extract(year_month from now()),lpad(extract(day from now()), 2, 0)) into nowdatepre; 
 if locate(nowdatepre,nearnum)>0  
  then  
    set numout = nearnum +1;   
  else  
    set numout = concat(beginstr,nowdatepre,'00001');  
  end if; 
 return numout; 
end 

这段函数在navicat上边执行不起来,在多次尝试之后将代码修改为以下:

delimiter $$ 
create definer=`12212`@`%` function createsalecode(benginstr varchar(20),company_id int(11) ) returns varchar(20) charset utf8 
begin 
  declare nearnum varchar(20);  
 declare nowdatepre varchar(20); 
 declare numout varchar(20);  
 select a.sale_code into nearnum from d_sale a where a.company_id = company_id order by a.sale_id desc limit 1; 
 select concat(extract(year_month from now()),lpad(extract(day from now()), 2, 0)) into nowdatepre; 
 if locate(nowdatepre,nearnum)>0  
  then  
    set numout = nearnum +1;   
  else  
    set numout = concat(beginstr,nowdatepre,'00001');  
  end if; 
 return numout; 
end$$ 
delimiter ; 

问题解决。

默认情况下,delimiter是分号;。

总结

以上就是本文关于navicat不能创建函数解决方法分享的全部内容,希望对大家有所帮助。感兴趣的朋友可以继续参阅本站:简述redis和mysql的区别mysql子查询和嵌套查询优化实例解析几个比较重要的mysql变量等,有什么问题可以随时留言,小编会及时回复大家的。感谢朋友们对本站的支持!