MySQL 存储过程

恋喵大鲤鱼

已于 2024-05-23 21:47:02 修改

阅读量2.6k

点赞数 1

分类专栏：《MySQL 快速上手》文章标签：存储过程 MySQL

于 2016-07-01 19:28:35 首次发布

本文链接：https://blog.csdn.net/K346K346/article/details/51801977

版权

《MySQL 快速上手》专栏收录该内容

28 篇文章 8 订阅

订阅专栏

文章目录

1.简介
2.优缺点
3.创建存储过程
- 语法
- 示例
4.调用存储过程
5.查看存储过程
- SHOW PROCEDURE STATUS
- SHOW CREATE PROCEDURE
6.修改存储过程
7.删除存储过程
8.实例讲解
参考文献

1.简介

存储过程（Stored Procedure）是一种存储在数据库中的程序，可供外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的 SQL 语句集，提供许多过程语言的功能，例如变量定义、条件语句、循环语句、游标以及异常处理等。存储过程没有返回值，但是它可以通过输出参数实现数据的返回，同时还可以产生一个查询结果返回到客户端。

存储过程经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数（需要时）调用执行。

存储过程思想上很简单，就是数据库 SQL 语句的封装与重用。

MySQL 5.0 版本引入了对存储过程、存储函数和触发器等存储程序的支持。这使得开发人员能够在数据库中创建和管理复杂的业务逻辑，从而提高了数据库的功能和灵活性。

存储例程包括存储过程和函数。存储程序包括存储例程、触发器和事件。

2.优缺点

存储过程是一组可以存储在服务器中的 SQL 语句。一旦这样做了，客户端就不需要重新发出单个语句，而是可以引用存储过程。

使用存储过程在某些场景下有很多好处。

代码重用：存储例程允许您将相同的逻辑用于多个应用程序，减少了代码的重复编写。
安全性：存储例程可以限制对表的直接访问，只通过存储过例程执行数据库操作，并且可以确保每个操作都被正确记录，从而提高数据的安全性。
减少网络流量：存储过程在服务器端执行，只返回结果，减少了在网络上传输的数据量。

不过，存储过程也存在一些缺点：

MySQL 存储过程的语法和其他数据库之间不兼容，无法直接移植。
存储过程需要占用数据库服务器的资源，包括 CPU、内存等。MySQL 对于大量逻辑处理的支持不够完善。
存储过程的开发和维护需要专业的技能。MySQL 存储过程不支持调试功能，增加了应用程序的开发和维护难度。

一般来说，对于业务快速变化的互联网应用，倾向于将业务逻辑放在应用层，便于扩展。对于传统行业，或者复杂的报表分析，合理使用存储过程可以提高效率。

3.创建存储过程

语法

使用 CREATE PROCEDURE 语句创建存储过程。

CREATE
    [DEFINER = user]
    PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

type:
    Any valid MySQL data type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

routine_body:
    Valid SQL routine statement

DEFINER 子句用于指定存储过程的创建者或拥有者，也就是定义存储过程的用户。作用是确定存储过程在执行时所具有的执行者的权限和权限级别。存储过程在执行时将使用 DEFINER 指定的用户的权限来执行，而不是调用存储过程的用户的权限。这可以用于实现数据库的安全性和隔离。

proc_parameter 为存储过程的参数，使用 IN（输入）、OUT（输出）或 INOUT（输入输出）修饰。如果不显示指定，缺省为 IN。

在创建存储过程时还可以指定一些可选的属性：

COMMENT 可以为存储过程添加注释。
LANGUAGE表示编写存储过程的语言，目前 SQL 是唯一可用值。
DETERMINISTIC 属性表示这是一个确定性存储过程，对于相同的输入参数一定会返回相同的结果；MySQL 缺省是非确定性（NOT DETERMINISTIC）。
CONTAINS SQL 表示程序中不包含读取或者写入数据表的语句，这是默认设置。举例来说，SET @x = 1 或者 DO RELEASE_LOCK(‘abc’) 不会读写任何数据。NO SQL 表示程序不包含任何 SQL 语句。READS SQL DATA 表示程序包含读取操作（例如 SELECT），但不会修改数据表。MODIFIES SQL DATA 表示程序包含写入操作（例如 INSERT 或者 DELETE）。这些属性仅供 MySQL 服务器参考使用，不会用于限制程序中实际使用的语句。
SQL SECURITY 表示存储过程以定义者（DEFINER ）权限还是调用者（INVOKER）权限执行。

