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 過程。