[MySQL] 如何接收 Store Procedure 的傳回值

2009-07-05

41569
0
C#

延續上一篇文章 [SQL Server] 如何接收 Store Procedure 的傳回值，這次來說明接收 MySQL 傳回值的部份。

Store Procedure 要將資料傳回一般有 2 種方式：
1. 使用 SELECT 以表格方式傳回。
2. 設定 Output Parameter 以參數方式傳回。

因為 MySQL 預儲程序不支援 RETURN 的方法，若要使用 RETURN 傳回資料可使用 FUNCTION 來傳回。
Function 傳回方式：
1. 使用 RETURN 傳回單一資料。
以下列出這 3 種傳回方式的接收方法...

延續上一篇文章 [SQL Server] 如何接收 Store Procedure 的傳回值，這次來說明接收 MySQL 傳回值的部份。

Store Procedure 要將資料傳回一般有 2 種方式：

使用 SELECT 以表格方式傳回。
設定 Output Parameter 以參數方式傳回。

因為 MySQL 預儲程序不支援 RETURN 的方法，若要使用 RETURN 傳回資料可使用 FUNCTION 來傳回。

Function 傳回方式：

使用 RETURN 傳回單一資料。

以下列出這 3 種傳回方式的接收方法，而程式在進行資料庫連線時是使用 Connector/Net 5.2 版的元件進行連線。

一、使用 SELECT 以表格方式傳回

Store Procedure：

view plain copy to clipboard print ?

DROP PROCEDURE IF EXISTS mysp_QryData;
CREATE PROCEDURE mysp_QryData (
id int
)
BEGIN
SELECT LastName, FirstName
FROM Customer
WHERE CustomerID = id;
END;

view plain copy to clipboard print ?

MySqlConnection conn = new MySqlConnection(strConn);
MySqlCommand cmd = new MySqlCommand(string.Empty, conn);
conn.Open();
cmd.Parameters.Clear();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "mysp_QryData";
cmd.Parameters.Add("?id", MySqlDbType.Int32).Value = 1;
MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (reader.HasRows)
{
reader.Read();
Console.WriteLine("Last Name：" + reader["LastName"]);
Console.WriteLine("First Name：" + reader["FirstName"]);
}
reader.Close();
reader.Dispose();
conn.Close();
conn.Dispose();

此例是以 ExecuteReader() 來進行讀取，然後配合 SqlDataReader 物件將欄位值讀出。

因傳回的內容只有 1 個資料集，所以第 10 行在讀取時可以使用 CommandBehavior.SingleRow 來接收傳回的資料集，可結省資源及增進效率，但要注意，若是傳回的內容有 2 個資料集以上，那麼使用 CommandBehavior.SingleRow 就無法讀到第 2 個以後的資料集內容了。

假設傳回內容只有 1 個傳回值，或是只要讀取傳回的第 1 筆資料的第 1 個欄位值，可以使用 ExecuteScalar() 將 LastName 讀出，如此上面 10～20 行的程式可改為如下：

view plain copy to clipboard print ?

string strName = Convert.ToString(cmd.ExecuteScalar());
Console.WriteLine("Last Name：" + strName);

二、設定 Output Parameter 以參數方式傳回

Store Procedure：

view plain copy to clipboard print ?

CREATE PROCEDURE mysp_QryData2 (
id int,
out lname varchar(30)
)
BEGIN
SELECT LastName INTO lname
FROM Customer
WHERE CustomerID = id;
END;

關鍵在第 3 行，在參數前方加上 out 設定為輸出參數 (註：SQL Server 是在參數後方加上 output)。

在另一支預儲程序中要接收此預儲程序傳回的輸出參數方法如下：

view plain copy to clipboard print ?

CALL mysp_QryData2(1, @lname);
SELECT @lname;

第 1 行：在執行預儲程序後方直接指定要接收傳回值的變數即可。

MySqlConnection conn = new MySqlConnection(strConn); MySqlCommand cmd = new MySqlCommand(string.Empty, conn); conn.Open(); cmd.Parameters.Clear(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "mysp_QryData2"; cmd.Parameters.Add("?id", MySqlDbType.Int32).Value = 1; MySqlParameter lname = cmd.Parameters.Add("?lname", MySqlDbType.VarChar, 30); lname.Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); Console.WriteLine("Last Name：" + lname.Value); conn.Close(); conn.Dispose();

第 10 行：建立要接收輸出的參數名稱資料，參數名稱 ?lname 須與預儲程序中的參數名稱一樣。
第 11 行：設定為輸出參數。
第 15 行：用 .value 將傳回資料取出。

三、Function 使用 RETURN 傳回單一數值

因為 MySQL 預儲程序不支援 RETURN 的方法，後來我測試用 FUNCTION 使用 RETURN 傳回資料，而 .NET 程式使用 CommandType = StoredProcedure 並設定 RETURN 參數可成功接收，程式碼如下。

MySQL Function：

DROP FUNCTION IF EXISTS func_QryData3; CREATE FUNCTION func_QryData3 ( id int ) RETURNS varchar(30) BEGIN DECLARE lname varchar(30); SELECT LastName INTO lname FROM Customer WHERE CustomerID = id; RETURN lname; END;

將資料讀出後，使用 RETURN 傳回資料。

在 MySQL 中呼叫 Function 將值接回的寫法：

view plain copy to clipboard print ?

SET @lname = func_QryData3(1);
SELECT @lname;

MySqlConnection conn = new MySqlConnection(strConn); MySqlCommand cmd = new MySqlCommand(string.Empty, conn); conn.Open(); cmd.Parameters.Clear(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "func_QryData3"; cmd.Parameters.Add("?id", MySqlDbType.Int32).Value =2; MySqlParameter lname = cmd.Parameters.Add("?ReturnValue", MySqlDbType.VarChar, 30); lname.Direction = ParameterDirection.ReturnValue; cmd.ExecuteNonQuery(); Console.WriteLine("Last Name：" + lname.Value); conn.Close(); conn.Dispose();

第 6 行：雖然程式是呼叫 Function，但可設定成 CommandType = StoredProcedure 來呼叫。
第 10 行：建立要接收 RETURN 的參數，參數名稱 ?ReturnValue 可自定。
第 11 行：將參數設定為 Return Value。
第 15 行：用 .value 將傳回資料取出。

參考資料：

回首頁