mysql函数问题

热爱生活~~~ 2018-05-28 05:53:11
传入   good_business = '1,2,3' 转化为 'a,b,c'
DELIMITER //
CREATE FUNCTION getDictListNameFor(counts INT ,good_business VARCHAR(50))
RETURNS VARCHAR(50)
BEGIN
DECLARE jieguo VARCHAR(50);
DECLARE i INT DEFAULT 1;
SET jieguo='';
WHILE i<=counts;
SELECT CONCAT_WS(',',jieguo,dictlist_name00) FROM basis_dict_list_info WHERE dictlist_code00=SUBSTRING_INDEX(SUBSTRING_INDEX(good_business,',',SET i),',',-1); //转化为 'a,b,c'
SET i=i+1;
END WHILE;
RETURN(jieguo);
END//
DELIMITER ;


报错如下

查询:CREATE FUNCTION getDictListNameFor(counts int ,good_business VARCHAR(50)) RETURNS VARCHAR(50) BEGIN declare jieguo VARCHAR(50); ...

错误代码: 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 ';
SELECT concat_ws(',',jieguo,dictlist_name00)
FROM basis_dict_list_inf' at line 7

执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0.001 sec


大佬们,帮我看看这个哪里有错?要怎么改?或者有更好地方法都可以发出来,小弟拜谢了!
...全文
1109 2 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
2 条回复
切换为时间正序
请发表友善的回复…
发表回复
crynono 2018-05-28
  • 打赏
  • 举报
回复
1 是jieguo 这个变量的值好像一直没有写入。2 是在functon中,select是不能返回结果集的,会报错 Statements that return a result set can be used within a stored procedure but not within a stored function. This prohibition includes SELECT statements that do not have an INTO var_list clause and other statements such as SHOW, EXPLAIN, and CHECK TABLE.
ACMAIN_CHM 2018-05-28
  • 打赏
  • 举报
回复
引用
12.8.6.7. WHILE Statement [begin_label:] WHILE search_condition DO statement_list END WHILE [end_label] The statement list within a WHILE statement is repeated as long as the search_condition is true. statement_list consists of one or more statements. A WHILE statement can be labeled. end_label cannot be given unless begin_label also is present. If both are present, they must be the same. Example: CREATE PROCEDURE dowhile() BEGIN DECLARE v1 INT DEFAULT 5; WHILE v1 > 0 DO ... SET v1 = v1 - 1; END WHILE; END
语法上的问题建议先参考一下免费的官方帮助手册。

56,914

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