MySQL存储过程

最新推荐文章于 2021-09-10 10:34:25 发布

挽歌亽朽年

最新推荐文章于 2021-09-10 10:34:25 发布

阅读量170

点赞数

分类专栏： MySQL

本文链接：https://blog.youkuaiyun.com/ghf183184/article/details/116159552

版权

MySQL 专栏收录该内容

20 篇文章

订阅专栏

存储过程

存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL存储过程名字”即可自动完成。

常用操作数据库的 SQL 语句在执行的时候需要先编译，然后执行。存储过程则采用另一种方式来执行 SQL 语句。

一个存储过程是一个可编程的函数，它在数据库中创建并保存，一般由 SQL 语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的特定功能时，存储过程尤为合适。

MySQL 5.0 版本以前并不支持存储过程，这使 MySQL 在应用上大打折扣。MySQL 从 5.0 版本开始支持存储过程，既提高了数据库的处理速度，同时也提高了数据库编程的灵活性

存储过程是数据库中的一个重要功能，存储过程可以用来转换数据、数据迁移、制作报表，它类似于编程语言，一次执行成功，就可以随时被调用，完成指定的功能操作。

使用存储过程不仅可以提高数据库的访问效率，同时也可以提高数据库使用的安全性。

对于调用者来说，存储过程封装了 SQL 语句，调用者无需考虑逻辑功能的具体实现过程。只是简单调用即可，它可以被 Java 和 C# 等编程语言调用。

编写存储过程对开发者要求稍微高一些，但这并不影响存储过程的普遍使用，因为存储过程有如下优点：

封装性：通常完成一个逻辑功能需要多条 SQL 语句，而且各个语句之间很可能传递参数，所以，编写逻辑功能相对来说稍微复杂些，而存储过程可以把这些 SQL 语句包含到一个独立的单元中，使外界看不到复杂的 SQL 语句，只需要简单调用即可达到目的。并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码。
可增强 SQL 语句的功能和灵活性：存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
可减少网络流量：由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。
高性能：当存储过程被成功编译后，就存储在数据库服务器里了，以后客户端可以直接调用，这样所有的 SQL 语句将从服务器执行，从而提高性能。但需要说明的是，存储过程不是越多越好，过多的使用存储过程反而影响系统性能。
提高数据库的安全性和数据的完整性：存储过程提高安全性的一个方案就是把它作为中间组件，存储过程里可以对某些表做相关操作，然后存储过程作为接口提供给外部程序。这样，外部程序无法直接操作数据库表，只能通过存储过程来操作对应的表，因此在一定程度上，安全性是可以得到提高的。
使数据独立：数据的独立可以达到解耦的效果，也就是说，程序可以调用存储过程，来替代执行多条的 SQL 语句。这种情况下，存储过程把数据同用户隔离开来，优点就是当数据表的结构改变时，调用表不用修改程序，只需要数据库管理者重新编写存储过程即可

MySQL创建存储过程

MySQL 存储过程是一些 SQL 语句的集合，比如有时候我们可能需要一大串的 SQL 语句，或者说在编写 SQL 语句的过程中需要设置一些变量的值，这个时候我们就完全有必要编写一个存储过程。

编写存储过程并不是件简单的事情，但是使用存储过程可以简化操作，且减少冗余的操作步骤，同时，还可以减少操作过程中的失误，提高效率，因此应该尽可能的学会使用存储过程。

下面主要介绍如何创建存储过程。

可以使用 CREATE PROCEDURE 语句创建存储过程，语法格式如下：

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>

语法说明如下：

过程名：存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db_name.sp_name。
- 需要注意的是，名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误。
过程参数：存储过程的参数列表。其中，<参数名>为参数名，<类型>为参数的类型（可以是任何有效的 MySQL 数据类型）。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数。
- MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。
- 需要注意的是，参数的取名不要与数据表的列名相同，否则尽管不会返回出错信息，但是存储过程的 SQL 语句会将参数名看作列名，从而引发不可预知的结果。
过程体存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束。若存储过程体中只有一条 SQL 语句，则可以省略 BEGIN-END 标志。

DELIMITER 命令

在存储过程的创建中，经常会用到一个十分重要的 MySQL 命令，即 DELIMITER 命令

在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句，这样显然不行。

为解决以上问题，通常使用 DELIMITER 命令将结束命令修改为其他字符。语法格式如下：

DELIMITER $$

语法说明如下：

$$ 是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“?”或两个“￥”等。
当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为它是 MySQL 的转义字符。

在 MySQL 命令行客户端输入如下 SQL 语句

mysql > DELIMITER ??

