Navicat cannot create function solution sharing

The first time I wrote a MySQL FUNCTION, I kept getting errors.

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

The original function:

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

This function cannot be executed on Navicat. After several attempts, the code is modified to the following:

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 ;

Problem solved.

By default, the delimiter is a semicolon;.

Summarize

The above is all the content of this article about the solution to the problem that navicat cannot create functions. I hope it will be helpful to everyone. Interested friends can continue to refer to this site: a brief introduction to the difference between Redis and MySQL, analysis of MYSQL subquery and nested query optimization examples, several important MySQL variables, etc. If you have any questions, you can leave a message at any time and the editor will reply to you in time. Thank you friends for supporting this site!

You may also be interested in:

Simple examples of creating stored procedures, triggers and using cursors in Navicat (pictures and text)
Navicat 8 creates a database and creates a user to assign permissions graphic method
Use Navicat 8 to create a database and import data to manage users and permissions [Graphic Method]

<<: VMware Workstation 14 Pro installation Ubuntu 16.04 tutorial

>>: How to clean up Alibaba Cloud MySQL space