MySQL 存储过程_云计算

发布时间:2024-07-11 19:51

阅读量:17

1.简介

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

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

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

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

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

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

2.优缺点

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

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

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

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

3.创建存储过程语法

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

代码语言：javascript

复制

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。

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

示例

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

代码语言：javascript

复制

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 语句可以调用存储过程。

代码语言：javascript

复制

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

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

代码语言：javascript

复制

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 语句查看存储过程列表和属性。

代码语言：javascript

复制

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

这个是一个 MySQL 扩展语句。

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

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

代码语言：javascript

复制

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 语句可以查看存储过程的定义。

代码语言：javascript

复制

SHOW CREATE PROCEDURE proc_name

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

代码语言：javascript

复制

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 语句。

代码语言：javascript

复制

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 语句可以删除存储过程。

代码语言：javascript

复制

DROP PROCEDURE [IF EXISTS] sp_name

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

8.实例讲解

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

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

代码语言：javascript

复制

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

代码语言：javascript

复制

---------------------------
--@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）语法太苛刻了，不支持在存储过程中有注释，比如下面简单的存储过程。

代码语言：javascript

复制

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

执行截图如下：

这里写图片描述

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

这里写图片描述

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

代码语言：javascript

复制

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

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

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

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

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

代码语言：javascript

复制

--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

SET 语句用于在查询或存储程序中给已声明的变量赋值。

它可以用于在任何上下文中设置变量的值，无论是在查询中还是在存储过程中。

SET 不仅可以用于设置局部变量的值，还可以用于设置用户变量的值（以 @ 符号开头的变量），且不需要指定类型。

代码语言：javascript

复制