示例

给定一个国家/地区代码，计算该国家/地区出现在 world 数据库的城市表中的城市数量。使用 IN 参数传递国家/地区代码，并使用 OUT 参数返回城市计数。

mysql> DELIMITER //

mysql> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)
       BEGIN
         SELECT COUNT(*) INTO cities FROM world.city
         WHERE CountryCode = country;
       END//
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

如果使用 mysql 客户端程序定义一个包含分号字符的存储程序，就会出现问题。默认情况下，mysql 本身将分号识别为语句分隔符，因此必须临时重新定义分隔符，以使 mysql 将整个存储程序定义传递到服务器。

所以上面的示例中，我们使用 DELIMITER 重新定义语句分隔符为 //，定义存储过程使用 // 结束，最后再使用 DELIMITER 将语句分隔符改回分号。

4.调用存储过程

使用 CALL 语句可以调用存储过程。

CALL sp_name([parameter[,...]])
CALL sp_name[()]

比如调用前文创建的查询国家/地区城市数量的存储过程，查询日本的城市数量。

mysql> CALL citycount('JPN', @cities); -- cities in Japan
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @cities;
+---------+
| @cities |
+---------+
|     248 |
+---------+
1 row in set (0.00 sec)

在 MySQL 中，@ 符号用于定义和使用用户变量。用户变量是一种在会话中存储和操作数据的机制，它们在 SQL 查询中可以使用，但并不与数据库表中的列或行直接关联。

调用存储过程传入的 @cities 表示接收城市数量的用户变量。调用完存储过程后，通过 SELECT 可以查询变量 @cities 的值，获知指定国家/地区的城市数量。

5.查看存储过程

SHOW PROCEDURE STATUS

使用 SHOW PROCEDURE STATUS 语句查看存储过程列表和属性。

SHOW PROCEDURE STATUS
    [LIKE 'pattern' | WHERE expr]

这个是一个 MySQL 扩展语句。

LIKE 用于匹配存储过程的名称，WHERE 可以指定更多的过滤条件。

例如以下语句返回了存储过程 citycount 的相关信息。

mysql> SHOW PROCEDURE STATUS WHERE name = 'citycount' \G
*************************** 1. row ***************************
                  Db: world
                Name: citycount
                Type: PROCEDURE
             Definer: testuser@%
            Modified: 2023-08-21 11:57:50
             Created: 2023-08-21 11:57:50
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8mb4_general_ci

返回的信息包括数据库、存储过程名称、类型（PROCEDURE）、创建者、创建时间和修改时间、调用权限以及字符集信息。

SHOW CREATE PROCEDURE

使用 SHOW CREATE PROCEDURE 语句可以查看存储过程的定义。

SHOW CREATE PROCEDURE proc_name

例如查看存储过程 citycount 的定义。

mysql> SHOW CREATE PROCEDURE test.citycount \G
*************************** 1. row ***************************
           Procedure: citycount
            sql_mode: NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`testuser`@`%` PROCEDURE `citycount`(IN country CHAR(3), OUT cities INT)
BEGIN SELECT COUNT(*) INTO cities FROM world.city WHERE CountryCode = country; END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8mb4_general_ci

事实上 SHOW PROCEDURE STATUS 和 SHOW CREATE PROCEDURE 均是从系统表 INFORMATION_SCHEMA.ROUTINES 获取存储过程元信息，所以我们也可以直接查看 INFORMATION_SCHEMA.ROUTINES 表查看存储过程元信息。

6.修改存储过程

如果想修改存储过程的属性可以使用 ALTER PROCEDURE 语句。

ALTER PROCEDURE proc_name [characteristic ...]

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

上面的语句不能更改存储过程的参数或主体。要进行此类更改，必须使用 DROP PROCEDURE 和 CREATE PROCEDURE 删除并重新创建该存储过程。

您必须具有该过程的 ALTER ROUTINE 权限。默认情况下，该权限会自动授予过程创建者。可以通过禁用 automatic_sp_privileges 系统变量来更改此行为。

7.删除存储过程

MySQL 使用 DROP PROCEDURE 语句可以删除存储过程。

