專欄作者新書出版：Android App程式開發剖析 第三版（適用Android 8 Oreo與Android Studio 3）

MySQL 超新手入門（12）Prepared Statement << 前情

1 Stored Routines的應用

在資料庫管理系統的應用中，不論是一般或網頁的應用程式，它們在執行資料查詢與維護的時候，都必須使用SQL敘述來請資料庫執行各種不同的工作。在比較複雜的應用程式需求下，很常會遇到類似下列的一組工作：

SQL敘述的特點是一次只能執行一件工作，所以要完成上列的工作，就必須執行數個SQL敘述。如果這樣的一組工作是很常執行的，你就可以考慮把這些要執行的敘述建立為「Stored procedure」元件：

把這一組工作建立為Stored procedure元件以後，以後要執行這些工作時，就可以「呼叫、call」這個建立好的Stored procedure元件：

要建立人口數比「USA」多的國家表格時，只要傳入指定的國家代碼就可以了：

Stored procedures是Stored routines其中一種元件，你可以視需要在資料庫中建立許多不同用途的Stored procedure。它可以包含你需要執行的一組工作，也可以依照需求設定必要的參數資料(例如上列「new_mycountry」中的國家代碼)。呼叫這些建立好的Stored procedure可以幫你省掉很多繁複的工作，請資料庫一次完成你要執行的工作。

Stored routines另外提供一種「Stored functions」元件，除了MySQL資料庫提供許多各種不同的函式外，你也可以建立自己的函式，這種函式稱為Stored functions。例如下列的範例：

你可以自己建立一個名稱為「ROUND2」的Stored functions，這個函式固定將一個指定的數值四捨五入到小數兩位：

建立好需要的Stored function元件以後，它使用起來就跟你在使用MySQL提供的函式一樣：

你同樣可以在資料庫中建立許多需要的Stored functions，把一些比較複雜工作建立為Stored functions元件以後，你就可以跟使用MySQL提供的函式一樣來使用它們，同樣可以簡化許多繁複的工作。

在MySQL資料庫管理系統中，把Stored procedures與Stored functions合稱為「Stored routines」。在後續的內容中，會把Stored procedures簡稱為「procedures」；把Stored functions簡稱為「functions」。

1.1 Stored Procedures介紹

Stored procedures元件也是一種可以建立、維護與刪除的資料庫元件。表格元件是用來儲存資料用的；索引元件是儲存索引與增加效率用的；而Stored procedures元件是用來「儲存程序」用的，程序表示一組特定的工作，如果在使用資料的過程中，常常需要執行一組同樣的工作，你就可以考慮把執行工作需要的敘述建立為Stored procedures元件。

下列是建立Stored procedures元件的基本語法：

下列是刪除Stored procedures元件的基本語法：

下列是呼叫Stored procedures元件的基本語法：

1.2 Stored Functions介紹

如果MySQL提供的函式無法完成你的工作，或是想要改善一些比較複雜的敘述，你都可以建立需要的Sotred functions元件。跟Stored procedures一樣，它也是一種用來「儲存程序」的元件，不過建立好的Stored procedures元件要使用「CALL」來呼叫，也就是請資料庫執行儲存在Stored procedures中的工作；要使用建立好的Stored functions元件，就跟使用MySQL提供的函式一樣來使用它們。

下列是建立Stored functions元件的基本語法：

下列是刪除Stored functions元件的基本語法：

2 在MySQL Workbench中管理Stored routines

Stored routines元件中可以包含許多要執行的SQL敘述，在後續的討論中，它也可以包含宣告與設定變數，和控制執行流程的指令。所以Stored routines元件其實就有一點類似開發應用程式用的程式語言，不過它不會像程式語言那麼複雜，而且大部份都是跟資料庫相關的SQL敘述。

2.1 SQL Script、DELIMITER與Stored routines

建立需要的Stored routines元件要使用「CREATE PROCEDURE」或「CREATE FUNCTION」敘述，雖然它們跟其它的SQL敘述一樣，也是請資料庫執行一件工作，不過Stored routines通常會包含許多需要的敘述，所以通常會使用「SQL script」來執行建立Stored routines的工作。

SQL script是一個包含許多SQL敘述的檔案，你可以把想要執行的SQL敘述都集中在一個檔案中。以建立課程範例資料庫的「cmdev.sql」檔案來說，它的內容會像這樣：

MySQL使用分號作為預設的delimiter，delimiter在SQL script檔案中的使用是很重要的，MySQL在執行檔案中的敘述時，是以delimiter來分辨一個SQL敘述的範圍。MySQL提供「DELIMITER」指令，可以修改預設的delimiter符號：

在一般的應用時，你通常不會去修改預設的delimiter符號；可是在建立Stored routines元件的SQL script檔案中就一定要使用了。下列是建立Stored procedure元件的基本內容：

在「MySQL Workbench」中選擇功能表「File > New Query Tab」，接下來就可以輸入下列建立procedure的敘述：

完成建立procedure的敘述後，要執行這個敘述來建立需要的procedure元件：

上列範例所建立的「show_countries」procedure元件中，只有包含一個查詢國家資料的敘述，如果一個procedure元件執行的工作只是這樣的話，應該就不需要建立procedure元件了。所以procedure元件通常會包含許多要執行的敘述，這時候就一定要使用「BEGIN」與「END」。下列是建立包含多個敘述Stored procedure元件的基本內容：

以下列的「my_world_count」procedure元件來說，它可以一次查詢國家、語言與城市三個表格的數量：

使用SQL script建立functions同樣要使用「DELIMITER」關鍵字設定delimiter。「CREATE FUNCTION」的語法另外包含「RETURNS」與「RETURN」兩個關鍵字。下列是建立Stored functions的基本內容：

