MySQL 存储例程

最后修改于 2023 年 1 月 10 日

本章介绍 MySQL 中的存储例程。在 MySQL 中，有两种类型的存储例程：存储过程和存储函数。

存储过程通过 CALL 语句调用。它们不返回值。存储函数返回数值。并且与 SELECT 语句一起使用。

存储例程是一组可以存储在服务器中的 SQL 语句。存储例程通常不被接受。它们有一些优点，但也有几个缺点。存储例程通常用于数据验证或访问控制。

存储过程在以下情况下可能很有用，即有许多用不同语言编写或在不同平台上运行的客户端应用程序，但需要执行相同的数据库操作。它们可以带来一些性能提升。存储例程存储在服务器中，因此网络负载减少。在某些数据库系统中，存储例程可以被预编译，这提高了性能。如果更改数据库中的某些逻辑，则所有可能的客户端都会自动准备就绪。当我们在客户端更改一些逻辑时，这必须在所有可能的客户端中完成。

另一方面，存储例程也有一些缺点。存储例程违反了设计模式，其中业务逻辑、数据和表示层被分离在特定的层中。存储例程将业务逻辑与数据混在一起。存储例程更难调试和测试。在存储例程中包含大量业务逻辑的应用程序的可伸缩性较差。此外，存储例程没有版本控制系统。最后，存储例程在不同的数据库系统中的实现方式不同。这使得在数据库系统之间进行潜在的迁移更加困难。

一个简单的过程

该过程是用 CREATE PROCEDURE 语句创建的。

mysql> CREATE PROCEDURE AllCars() SELECT * FROM Cars;

在此语句中，我们创建了一个名为 AllCars() 的新简单过程。该过程名称后面的 select 语句是该过程的主体，在调用该过程时执行。该过程从 Cars 表中选择所有数据。

mysql> CALL AllCars();
+----+------------+--------+
| Id | Name       | Cost   |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+

我们调用 AllCars() 过程，其主体被执行。

一个简单的函数

函数是用 CREATE FUNCTION 语句创建的。函数返回一个值。它使用 SELECT 语句调用。

-- this function computes the area
-- of a circle; it takes a radius as
-- a parameter

DELIMITER $$

DROP FUNCTION IF EXISTS CircleArea;

CREATE FUNCTION CircleArea(r DOUBLE) RETURNS DOUBLE
BEGIN
    DECLARE area DOUBLE;

    SET area = r * r * pi();
    RETURN area;
END 

$$

DELIMITER ;

在此代码中，我们创建一个 CircleArea() 函数，该函数计算圆的面积。它将半径作为参数。创建多于一行的过程或函数的最佳方法是创建一个 SQL 文件，并使用 source 命令读取该文件。

-- this function computes the area
-- of a circle; it takes a radius as
-- a parameter

注释以双破折号开头。

DELIMITER $$

SQL 语句以分号结束。要创建过程或函数，我们需要多个语句。因此，我们需要暂时使用不同的分隔符。这里我们使用 $$ 作为分隔符。我们可以使用不同的字符。在函数定义的末尾，我们使用此分隔符。

DROP FUNCTION IF EXISTS CircleArea;

在开发存储例程时，我们会遇到各种语法或其他错误。该函数可能已经被部分创建。因此，我们使用上面的语句来消除我们有缺陷的尝试，并从头开始创建一个函数。

CREATE FUNCTION CircleArea(r DOUBLE) RETURNS DOUBLE

我们创建一个名为 CircleArea 的函数。它采用类型为 DOUBLE 的参数 r。该函数返回 DOUBLE 类型的值。

BEGIN
   ...
END

函数体位于 BEGIN 和 END 关键字之间。

DECLARE area DOUBLE;

我们在例程中声明一个新变量。它的名称是 area，数据类型是 DOUBLE。

SET area = r * r * pi();

我们使用给定的半径计算圆的面积。

RETURN area;

我们返回该变量。

$$

这是例程的结尾。

DELIMITER ;

我们再次使用默认分隔符。

mysql> source circlearea.sql

mysql> SELECT CircleArea(5.5);
+-------------------+
| CircleArea(5.5)   |
+-------------------+
| 95.03317777109125 |
+-------------------+

