MySQL 存储过程 · ZetCode 中文系列教程

# MySQL 存储过程 > 原文： [http://zetcode.com/databases/mysqltutorial/routines/](http://zetcode.com/databases/mysqltutorial/routines/) 本章介绍 MySQL 中的存储过程。在 MySQL 中，有两种存储过程：存储过程和存储函数。使用`CALL`语句调用存储过程。它们不返回值。存储的函数返回值。与`SELECT`语句一起使用。存储的过程是一组可以存储在服务器中的 SQL 语句。通常不接受存储的过程。它们既有优点也有缺点。存储的过程通常用于数据验证或访问控制。在存在许多用不同语言编写或在不同平台上工作的客户端应用但需要执行相同数据库操作的情况下，存储过程可能会很有用。它们可以导致一些性能提升。存储的过程存储在服务器中，因此网络负载减少。在某些数据库系统中，可以对存储的过程进行预编译，从而提高性能。如果更改数据库上的某些逻辑，则它会自动为所有可能的客户端准备。当我们在客户端更改某些逻辑时，必须在所有可能的客户端中执行此操作。另一方面，存储的过程有一些缺点。存储的过程违反了主要的设计模式，在该模式中，业务逻辑，数据和表示在特定的层中分开。存储的过程会破坏业务逻辑和数据。存储的过程更难以调试和测试。在存储的过程中具有大量业务逻辑的应用可伸缩性较差。而且，没有用于存储过程的版本控制系统。最后，在各种数据库系统中，存储过程的实现方式有所不同。这使得数据库系统之间的潜在迁移更加困难。 ## 一个简单的过程该过程是使用`CREATE PROCEDURE`语句创建的。 ```sql mysql> CREATE PROCEDURE AllCars() SELECT * FROM Cars; ``` 在此语句中，我们创建了一个名为`AllCars()`的新简单过程。过程名称后面的`select`语句是过程的主体，当我们调用过程时会执行该主体。该过程从`Cars`表中选择所有数据。 ```sql 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`语句调用它。 ```sql -- 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`命令读取该文件。 ```sql -- this function computes the area -- of a circle; it takes a radius as -- a parameter ``` 注释以双破折号开头。 ```sql DELIMITER $$ ``` SQL 语句以分号结束。要创建过程或函数，我们需要多个语句。因此，我们需要暂时使用其他定界符。在这里，我们使用`$$`作为分隔符。我们可以使用不同的字符。在函数定义的末尾，我们使用此定界符。 ```sql DROP FUNCTION IF EXISTS CircleArea; ``` 在开发存储过程时，我们将遇到各种语法或其他错误。该函数可能已经部分创建。因此，我们使用上面的语句来消除任何有缺陷的尝试，并从一开始就创建一个函数。 ```sql CREATE FUNCTION CircleArea(r DOUBLE) RETURNS DOUBLE ``` 我们创建一个名为`CircleArea`的函数。它采用类型为`DOUBLE`的参数`r`。该函数返回`DOUBLE`类型的值。 ```sql BEGIN ... END ``` 函数主体位于`BEGIN`和`END`关键字之间。 ```sql DECLARE area DOUBLE; ``` 我们在过程中声明一个新变量。它的名称是`area`，数据类型是`DOUBLE`。 ```sql SET area = r * r * pi(); ``` 我们用给定的半径计算圆的面积。 ```sql RETURN area; ``` 我们返回变量。 ```sql $$ ``` 过程到此结束。 ```sql DELIMITER ; ``` 我们再次使用默认的定界符。 ```sql 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`参数的混合。可以将其传递给过程，在此进行更改，也可以在过程外部进行检索。 ```sql -- 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 ; ``` 在此过程中，我们计算给定值的功效。 ```sql CREATE PROCEDURE Pow(IN val DOUBLE, OUT p DOUBLE) ``` 该过程采用两个参数。第一个是计算功效的值。声明为`IN`。它被传递到过程并在那里使用。第二个变量是`OUT`变量。这是我们存储此过程结果的参数。过程完成后即可使用。 ```sql mysql> source power.sql mysql> CALL Pow(3, @p); mysql> SELECT @p; +------+ | @p | +------+ | 9 | +------+ ``` 我们创建过程`Pow()`。我们使用`CALL`语句来调用它。结果存储在`@p`变量中。最后，我们选择`@p`变量以查看其内容。 ## 随机数在下面的示例中，我们将创建一个生成五个随机数的过程。从 0 到 9。 ```sql -- 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()`内置函数。 ```sql SET @i = 0; ``` 此变量是一个计数器。 ```sql REPEAT SELECT FLOOR(RAND() * 10) AS 'Random Number'; SET @i = @i + 1; UNTIL @i >=5 END REPEAT; ``` 关键字`REPEAT`和`UNTIL`创建一个循环。计数器用于控制迭代次数。就我们而言，我们有五个。 `RAND()`函数返回一个十进制数字，`FLOOR()`函数用于将其舍入。 ```sql 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`表。我们可以查询表以获取有关存储过程的信息。 ```sql mysql> SELECT SPECIFIC_NAME from information_schema.ROUTINES -> WHERE ROUTINE_TYPE='PROCEDURE'; +-------------------+ | SPECIFIC_NAME | +-------------------+ | AllCars | | FiveRandomNumbers | | Pow | +-------------------+ ``` 该语句显示数据库中的所有过程。 ```sql mysql> SELECT SPECIFIC_NAME from information_schema.ROUTINES -> WHERE ROUTINE_TYPE='FUNCTION'; +---------------+ | SPECIFIC_NAME | +---------------+ | CircleArea | +---------------+ ``` 该语句显示数据库中的所有函数。在本章中，我们介绍了 MySQL 过程。