Navicat cannot create function solution sharing

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

Recommend

Mysql implements three functions for field splicing

When exporting data to operations, it is inevitab...

js to implement web calculator

How to make a simple web calculator using HTML, C...

Vue project @change multiple parameters to pass multiple events

First, there is only one change event. changeleve...

mysql IS NULL using index case explanation

Introduction The use of is null, is not null, and...

Html tips to make your code semantic

Html semantics seems to be a commonplace issue. G...

Let’s talk in detail about how JavaScript affects DOM tree construction

Table of contents Document Object Model (DOM) DOM...

MySQL 8.0.23 installation and configuration method graphic tutorial under win10

This article shares the installation and configur...

Detailed explanation based on event bubbling, event capture and event delegation

Event bubbling, event capturing, and event delega...

MySQL 8.0.15 version installation tutorial connect to Navicat.list

The pitfalls 1. Many tutorials on the Internet wr...

Vue Beginner's Guide: Environment Building and Getting Started

Table of contents Initial Vue Building a Vue deve...

Scary Halloween Linux Commands

Even though it's not Halloween, it's wort...

Detailed explanation of Mysql function call optimization

Table of contents Function call optimization Func...

Page Refactoring Skills - Javascript, CSS

About JS, CSS CSS: Stylesheet at the top Avoid CS...