以下列的「my_date」Stored function來說，它會傳回「年/月/日 時:分:秒 星期」格式的日期時間資料：

如果function元件包含許多要執行的敘述，也一定要使用「BEGIN」與「END」。下列是建立包含多個敘述Stored functions元件的基本內容：

下列建立「my_date2」Stored function的敘述中，因為包含多個敘述，所以一定要使用「BEGIN」與「END」：

註：在Stored routines中使用「DECLARE」與「SET」在「Sotred Routines的變數與流程」中討論。

2.2 管理Stored Procedures

除了使用SQL script建立需要的Stored Procedures外，你也可以使用「MySQL Workbench」提供的功能來管理Stored Procedures。以建立procedure元件來說，在「Stored Procedures」目錄上按滑鼠右鍵後選擇「Create Stored Procedure…」：

MySQL Workbench會幫你準備一個建立procedure元件的樣版，你只要在「BEGIN」與「END」之間，輸入這個procedure元件需要執行的敘述，再選擇「Apply」按鈕：

在確認的視窗選擇「Apply」按鈕：

最後選擇「Finish」就可以建立指定的Stored Procedure：

在建立好的Stored Procedure上按滑鼠右鍵後選擇「Alter Stored Procedure…」和「Drop Stored Procedure…」可以修改或刪除指定的Stored Procedure。

2.3 管理Stored Functions

你也可以使用「MySQL Workbench」提供的功能來管理Stored functions。以建立function元件來說，在「Functions」目錄上按滑鼠右鍵後選擇「Create Function…」：

MySQL Workbench會幫你準備一個建立Function元件的樣版，輸入這個Function的內容後，再選擇「Apply」按鈕：

在確認的視窗選擇「Apply」按鈕：

最後選擇「Finish」就可以建立指定的Function：

在建立好的Function上按滑鼠右鍵後選擇「Alter Function…」和「Drop Function…」可以修改或刪除指定的Function。

3 Stored Routines的參數

Stored routines可以使用參數(parameters)讓使用者傳送資料給stored routines使用，procedures與functions都可以依照需要決定參數的個數與型態。

3.1 Stored Functions的參數

Functions參數的決定會比procedures簡單，因為functions的參數只是用來接收資料後，在functions中使用。你必須決定每一個參數的名稱和型態，再依照想要的順序定義在functions中：

以下列一個合計功能的function來說，它需要兩個「INT」型態的整數參數：

在呼叫「my_summary」的時候，依照參數的定義，指定兩個要合計的整數數值，這個function會將兩個傳入的整數數值加起來後回傳給你：

在呼叫function的時候，一定要依照參數的定義，傳送正確個數的參數資料：

除了參數的個數外，你也要遵守參數型態的規定：

一個function的定義不一定需要參數，以下列的範例來說，呼叫「my_date」時並不需要傳送任何參數資料，不過無論是否需要參數，在呼叫function時，名稱後面的左右刮號是不可以省略的：

3.2 Stored Procedures的參數

Procedures參數的定義與functions大致上相同，除了必須決定每一個參數的名稱、型態與順序，你還需要決定每一個參數的用途：

下列是參數用途的說明：

• IN：「輸入、input」用的參數。這種參數與functions中的參數完全一樣，在呼叫procedures時傳送資料給procedures用的
• OUT：「輸出、output」用的參數。在呼叫procedures時，不能接收傳送的資料，不過在執行procedures時，可以設定這類參數的值，新的值在執行完成後，可以回傳給呼叫的地方使用
• INOUT：「輸入與輸出、input與output」用的參數。同時具有「IN」與「OUT」兩種用途

下列是一個說明三種用途參數的範例：

呼叫procedures時要依照定義的參數個數與型態來傳送資料：

在呼叫Procedures時傳送的參數資料，會因為不同的用途而有不同的限制：

如果違反參數用途上的規定就會發生錯誤：

所以在呼叫procedures時，「OUT」與「INOUT」參數必須指定變數名稱，這是因為「OUT」與「INOUT」參數在執行完成後會回傳資料，使用變數名稱才可以接收procedures回傳的資料：

執行procedures以後，指定給「OUT」與「INOUT」的變數名稱，就會儲存procedures中設定的值：

如果在呼叫procedures之前，先把參數資料設定為使用者變數，再把它們指定給參數使用：

執行上列呼叫procedures的敘述後，你可以發現設定為「OUT」用途的參數是不能接收參數資料的；而下列查詢使用者變數的敘述，可以發現設定為「IN」用途的參數沒有回傳資料的功能：

以下列的範例來說，呼叫「country_count」需要一個洲名的參數，執行以後，它會使用你的洲名執行查詢國家的數量。這個洲名參數的需求，只是用來設定查詢條件用的，並不需要回傳資料，所以這樣的參數適合設定為「IN」：

下列的範例先設定好一個使用者變數儲存洲名，再呼叫「country_count」：

在procedures與functions中，MySQL提供一種特別的查詢敘述。一般的查詢敘述是用來回傳需要的資料用的，而這種查詢敘述可以把「SELECT」子句中指定的資料指定給變數：

以下列的範例來說，呼叫「country_count2」需要一個洲名的參數，它會使用你的洲名執行查詢國家的數量，不過執行以後，它會回傳國家的數量給你。所以這個procedure需要第二個參數用來回傳國家的數量，以這樣的參數需求，國家的數量的參數適合設定為「OUT」：

呼叫「country_count2」時要提供洲名與接收國家數量的變數名稱，在procedure執行以後，使用者變數「my_count」就會儲存國家數量了：

後續 >> MySQL 超新手入門（14）Stored Routines 的變數與流程