DROP PROCEDURE [IF EXISTS] sp_name

如果删除不存在的存储过程会返回错误，使用 IF EXISTS 可避免该错误。

8.实例讲解

项目中需要一个脚本，需要在 MySQL 中判断表的索引是否存在，不存在则创建。

本以为 MySQL 存在以下的语句能够完成上面的功能，但是没有。

IF NOT EXISTS(SHOW INDEX FROM tbl_name) THEN ALTER TABLE tbl_name ADD INDEX(column_list) END IF;

# 或
CREATE INDEX index_name ON tbl_name (column_list) IF NOT EXISTS index_name;

可惜啊！MySQL 目前并不支持在 SQL 语句中存在流控制语句，例如上面的IF NOT EXISTS THEN END IF；让人痛心疾首。但是我们可以使用存储过程完成上面要求的功能。

MySQL 判断表的索引是否存在，不存在则创建的存储过程书写如下。里面很多细节需要了解，不然会出现自认为莫名其妙的问题。

---------------------------
--@brief:判断指定数据表的索引是否存在，如果不存在则创建
--@param:tableName:数据表名；idxName：索引名；columnName：建立索引的列名
---------------------------
DROP PROCEDURE IF EXISTS proc_addIndex;  
DELIMITER //
create procedure proc_addIndex(IN tableName VARCHAR(64),IN idxName VARCHAR(200),IN columnName VARCHAR(64))
BEGIN
	IF NOT EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema="databaseName" AND table_name=tableName AND index_name=idxName)
	THEN
		SET @sqlStr=CONCAT("alter table ",tableName," add index(",columnName,")");
		PREPARE sqlStatement from @sqlStr;
		EXECUTE sqlStatement; 
		DEALLOCATE PREPARE sqlStatement;
	END IF; 
END//
DELIMITER ;

阅读上面的代码需要注意如下几个问题。

（1）MySQL 存储过程中是不支持表名和列名作为变量。如果表名和列名作为参数的话，只有通过 CONCAT 函数拼接动态 SQL 字符串，使用 PREPARE 语句预处理后，再由 EXECUTE 来执行。但是在 IF EXISTS 或者 IF NOT EXISTS 中 SQL 语句作为条件出现时，表名和列名可以作为变量。

（2）DELIMITER // 用于改变 SQL 语句分隔符为 //，否则 mysql 在回车的情况下会按照默认分割符分号将存储过程拆分，使其执行失败。存储过程结束后，再次使用 DELIMITER 将语句分隔符改回分号。注意 DELIMITER 和分隔符之间要有空格。

（3）MySQL（5.7.38）语法太过苛刻，不支持在存储过程中有注释，比如下面简单的存储过程。

DELIMITER //
CREATE PROCEDURE HelloWorld()
BEGIN
	SELECT 'Hello World'; 
END// 
DELIMITER ;

执行截图如下：

这里写图片描述

如果在 BEGIN 后面加上一行注释就会出现错误。

这里写图片描述
（4）还有一个很苛刻的地方就是，MySQL 的存储过程不能写在同一行。比如将上面的 HelloWorld 存储过程写在同一行，写成如下格式执行不出错，但也没有成功，此时 MySQL 的分隔符是我们使用 DELIMITER 指定的分割符，很是奇怪，有兴趣的读者可以试一下。

DROP PROCEDURE IF EXISTS HelloWorld;
DELIMITER // CREATE PROCEDURE HelloWorld() BEGIN SELECT 'Hello World'; END// DELIMITER ;

（5）MySQL存储过程 DECLARE 和 SET 定义变量的区别。

DECLARE 语句用于在存储过程、函数、触发器等存储程序中声明局部变量。

声明的变量在存储程序的范围内有效，它们不能在存储程序外部访问。

DECLARE 声明的变量只是声明了变量的名称和数据类型，但不进行赋值。您需要在后续的逻辑中使用 SET 或其他方式为变量赋值。

--DECLARE 定义变量
DECLARE var_name [datatype(size)] DEFAULT [default_value];
 
--例如
DECLARE count INT DEFAULT 0;

--使用 SET 赋值
SET count=5;

--还可以通过 SELECT INTO 语句将返回的值赋给变量
SELECT COUNT(*) INTO count FROM tbl_name;

--可同时定义多个变量
DECLARE x, y INT DEFAULT 0