MySQL 存储过程|极客教程

本章介绍 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是默认参数类型。未明确指定类型时使用。 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    |
+---------------+