理解MySQL存储过程和函数

⼀、概述

⼀提到存储过程可能就会引出另⼀个话题就是存储过程的优缺点，这⾥也不做讨论，⼀般别⼈问我我就这样回答你觉得它好你就⽤它。因为mysql中存储过程和函数的语法⾮常接近所以就放在⼀起，主要区别就是函数必须有返回值（return），并且函数的参数只有IN类型⽽存储过程有IN、OUT、INOUT这三种类型。⼆、语法

创建存储过程和函数语法

CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body

CREATE FUNCTION sp_name ([func_parameter[,...]]) RETURNS type

[characteristic ...] routine_body

proc_parameter:

[ IN | OUT | INOUT ] param_name type

func_parameter: param_name type type:

Any valid MySQL data type

characteristic:

LANGUAGE SQL

| [NOT] DETERMINISTIC

| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'

routine_body:

Valid SQL procedure statement or statements

语法来⾃官⽅⾃带的参考⼿册，characteristic语法块是需要注意的地⽅，先⽤⼀个例⼦来介绍。例⼦：

#创建数据库

DROP DATABASE IF EXISTS Dpro;CREATE DATABASE DproCHARACTER SET utf8;

USE Dpro;

#创建部门表

DROP TABLE IF EXISTS Employee;CREATE TABLE Employee

(id INT NOT NULL PRIMARY KEY COMMENT '主键', name VARCHAR(20) NOT NULL COMMENT '⼈名', depid INT NOT NULL COMMENT '部门id');

#插⼊测试数据

INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100),(2,'王',101),(3,'张',101),(4,'李',102),(5,'郭',103);#创建存储过程

DROP PROCEDURE IF EXISTS Pro_Employee;DELIMITER $$

CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )READS SQL DATA

SQL SECURITY INVOKERBEGIN

SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid;END$$

DELIMITER ;

#执⾏存储过程

CALL Pro_Employee(101,@pcount);SELECT @pcount;

语法解释：

在创建存储过程的时候⼀般都会⽤DELIMITER$$.....END$$ DELIMITER ;放在开头和结束，⽬的就是避免mysql把存储过程内部的\";\"解释成结束符号，最后通过“DELIMITER ;”来告知存储过程结束。主要解释characteristic部分：

LANGUAGE SQL：⽤来说明语句部分是SQL语句，未来可能会⽀持其它类型的语句。

[NOT] DETERMINISTIC：如果程序或线程总是对同样的输⼊参数产⽣同样的结果，则被认为它是“确定的”，否则就是“⾮确定”的。如果既没有给定DETERMINISTIC也没有给定NOT DETERMINISTIC，默认的就是NOT DETERMINISTIC（⾮确定的）CONTAINS SQL：表⽰⼦程序不包含读或写数据的语句。NO SQL：表⽰⼦程序不包含SQL语句。

READS SQL DATA：表⽰⼦程序包含读数据的语句，但不包含写数据的语句。MODIFIES SQL DATA：表⽰⼦程序包含写数据的语句。

SQL SECURITY DEFINER：表⽰执⾏存储过程中的程序是由创建该存储过程的⽤户的权限来执⾏。

SQL SECURITY INVOKER：表⽰执⾏存储过程中的程序是由调⽤该存储过程的⽤户的权限来执⾏。（例如上⾯的存储过程我写的是由调⽤该存储过程的⽤户的权限来执⾏，当前存储过程是⽤来查询Employee表，如果我当前执⾏存储过程的⽤户没有查询Employee表的权限那么就会返回权限不⾜的错误，如果换成DEFINER如果存储过程是由ROOT⽤户创建那么任何⼀个⽤户登⼊调⽤存储过程都可以执⾏，因为执⾏存储过程的权限变成了root）COMMENT 'string'：备注，和创建表的字段备注⼀样。

注意：在编写存储过程和函数时建议明确指定上⾯characteristic部分的状态，特别是存在复制的环境中，如果创建函数不明确指定这些状态会报错，从⼀个⾮复制环境将带函数的数据库迁移到复制环境的机器上如果没有明确指定DETERMINISTIC, NOSQL, or READS SQL DATA该三个状态也会报错。报错⽰例

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

这个报错就是上⾯注意部分说的问题。原来是因为在主从复制的两台MySQL服务器中开启了⼆进制⽇志选项log-bin,slave会从master复制数据，⽽⼀些操作，⽐如function所得的结果在master和slave上可能不同，所以存在潜在的安全隐患。因此，在默认情况下回阻⽌function的创建。解决办法有两种：

1.将log_bin_trust_function_creators参数设置为ON，这样⼀来开启了log-bin的MySQL Server便可以随意创建function。这⾥存在潜在的数据安全问题，除⾮明确的知道创建的function在master和slave上的⾏为完全⼀致。

设置该参数可以⽤动态的⽅式或者指定该参数来启动数据库服务器或者修改配置⽂件后重启服务器。需注意的是，动态设置的⽅式会在服务器重启后失效。

mysql> show variables like 'log_bin_trust_function_creators'; mysql> set global log_bin_trust_function_creators=1;

另外如果是在master上创建函数，想通过主从复制的⽅式将函数复制到slave上则也需在开启了log-bin的slave中设置上述变量的值为ON（变量的设置不会从master复制到slave上，这点需要注意），否则主从复制会报错。

2.明确指明函数的类型

1 ）、DETERMINISTIC 不确定的

2 ）、NO SQL 没有SQl语句，当然也不会修改数据

3 ）、READS SQL DATA 只是读取数据，当然也不会修改数据

⽐如：CREATE DEFINER=`username`@`%` READS SQL DATA FUNCTION `fn_getitemclock`(i_itemid bigint,i_clockint,i_pos int) RETURNS int(11)...

这样⼀来相当于明确的告知MySQL服务器这个函数不会修改数据，因此可以在开启了log-bin的服务器上安全的创建并被复制

到开启了log-bin的slave上。修改存储过程函数语法

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]

characteristic:

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'

删除存储过程函数语法

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

查看存储过程和函数1.查看存储过程状态

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']show procedure status like 'Pro_Employee' \\G

2.查看存储过程和函数的创建语法

SHOW CREATE {PROCEDURE | FUNCTION} sp_nameSHOW CREATE PROCEDURE Pro_Employee \\G;

3.查看存储过程和函数详细信息

复制代码 代码如下:

SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME='Pro_Employee' \\G;总结

存储过程和函数语法不难理解，但是往往存储过程中不单单只包含这种简单的查询语法，还会嵌套循环语句、变量、报错处理、事务等，下⼀篇⽂章会单独讲变量，将变量的知识加⼊到存储过程，包括变量的声明和报错处理，欢迎关注。