MySQL中的存储事务(第十八课-全部完结)

MySQL stored routines

MySQL存储事务

This chapter introduces stored routines in MySQL. In MySQL there are two kinds of stored routines: stored procedures and stored functions.

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

Stored procedures are called with the CALL statement. They do not return values. Stored functions return values. And are used with the SELECT statement.

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

A stored routine is a set of SQL statements that can be stored in the server. Stored routines are not generally accepted. They have some advantages but also several disadvantages. Stored routines are typically used in data validation or access control.

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

Stored procedures may be useful in situations, where there are many client applications written in different languages or work on different platforms, but need to perform the same database operations. They can lead to some performance gains. Stored routines are stored in the server and so the network load decreases. In some database systems the stored routines can be precompiled, which increases the performance. If you change some logic on the database, it is automatically ready for all possible clients. When we change some logic on the client side, this must be done in all possible clients.

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

On the other hand, stored routines have some drawbacks. Stored routines violate the principal design pattern, where business logic, data and presentation are separated in specific tiers. Stored routines mangle business logic with data. Stored routines are more difficult to debug and test. The application that has a lot of business logic in stored routines is less scalable. Moreover, there is no version control system for stored routines. Finally, stored routines are implemented differently in various database systems. This makes potential migration between database systems more difficult.

另一方面，存储的例程有一些缺点。存储的例程违反了主要设计模式，在该模式中，业务逻辑，数据和表示在特定的层中分开。存储的例程会破坏业务逻辑和数据。存储的例程更难以调试和测试。在存储的例程中具有大量业务逻辑的应用程序可伸缩性较差。而且，没有用于存储例程的版本控制系统。最后，在各种数据库系统中，存储例程的实现方式有所不同。这使得数据库系统之间的潜在迁移更加困难。

A simple procedure

一个简单的过程

The procedure is created with a CREATE PROCEDURE statement.

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

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

In this statement, we have created a new simple procedure called AllCars(). The select statement following the name of the procedure is the body of the procedure, which is executed when we call the procedure. The procedure selects all data from the Cars table.

<code>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 |
+----+------------+--------+
/<code>

We call the AllCars() procedure and its body is executed.

我们调用AllCars（）过程，并执行其主体.

A simple function

一个简单的函数

A function is created with a CREATE FUNCTION statement. A function returns a value. It is called with a SELECT statement.

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

<code> 
 
 

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 ;
/<code>

In this code, we create a CircleArea() function which computes the area of a circle. It takes a radius as a parameter. The best way to create a procedure or a function that has more than one line is to create an SQL file and read the file with the source command.

在此代码中，我们创建了一个CircleArea（）函数来计算圆的面积。 它以半径为参数。 创建具有多个行的过程或函数的最佳方法是创建一个SQL文件并使用source命令读取该文件.

<code> 
 
 
/<code>

Comments begin with double dashes.

注释以双破折号开头。

<code>
DELIMITER
 $$
/<code>

SQL statements are finished with a semicolon. To create a procedure or a function we need multiple statements. Because of this, we need to temporarily use a different delimiter. Here we have used $$ as a delimiter. We could use different characters. At the end of the function definition, we use this delimiter.

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

<code>
DROP
 
FUNCTION
 
IF 
 
EXISTS
 CircleArea;
/<code>

When developing our stored routines, we will run into various syntax or other errors. The function may be already partially created. Therefore we use the above statement to erase any of our flawed attempts and create a function from the beginning.

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

<code>
CREATE
 
FUNCTION
 CircleArea(r 
DOUBLE
) 
RETURNS
 
DOUBLE
/<code>

We create a function called CircleArea. It takes a parameter r of type DOUBLE. The function returns a value of type DOUBLE.

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

<code>
BEGIN
   ...
END
 
/<code>

The function body is placed between the BEGIN and END keywords.

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

<code>
DECLARE
 area 
DOUBLE
;
/<code>

We declare a new variable in the routine. Its name is area and data type is DOUBLE.

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

<code>
SET
 area = r * r * 
