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