成功执行这条 SQL 语句后，任何命令、语句或程序的结束标志就换为两个问号“??”了。

若希望换回默认的分号“;”作为结束标志，则在 MySQL 命令行客户端输入下列语句即可：

mysql > DELIMITER ;

注意：DELIMITER 和分号“;”之间一定要有一个空格。在创建存储过程时，必须具有 CREATE ROUTINE 权限。

/*
	创建存储过程
	-- 修改分隔符为$
	DELIMITER $
	- 标准语法
	CREATE PROCEDURE 存储过程名称(参数列表)
	BEGIN
		SQL 语句列表;
	END$
	-- 修改分隔符为分号
	DELIMITER ;
*/
-- 创建stu_group()存储过程，封装 分组查询总成绩，并按照总成绩升序排序的功能
DELIMITER $

CREATE PROCEDURE stu_group()
BEGIN
	SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
END$

DELIMITER ;

MySQL调用存储过程

在这里插入图片描述

/*
	调用存储过程
	CALL 存储过程名称(实际参数);
*/
-- 调用stu_group()存储过程
CALL stu_group();

MySQL查看存储过程

创建好存储过程后，用户可以通过 SHOW ATATUS 语句来查看存储过程的状态，也可以通过 SHOW CREATE 语句来查看存储过程的定义。

查看存储过程的状态

MySQL 中可以通过 SHOW STATUS 语句查看存储过程的状态，其基本语法形式如下：

SHOW PROCEDURE STATUS LIKE 存储过程名;

LIKE 存储过程名用来匹配存储过程的名称，LIKE 不能省略。

查看存储过程的定义

MySQL 中可以通过 SHOW CREATE 语句查看存储过程的状态，语法格式如下：

SHOW CREATE PROCEDURE 存储过程名;

SHOW STATUS 语句只能查看存储过程是操作的哪一个数据库、存储过程的名称、类型、谁定义的、创建和修改时间、字符编码等信息。但是，这个语句不能查询存储过程的集体定义，如果需要查看详细定义，需要使用 SHOW CREATE 语句。

存储过程的信息都存储在 information_schema 数据库下的 Routines 表中，可以通过查询该表的记录来查询存储过程的信息，SQL 语句如下：

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=存储过程名;

在 information_schema 数据库下的 routines 表中，存储着所有存储过程的定义。所以，使用 SELECT 语句查询 routines 表中的存储过程和函数的定义时，一定要使用 routine_name 字段指定存储过程的名称，否则，将查询出所有的存储过程的定义。

/*
	查询数据库中所有的存储过程
	SELECT * FROM mysql.proc WHERE db='数据库名称';
*/
-- 查看db6数据库中所有的存储过程
SELECT * FROM mysql.proc WHERE db='db6';

MySQL修改存储过程

在实际开发过程中，业务需求修改的情况时有发生，所以修改 MySQL 中的存储过程是不可避免的。

MySQL 中通过 ALTER PROCEDURE 语句来修改存储过程。

MySQL 中修改存储过程的语法格式如下：

ALTER PROCEDURE 存储过程名 [ 特征 ... ]

特征指定了存储过程的特性，可能的取值有：

CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句。
NO SQL 表示子程序中不包含 SQL 语句。
READS SQL DATA 表示子程序中包含读数据的语句。
MODIFIES SQL DATA 表示子程序中包含写数据的语句。
SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行。
DEFINER 表示只有定义者自己才能够执行。
INVOKER 表示调用者可以执行。
COMMENT ‘string’ 表示注释信息

注意：ALTER PROCEDURE 语句用于修改存储过程的某些特征。如果要修改存储过程的内容，可以先删除原存储过程，再以相同的命名创建新的存储过程；如果要修改存储过程的名称，可以先删除原存储过程，再以不同的命名创建新的存储过程。

MySQL删除存储过程

存储过程被创建后，就会一直保存在数据库服务器上，直至被删除。当 MySQL 数据库中存在废弃的存储过程时，我们需要将它从数据库中删除。

MySQL 中使用 DROP PROCEDURE 语句来删除数据库中已经存在的存储过程。语法格式如下：

DROP PROCEDURE [ IF EXISTS ] <过程名>

语法说明如下：

过程名：指定要删除的存储过程的名称。
IF EXISTS：指定这个关键字，用于防止因删除不存在的存储过程而引发的错误。

注意：**存储过程名称后面没有参数列表，也没有括号，在删除之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行

/*
	删除存储过程
	DROP PROCEDURE [IF EXISTS] 存储过程名称;
*/
DROP PROCEDURE IF EXISTS stu_group;