数据库 | Mysql - [自定义函数 & 存储过程]

§1 函数

信任自定义函数

show variables like '%log_bin_trust_function_creators%';

此变量为 OFF 时，创建修改函数会报错

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

通过下面语句设置

set global log_bin_trust_function_creators=1;

创建自定义函数
创建格式如下

DELIMITER &&
CREATE FUNCTION func_name(var_name var_type) RETURNS return_type
BEGIN
// function
END
$$

示例(随机字符串)

DELIMITER &&
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN

DECLARE charstr VARCHAR(100) DEFAULT 'abcefghigklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE resulestr VARCHAR(100) DEFAULT '';
DECLARE i INT DEFAULT 0;

WHILE i < n DO
	set returnstr = CONCAT(returnstr, SUBSTRING(charstr, FLOOR(1 + RAND() * 62), 1));
	set i = i + 1;
END WHILE:
RETURN returnstr;
END
$$

§2 存储过程

创建自定义函数
创建格式如下

DELIMITER &&
CREATE PROCEDURE name(var_name var_type)
BEGIN
// PROCEDURE 
END
$$

示例

DELIMITER &&
CREATE PROCEDURE add_data (n INT)
BEGIN

DECLARE i INT DEFAULT 0;
set autocommit = 0;

REPEAT
	set i = i + 1;
	insert into .......
	until i = n;
END REPEAT:

COMMIT;

END
$$

调用自定义函数

CALL PROCEDURE_NAME(params...);