MySQL存储函数

Posted on | In | | Visitors:
Words count in article: 1.4k

【转】MySQL存储函数

存储过程和函数区别

1）一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。存储过程，功能强大，可以执行包括修改表等一系列数据库操作；用户定义函数不能用于执行一组修改全局数据库状态的操作。

2）对于存储过程来说可以返回参数，如记录集，而函数只能返回值或者表对象。函数只能返回一个变量；而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型，而函数只能有IN类~~存储过程声明时不需要返回类型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的RETURN语句。

3）存储过程，可以使用非确定函数，不允许在用户定义函数主体中内置非确定函数。

4）存储过程一般是作为一个独立的部分来执行（ EXECUTE 语句执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程，而可以使用函数。

转自这里

MySQL存储函数（自定义函数），函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。

1、创建存储函数

在MySQL中，创建存储函数使用CREATE FUNCTION关键字，其基本形式如下：

1
2
3
4
5
6
CREATE FUNCTION func_name ([param_name type[,...]])
RETURNS type
[characteristic ...] 
BEGIN
	routine_body
END;

参数说明：

（1）func_name ：存储函数的名称。

（2）param_name type：可选项，指定存储函数的参数。type参数用于指定存储函数的参数类型，该类型可以是MySQL数据库中所有支持的类型。

（3）RETURNS type：指定返回值的类型。

（4）characteristic：可选项，指定存储函数的特性。

（5）routine_body：SQL代码内容。

2、调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法基本相同。用户自定义的存储函数与MySQL内部函数性质相同。区别在于，存储函数是用户自定义的。而内部函数由MySQL自带。其语法结构如下：

1
SELECT func_name([parameter[,…]]);

示例：创建存储函数，实现根据用户编号，获取用户姓名功能。

（1）先创建tb_user（用户信息表），并添加数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 创建用户信息表
CREATE TABLE IF NOT EXISTS tb_user
(
	id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',
	name VARCHAR(50) NOT NULL COMMENT '用户姓名'
) COMMENT = '用户信息表';
-- 添加数据
INSERT INTO tb_user(name) VALUES('pan_junbiao的博客');
INSERT INTO tb_user(name) VALUES('KevinPan');
INSERT INTO tb_user(name) VALUES('pan_junbiao');
INSERT INTO tb_user(name) VALUES('阿标');
INSERT INTO tb_user(name) VALUES('panjunbiao');
INSERT INTO tb_user(name) VALUES('pan_junbiao的CSDN博客');
INSERT INTO tb_user(name) VALUES('https://blog.csdn.net/pan_junbiao');

查询数据结果：

img

（2）创建存储函数

1
2
3
4
5
6
7
8
9
10
-- 创建存储函数
DROP FUNCTION IF EXISTS func_user;
CREATE FUNCTION func_user(in_id INT)
RETURNS VARCHAR(50)
BEGIN
	DECLARE out_name VARCHAR(50);
	SELECT name INTO out_name FROM tb_user
	WHERE id = in_id;
	RETURN out_name;
END;

（3）调用存储函数

1
2
3
4
5
6
7
8
-- 调用存储函数
SELECT func_user(1);
SELECT func_user(2);
SELECT func_user(3);
SELECT func_user(4);
SELECT func_user(5);
SELECT func_user(6);
SELECT func_user(7);

执行结果：

img

3、修改存储函数

MySQL中，通过ALTER FUNCTION 语句来修改存储函数，其语法格式如下：

1
2
3
4
5
6
ALTER FUNCTION func_name [characteristic ...]
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

上面这个语法结构是MySQL官方给出的，修改的内容可以包含SQL语句也可以不包含，既可以是读数据的SQL也可以是修改数据的SQL还有权限。此外在修改function的时候还需要注意你不能使用这个语句来修改函数的参数以及函数体，如果你想改变这些的话你就需要删除掉这个函数然后重新创建。

4、删除存储函数

MySQL中使用DROP FUNCTION语句来删除存储函数。

示例：删除存储函数。

1
DROP FUNCTION IF EXISTS func_user;

其它补充：

如果你在创建存储函数时提示以下错误：

[Err] 1418 - 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)

这是我们开启了bin-log, 我们就必须指定我们的函数是否是
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句

其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。

解决方法：

解决办法也有两种， 第一种是在创建子程序(存储过程、函数、触发器)时，声明为DETERMINISTIC或NO SQL与READS SQL DATA中的一个， 例如: CREATE DEFINER = CURRENT_USER PROCEDURE NewProc() DETERMINISTIC BEGIN #Routine body goes here… END;;

第二种是信任子程序的创建者，禁止创建、修改子程序时对SUPER权限的要求，设置log_bin_trust_routine_creators全局系统变量为1。

设置方法有三种：

（1）在客户端上执行 SET GLOBAL log_bin_trust_function_creators = 1。

（2）MySQL启动时，加上—log-bin-trust-function-creators选贤，参数设置为1。

（3）在MySQL配置文件my.ini或my.cnf中的[mysqld]段上加log-bin-trust-function-creators=1。