pi
();
/<code>

We compute the area of the circle with the given radius.

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

<code>
RETURN
 area;
/<code>

We return the variable.

我们返回变量。

<code>$$
/<code>

Here is the end of the routine.

例程到此结束.

<code>
DELIMITER 
 ;
/<code>

We use again the default delimiter.

我们再次使用默认的定界符。

<code>mysql> source circlearea.sql

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

We create the CircleArea() function and call it with the SELECT statement.

我们创建CircleArea（）函数，并使用SELECT语句调用它。

Procedure parameters

程序参数

A procedure cannot return a value. However, it can work with three types of variables:

程序无法返回值。 但是，它可以使用三种类型的变量：

• IN
• OUT
• INOUT

The IN is the default type of parameter. It is used when no type is specified explicitly. The IN parameter is passed to the procedure. It can be modified inside the procedure, but it remains unchanged outside. In the case of the OUT parameter no value is passed to the procedure. It can be modified inside the procedure. And the variable is available outside the procedure. The INOUT variable is the blending of the both IN and OUT parameters. It can be passed to the procedure, changed there and can be retrieved outside the procedure.

<code> 
 

DELIMITER $$

DROP
 
PROCEDURE
 
IF
 
EXISTS
 
Pow
;

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

$$

DELIMITER ;
/<code>

In this procedure, we compute the power of a given value.

在此过程中，我们计算幂值.

<code>
CREATE
 
PROCEDURE
 
Pow
(
IN
 val 
DOUBLE
, 
OUT
 p 
DOUBLE
) 
/<code>

The procedure takes two parameters. The first is the value to compute the power. It is declared to be IN. It is passed to the routine and used there. The second variable is an OUT variable. It is the parameter where we store the result of this procedure. It can be used after the routine has finished.

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

<code>
mysql
> 
source
 
power
.sql

mysql
> 
CALL
 
Pow
(
3 
, 
@p
);

mysql
> 
SELECT
 @
p
;
+
------
+
| @
p
   |
+
------
+
|    
9
 |
+
------
+
/<code>

We create the procedure Pow(). We call it using the CALL statement. The result is stored in the @p variable. Finally, we select the @p variable to see its content.

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

Random numbers

随机数

In the following example, we will create a procedure which produces five random numbers. From 0 to 9.

在下面的示例中，我们将创建一个生成five random numbers的过程。 从0到9。

<code>  
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
 
;
/<code>

In this procedure, we will use RAND() and FLOOR() built-in functions.

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

<code>
SET
 
@i
 = 
0
;
/<code>

This variable is a counter.

此变量是一个计数器。

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

The REPEAT, UNTIL keywords create a loop. The counter is used to control the number of iterations. In our case, we have five. The RAND() function returns a decimal number and the FLOOR() function is used to round it.

REPEAT，UNTIL关键字创建一个循环。 计数器用于控制迭代次数。 在我们的情况下，我们有五个。 RAND（）函数返回一个十进制数，而FLOOR（）函数用于将其舍入。

<code>mysql> source fiverandomnumbers.sql;

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

+---------------+
| Random Number |
+---------------+
|             1 |
+---------------+
...
/<code>

We create the procedure using the source command. And then call it.

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

Finding routines

查找例程

In MySQL, we can use SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS to see routines and their characteristics in our database.

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

There is also a ROUTINES table in the information_schema database. We can query the table for information about stored routines.

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

<code>mysql> SELECT SPECIFIC_NAME from information_schema.ROUTINES  
    -> WHERE ROUTINE_TYPE=
'PROCEDURE'
;
+-------------------+
| SPECIFIC_NAME     | 

+-------------------+
| AllCars           |
| FiveRandomNumbers |
| Pow               |
+-------------------+
/<code>

This statement shows all procedures in the database.

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

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

This statement shows all functions in the database.

In this chapter, we covered MySQL routines

该语句显示数据库中的所有功能。

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

全部完结，感谢你的学习，希望能够对你的工作有所帮助.

關鍵字: function stored 语句