mysql自定义函数用if报1064_MySQL自定义函数出现的问题

DROPFUNCTIONIFEXISTSmyFunction;delimiter$$CREATEFUNCTIONmyFunction()RETURNSVARCHAR(4)BEGINDECLAREfirst_name_listCHAR(32)DEFAULT'赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨';DECLAREl...

DROP FUNCTION IF EXISTS myFunction;

delimiter $$

CREATE FUNCTION myFunction() RETURNS VARCHAR(4)

BEGIN

DECLARE first_name_list CHAR(32) DEFAULT '赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨';

DECLARE last_name_list CHAR(20) DEFAULT '甲乙丙丁戊己庚辛壬癸';

DECLARE full_name CHAR(2);

SET full_name =

CONCAT(SUBSTRING(first_name_list, FLOOR(RAND()*16+1), 1),

SUBSTRING(last_name_list, FLOOR(RAND()*10+1), 1));

RETURN full_name;

END $$

delimiter ;

DROP FUNCTION IF EXISTS dotest;

delimiter $$

CREATE FUNCTION dotest()

BEGIN

DECLARE i INT;

SET i = 10;

WHILE i <= 10 DO

SELECT myFunction();

SET i = i + 1;

END WHILE;

END $$

delimiter ;

SELECT dotest();

代码不能格式化, 大侠们将就着看一下吧...

第一个函数执行没问题, 第二个报错如下:

[SQL]

DROP FUNCTION IF EXISTS dotest;

受影响的行: 0

时间: 0.000s

[SQL]

CREATE FUNCTION dotest()

BEGIN

DECLARE i INT;

SET i = 10;

WHILE i <= 10 DO

SELECT myFunction();

SET i = i + 1;

END WHILE;

END ;

[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 'BEGIN

DECLARE i INT;

SET i = 10;

WHILE i <= 10 DO

SELECT myFunction();

' at line 2

展开