第一次寫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
本來的函數：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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上邊執行不起來，在多次嘗試之后將代碼修改為以下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
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不能創建函數解決方法分享的全部內容，希望對大家有所幫助。有什么問題可以隨時留言，小編會及時回復大家的。感謝朋友們對本站的支持！
原文鏈接：http://blog.csdn.net/wojiaohuangyu/article/details/54629414