我们创建 CircleArea() 函数并使用 SELECT 语句调用它。

过程参数

一个过程不能返回值。但是，它可以处理三种类型的变量

IN
OUT
INOUT

IN 是参数的默认类型。当未明确指定类型时使用。IN 参数将传递给过程。它可以在过程中修改，但在过程外部保持不变。对于 OUT 参数，没有值传递给过程。它可以在过程中修改。并且该变量在过程外部可用。INOUT 变量是 IN 和 OUT 参数的混合。它可以传递给过程，在那里更改，并且可以在过程外部检索。

-- this procedure computes the power 
-- of a given value

DELIMITER $$

DROP PROCEDURE IF EXISTS Pow;

CREATE PROCEDURE Pow(IN val DOUBLE, OUT p DOUBLE) 
BEGIN
    SET p = val * val;
END 

$$

DELIMITER ;

在此过程中，我们计算给定值的幂。

CREATE PROCEDURE Pow(IN val DOUBLE, OUT p DOUBLE)

该过程接受两个参数。第一个是计算幂的值。它被声明为 IN。它被传递给例程并在那里使用。第二个变量是一个 OUT 变量。它是我们存储此过程结果的参数。它可以在例程完成后使用。

mysql> source power.sql

mysql> CALL Pow(3, @p);

mysql> SELECT @p;
+------+
| @p   |
+------+
|    9 |
+------+

我们创建 Pow() 过程。我们使用 CALL 语句调用它。结果存储在 @p 变量中。最后，我们选择 @p 变量以查看其内容。

随机数

在下面的示例中，我们将创建一个生成五个随机数的过程。从 0 到 9。

-- this procedure generates
-- five random numbers from 0 to 9

DELIMITER $$

DROP PROCEDURE IF EXISTS FiveRandomNumbers;

CREATE PROCEDURE FiveRandomNumbers()
BEGIN
    SET @i = 0;
    REPEAT 
        SELECT FLOOR(RAND() * 10) AS 'Random Number'; 
        SET @i = @i + 1; 
    UNTIL @i >=5 END REPEAT;
END 

$$

DELIMITER ;

在此过程中，我们将使用 RAND() 和 FLOOR() 内置函数。

SET @i = 0;

此变量是一个计数器。

REPEAT 
    SELECT FLOOR(RAND() * 10) AS 'Random Number'; 
    SET @i = @i + 1; 
UNTIL @i >=5 END REPEAT;

REPEAT，UNTIL 关键字创建一个循环。计数器用于控制迭代次数。在我们的例子中，我们有五个。RAND() 函数返回一个小数，FLOOR() 函数用于四舍五入。

mysql> source fiverandomnumbers.sql;

mysql> CALL FiveRandomNumbers;
+---------------+
| Random Number |
+---------------+
|             9 |
+---------------+
1 row in set (0.00 sec)

+---------------+
| Random Number |
+---------------+
|             1 |
+---------------+
...

我们使用 source 命令创建过程。然后调用它。

查找例程

在 MySQL 中，我们可以使用 SHOW PROCEDURE STATUS 和 SHOW FUNCTION STATUS 来查看数据库中的例程及其特征。

information_schema 数据库中也有一个 ROUTINES 表。我们可以查询该表以获取有关存储例程的信息。

mysql> SELECT SPECIFIC_NAME from information_schema.ROUTINES  
    -> WHERE ROUTINE_TYPE='PROCEDURE';
+-------------------+
| SPECIFIC_NAME     |
+-------------------+
| AllCars           |
| FiveRandomNumbers |
| Pow               |
+-------------------+

此语句显示数据库中的所有过程。

mysql> SELECT SPECIFIC_NAME from information_schema.ROUTINES 
    -> WHERE ROUTINE_TYPE='FUNCTION';
+---------------+
| SPECIFIC_NAME |
+---------------+
| CircleArea    |
+---------------+

此语句显示数据库中的所有函数。

在本章中，我们介绍了 MySQL 例程。

电子书

MySQL 存储例程

一个简单的过程

一个简单的函数

过程参数

随机数

查找例程