MySQL 存储过程和函数

最新推荐文章于 2023-05-04 10:38:04 发布

转载最新推荐文章于 2023-05-04 10:38:04 发布 · 511 阅读

文章标签：

#mysql

mysql 专栏收录该内容

37 篇文章

订阅专栏

本文详细介绍MySQL存储过程的创建、修改及删除语法，并通过实例演示如何使用存储过程进行数据查询及错误处理。

概述

一提到存储过程可能就会引出另一个话题就是存储过程的优缺点，这里也不做讨论。因为mysql中存储过程和函数的语法非常接近所以就放在一起，主要区别就是函数必须有返回值（return），并且函数的参数只有IN类型而存储过程有IN、OUT、INOUT这三种类型。

1.创建存储过程和函数语法

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语法块是需要注意的地方，先用一个例子来介绍。

2.示例

#创建数据库
DROP DATABASE IF EXISTS Dpro;
CREATE  DATABASE Dpro
CHARACTER 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 INVOKER BEGIN SELECT COUNT(id) INTO pcount FROM Employee WHERE depid = pdepid; END $$ DELIMITER ;
#执行存储过程 CALL Pro_Employee( 101, @pcount ); SELECT @pcount;

3. 语法解释

在创建存储过程的时候一般都会用DELIMITER

. . . . . E N D

$.....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, NO SQL, or READS SQL DATA该三个状态也会报错。

4.报错示例

Error Code: 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)

这个报错就是上面注意部分说的问题。原来是因为在主从复制的两台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_clock int,i_pos int) RETURNS int(11)...
这样一来相当于明确的告知MySQL服务器这个函数不会修改数据，因此可以在开启了log-bin的服务器上安全的创建并被复制到开启了log-bin的slave上。

5.修改存储过程函数语法

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

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

6.删除存储过程函数语法

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

7.查看存储过程和函数

1.查看存储过程状态

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

show procedure status like 'Pro_Employee' \G

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

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

SHOW CREATE PROCEDURE Pro_Employee \G;

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

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

总结

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

备注：

作者：pursuer.chen

博客：http://www.cnblogs.com/chenmh

本站点所有随笔都是原创，欢迎大家转载；但转载时必须注明文章来源，且在文章开头明显处给明链接。

《欢迎交流讨论》

概述

变量在存储过程中会经常被使用，变量的使用方法是一个重要的知识点，特别是在定义条件这块比较重要。

mysql版本：5.6

变量定义和赋值

#创建数据库
DROP DATABASE IF EXISTS Dpro;
CREATE  DATABASE Dpro
CHARACTER 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);

declare定义变量

在存储过程和函数中通过declare定义变量在BEGIN…END中，且在语句之前。并且可以通过重复定义多个变量

注意：declare定义的变量名不能带‘@’符号，mysql在这点做的确实不够直观，往往变量名会被错成参数或者字段名。

DECLARE var_name[,...] type [DEFAULT value]

例如：

DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '陈';
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid;

END$$
DELIMITER ;</span></pre>
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a href="javascript:void(0);" onclick="copyCnblogsCode(this)" title="复制代码"><img src="//common.cnblogs.com/images/copycode.gif" alt="复制代码"></a></span></div></div>
<p><strong>SET变量赋值&nbsp;</strong></p>
<p>SET除了可以给已经定义好的变量赋值外，还可以指定赋值并定义新变量，且SET定义的变量名可以带‘@’符号，SET语句的位置也是在BEGIN ....END之间的语句之前。</p>
<p>1.变量赋值</p>
<div class="cnblogs_code">
<pre>SET var_name = expr [, var_name = expr] ...</pre>
</div>
<div class="cnblogs_code"><div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a href="javascript:void(0);" onclick="copyCnblogsCode(this)" title="复制代码"><img src="//common.cnblogs.com/images/copycode.gif" alt="复制代码"></a></span></div>
<pre><span style="color: #000000;">DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '陈';
SET pname='王';
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid AND name=pname;

END$$
DELIMITER ;

CALL Pro_Employee(101,@pcount);

SELECT @pcount;

2.通过赋值定义变量

DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '陈';
SET pname='王';
SET @ID=1;
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid AND name=pname;
SELECT @ID;

END$$
DELIMITER ;

CALL Pro_Employee(101,@pcount);

SELECT … INTO语句赋值

通过select into语句可以将值赋予变量，也可以之间将该值赋值存储过程的out参数,上面的存储过程select into就是之间将值赋予out参数。

DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '陈';
DECLARE Pid INT;
SELECT COUNT(id) INTO Pid FROM Employee WHERE depid=pdepid AND name=pname;
SELECT Pid;

END$$
DELIMITER ;

CALL Pro_Employee(101,@pcount);

这个存储过程就是select into将值赋予变量；

表中并没有depid=101 and name=’陈’的记录。

条件

