MySQL：存储过程

最新推荐文章于 2023-08-08 17:06:29 发布

Hey 锡瑞

最新推荐文章于 2023-08-08 17:06:29 发布

阅读量443

点赞数 1

分类专栏： MySQL

本文链接：https://blog.csdn.net/RuiKe1400360107/article/details/108468808

版权

MySQL 专栏收录该内容

12 篇文章 1 订阅

订阅专栏

一、概述

1.1、什么是存储过程

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

1.2、使用存储过程原因

（1）存储过程只在创造时进行编译，以后每次执行存错过程都不需要在重新编译，而一般SQL语句每执行一次就编译一次，所以使用存储过程可提高数据库执行速度。

（2）当丢数据库进行复杂操作时(如对多个表进行Update， Insert， Query， Delete)，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的sql语句，可能要多次连接数据库。而换成存储，就只需要连接一次数据库就可以了

（3）存储过程可以重复使用，可减少数据库开发人员的工作量。

（4）安全性高，可设定只有某此用户才具有对指定存储过程的使用权

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

1.3、存储过程使用场景

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

1.4、存储过程优点

（1）封装性

通常完成一个逻辑功能需要多条 SQL 语句，而且各个语句之间很可能传递参数，所以，编写逻辑功能相对来说稍微复杂些，而存储过程可以把这些 SQL 语句包含到一个独立的单元中，使外界看不到复杂的 SQL 语句，只需要简单调用即可达到目的。并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码

（2）可增强 SQL 语句的功能和灵活性

存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算

（3）可减少网络流量

由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载

（4）高性能

当存储过程被成功编译后，就存储在数据库服务器里了，以后客户端可以直接调用，这样所有的 SQL 语句将从服务器执行，从而提高性能。但需要说明的是，存储过程不是越多越好，过多的使用存储过程反而影响系统性能

（5）提高数据库的安全性和数据的完整性

存储过程提高安全性的一个方案就是把它作为中间组件，存储过程里可以对某些表做相关操作，然后存储过程作为接口提供给外部程序。这样，外部程序无法直接操作数据库表，只能通过存储过程来操作对应的表，因此在一定程度上，安全性是可以得到提高的

（6）使数据独立（解耦）

数据的独立可以达到解耦的效果，也就是说，程序可以调用存储过程，来替代执行多条的 SQL 语句。这种情况下，存储过程把数据同用户隔离开来，优点就是当数据表的结构改变时，调用表不用修改程序，只需要数据库管理者重新编写存储过程即可

二、存储过程增删改查

2.1、创建语句

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

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

（1）过程名

存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db_name.sp_name

（注意：名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误）

（2）过程参数

存储过程的参数列表。其中，<参数名>为参数名，<类型>为参数的类型（可以是任何有效的 MySQL 数据类型）。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数。

MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数

（注意：参数的取名不要与数据表的列名相同，否则尽管不会返回出错信息，但是存储过程的 SQL 语句会将参数名看作列名，从而引发不可预知的结果）

（3）过程体

存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束。若存储过程体中只有一条 SQL 语句，则可以省略 BEGIN-END 标志

2.2、 DELIMITER 命令

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

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

DELIMITER $$

语法说明如下：

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

在 MySQL 命令行客户端输入如下 SQL 语句，成功执行这条 SQL 语句后，任何命令、语句或程序的结束标志就换为两个问号“??”了：

mysql > DELIMITER ??

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

mysql > DELIMITER ;

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

2.3、创建存储过程案例

（1）创建名称为 ShowStuScore 的存储过程，存储过程的作用是从学生成绩信息表中查询学生的成绩信息，输入的 SQL 语句和执行过程如下所示：

mysql> DELIMITER //
mysql> CREATE PROCEDURE ShowStuScore()
    -> BEGIN
    -> SELECT * FROM tb_students_score;
    -> END //
Query OK， 0 rows affected (0.09 sec)

（2）创建名称为 GetScoreByStu 的存储过程，输入参数是学生姓名。存储过程的作用是通过输入的学生姓名从学生成绩信息表中查询指定学生的成绩信息，输入的 SQL 语句和执行过程如下所示：

mysql> DELIMITER //
mysql> CREATE PROCEDURE GetScoreByStu
    -> (IN name VARCHAR(30))
    -> BEGIN
    -> SELECT student_score FROM tb_students_score
    -> WHERE student_name=name;
    -> END //
Query OK, 0 rows affected (0.01 sec)

2.4、MySQL查看存储过程

SHOW ATATUS: 查看存储过程的状态

SHOW CREATE :查看存储过程的定义

（1）查看存储过程的状态

语法：

SHOW PROCEDURE STATUS LIKE 存储过程名;

（2）查看存储过程的定义

语法：

SHOW CREATE PROCEDURE 存储过程名;

示例：下面使用 SHOW CREATE 查询名为 showstuscore 的存储过程的状态，SQL 语句和运行结果如下：

mysql> SHOW CREATE PROCEDURE showstuscore \G
*************************** 1. row ***************************
           Procedure: showstuscore
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `showstuscore`()
BEGIN
SELECT id,name,score FROM studentinfo;
END
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.01 sec)

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

2.5、修改存储过程

ALTER PROCEDURE

（1）语法格式

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

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

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

（2）案例

修改存储过程 showstuscore 的定义，将读写权限改为 MODIFIES SQL DATA，并指明调用者可以执行，代码如下：

mysql> ALTER PROCEDURE showstuscore MODIFIES SQL DATA SQL SECURITY INVOKER;
Query OK, 0 rows affected (0.01 sec)

执行代码，并查看修改后的信息，运行结果如下：

mysql> SHOW CREATE PROCEDURE showstuscore \G
*************************** 1. row ***************************
           Procedure: showstuscore
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `showstuscore`()
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
BEGIN
SELECT id,name,score FROM studentinfo;
END
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

结果显示，存储过程修改成功。从运行结果可以看到，访问数据的权限已经变成了 MODIFIES SQL DATA，安全类型也变成了 INVOKE。

2.6、MySQL删除存储过程

DROP PROCEDURE

（1）语法格式

DROP PROCEDURE [ IF EXISTS ] <过程名>

语法说明如下：

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

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

（2）案例

下面删除存储过程 ShowStuScore：

mysql> DROP PROCEDURE ShowStuScore;
Query OK, 0 rows affected (0.08 sec)

删除后，可以通过查询 information_schema 数据库下的 routines 表来确认上面的删除是否成功，结果显示，没有查询出任何记录，说明存储过程 ShowStuScore 已经被删除了：

mysql> SELECT * FROM information_schema.routines WHERE routine_name='ShowStuScore';
Empty set (0.03 sec)

参考：http://c.biancheng.net/mysql/85/

Hey 锡瑞

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
2
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录