mysql用户自定义函数实例与部分问题解决方法_MySQL

mysql> show variables like '%func%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| log_bin_trust_function_creators | ON |
+-----------------------------------------+-------+
1 row in set (0.02 sec)

mysql> set global log_bin_trust_function_creators=1;
use app02
delimiter //
mysql>DROP FUNCTION IF EXISTS `testhanshu`;

CREATE FUNCTION `testhanshu`(`tustate` int)
RETURNS varchar(2000)
BEGIN

declare oneAddr varchar(200) default '';
declare allAddr varchar(2000) default '';
DECLARE done INT DEFAULT FALSE;

declare curl CURSOR for select utruename from tsys_user where ustate = tustate;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
open curl;

  REPEAT
FETCH curl INTO oneAddr;
IF NOT done THEN
set oneAddr = CONCAT(oneAddr, ';');
set allAddr = CONCAT(allAddr, oneAddr);
END IF;
UNTIL done END REPEAT;
close curl;
RETURN allAddr;
END;
mysql>select testhanshu(1);