条件的作用一般用在对指定条件的处理，比如我们遇到主键重复报错后该怎样处理。

定义条件

定义条件就是事先定义某种错误状态或者sql状态的名称，然后就可以引用该条件名称开做条件处理，定义条件一般用的比较少，一般会直接放在条件处理里面。

DECLARE condition_name CONDITION FOR condition_value

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | mysql_error_code

1.没有定义条件：

DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
SET @ID=1;
INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100);
SET @ID=2;
INSERT INTO Employee(id,name,depid) VALUES(6,'陈',100);
SET @ID=3;

END$$
DELIMITER ;

#执行存储过程 CALL Pro_Employee_insert(); #查询变量值 SELECT @ID,@X;

报主键重复的错误，其中1062是主键重复的错误代码，23000是sql错误状态

2.定义处理条件

DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN

#定义条件名称， DECLARE reprimary CONDITION FOR 1062 ; #引用前面定义的条件名称并做赋值处理 DECLARE EXIT HANDLER FOR reprimary SET @x =1 ; SET @ID= 1 ; INSERT INTO Employee(id,name,depid) VALUES( 1, ' 陈 ', 100 ); SET @ID= 2 ; INSERT INTO Employee(id,name,depid) VALUES( 6, ' 陈 ', 100 ); SET @ID= 3 ; END$$ DELIMITER ; CALL Pro_Employee_insert(); SELECT @ID,@X;

在执行存储过程的步骤中并没有报错，但是由于我定义的是exit，所以在遇到报错sql就终止往下执行了。

接下来看看continue的不同

DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN

#定义条件名称， DECLARE reprimary CONDITION FOR SQLSTATE '23000' ; #引用前面定义的条件名称并做赋值处理 DECLARE CONTINUE HANDLER FOR reprimary SET @x= 1 ; SET @ID= 1 ; INSERT INTO Employee(id,name,depid) VALUES( 1, ' 陈 ', 100 ); SET @ID= 2 ; INSERT INTO Employee(id,name,depid) VALUES( 6, ' 陈 ', 100 ); SET @ID= 3 ; END$$ DELIMITER ; CALL Pro_Employee_insert(); SELECT @ID,@X;

其中红色标示的是和上面不同的地方，这里定义条件使用的是SQL状态，也是主键重复的状态；并且这里使用的是CONTINUE就是遇到错误继续往下执行。

条件处理

条件处理就是之间定义语句的错误的处理，省去了前面定义条件名称的步骤。

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

handler_type:
    CONTINUE| EXIT| UNDO

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  | mysql_error_code

handler_type：遇到错误是继续往下执行还是终止，目前UNDO还没用到。

CONTINUE:继续往下执行

EXIT：终止执行

condition_values:错误状态

SQLSTATE [VALUE] sqlstate_value：就是前面讲到的SQL错误状态，例如主键重复状态SQLSTATE ‘23000’

condition_name：上面讲到的定义条件名称；

SQLWARNING：是对所有以01开头的SQLSTATE代码的速记，例如：DECLARE CONTINUE HANDLER FOR SQLWARNING。

NOT FOUND:是对所有以02开头的SQLSTATE代码的速记。

SQLEXCEPTION:是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。

mysql_error_code:是错误代码，例如主键重复的错误代码是1062，DECLARE CONTINUE HANDLER FOR 1062

语句：

DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN

#引用前面定义的条件名称并做赋值处理 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @x= 2;
#开始事务必须在DECLARE之后
START TRANSACTION ; SET @ID= 1; INSERT INTO Employee(id,name,depid) VALUES( 7,'陈', 100); SET @ID= 2; INSERT INTO Employee(id,name,depid) VALUES( 6,'陈', 100); SET @ID= 3; IF @x=2 THEN ROLLBACK; ELSE COMMIT; END IF; END $$ DELIMITER ;

#执行存储过程
CALL Pro_Employee_insert();

#查询 SELECT @ID, @X;

通过SELECT @ID,@X可以知道存储过程已经执行到了最后，但是因为存储过程后面有做回滚操作整个语句进行了回滚，所以ID=7的符合条件的记录也被回滚了。

总结

变量的使用不仅仅只有这些，在光标中条件也是一个很好的功能，刚才测试的是continue如果使用EXIT的话语句执行完“SET @ID=2;”就不往下执行了，后面的IF也不被执行整个语句不会被回滚，但是使用CONTINE当出现错误后还是会往下执行如果后面的语句还有很多的话整个回滚的过程将会很长，在这里可以利用循环，当出现错误立刻退出循环执行后面的if回滚操作，在下一篇讲循环语句会写到，欢迎关注。

备注：

作者：pursuer.chen

博客：http://www.cnblogs.com/chenmh

本站点所有随笔都是原创，欢迎大家转载；但转载时必须注明文章来源，且在文章开头明显处给明链接。

《欢迎交流讨论》