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

vitrualBox+ubuntu16.04 install python3.6 latest tutorial and detailed steps

Because I need to use Ubuntu+Python 3.6 version t...

MySQL multi-table join query example explanation

In actual projects, there are relationships betwe...

Detailed explanation of Nginx proxy_redirect usage

Today, I encountered a little problem when I was ...

Docker primary network port mapping configuration

Port Mapping Before the Docker container is start...

How to Fix File System Errors in Linux Using ‘fsck’

Preface The file system is responsible for organi...

CSS hacks \9 and \0 may not work for hacking IE11\IE9\IE8

Every time I design a web page or a form, I am tr...

About uniApp editor WeChat sliding problem

The uniapp applet will have a similar drop-down p...

Detailed explanation of how to use zabbix to monitor oracle database

1. Overview Zabbix is ​​a very powerful and most ...

Axios secondary encapsulation example Demo in the project

1. Why do packaging? Facilitates overall code cal...

Vue component encapsulates sample code for uploading pictures and videos

First download the dependencies: cnpm i -S vue-uu...

Tutorial on installing Ceph distributed storage with yum under Centos7

Table of contents Preface Configure yum source, e...

Tutorial on deploying jdk and tomcat on centos7 without interface

1. Install xshell6 2. Create a server connection ...

VMware Workstation installation Linux (Ubuntu) system

For those who don't know how to install the s...