全部教學 » MySQL儲存過程引數

MySQL儲存過程引數

2019-10-16 22:56:23

在本教學中，您將學習如何編寫具有引數的MySQL儲存過程。還將通過幾個儲存過程範例來了解不同型別的引數。

MySQL儲存過程引數簡介

在現實應用中，開發的儲存過程幾乎都需要引數。這些引數使儲存過程更加靈活和有用。 在MySQL中，引數有三種模式：IN，OUT或INOUT。

  • IN - 是預設模式。在儲存過程中定義IN引數時，呼叫程式必須將引數傳遞給儲存過程。 另外，IN引數的值被保護。這意味著即使在儲存過程中更改了IN引數的值，在儲存過程結束後仍保留其原始值。換句話說，儲存過程只使用IN引數的副本。
  • OUT - 可以在儲存過程中更改OUT引數的值，並將其更改後新值傳遞回撥用程式。請注意，儲存過程在啟動時無法存取OUT引數的初始值。
  • INOUT - INOUT引數是IN和OUT引數的組合。這意味著呼叫程式可以傳遞引數，並且儲存過程可以修改INOUT引數並將新值傳遞回撥用程式。

在儲存過程中定義引數的語法如下：

MODE param_name param_type(param_size)

上面語法說明如下 -

  • 根據儲存過程中引數的目的，MODE可以是IN，OUT或INOUT。
  • param_name是引數的名稱。引數的名稱必須遵循MySQL中列名的命名規則。
  • 在引數名之後是它的資料型別和大小。和變數一樣，引數的資料型別可以是任何有效的MySQL資料型別。

如果儲存過程有多個引數，則每個引數由逗號(,)分隔。

讓我們練習一些例子來更好的理解。 我們將使用範例資料庫(yiibaidb)中的表進行演示。

MySQL儲存過程引數範例

1.IN引數範例

以下範例說明如何使用GetOfficeByCountry儲存過程中的IN引數來查詢選擇位於特定國家/地區的辦公室。

USE `yiibaidb`;
DROP procedure IF EXISTS `GetOfficeByCountry`;

DELIMITER $$
USE `yiibaidb`$$
CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))
 BEGIN
 SELECT * 
 FROM offices
 WHERE country = countryName;
 END$$

DELIMITER ;

countryName是儲存過程的IN引數。在儲存過程中，我們查詢位於countryName引數指定的國家/地區的所有辦公室。

假設我們想要查詢在美國(USA)的所有辦事處，我們只需要將一個值(USA)傳遞給儲存過程，如下所示：

CALL GetOfficeByCountry('USA');

執行上面查詢語句，得到以下結果 -

要在法國獲得所有辦事處，我們將France字串傳遞給GetOfficeByCountry儲存過程，如下所示：

CALL GetOfficeByCountry('France')

2.OUT引數範例

以下儲存過程通過訂單狀態返回訂單數量。它有兩個引數：

  • orderStatus：IN引數，它是要對訂單計數的訂單狀態。
  • total：儲存指定訂單狀態的訂單數量的OUT引數。

以下是CountOrderByStatus儲存過程的原始碼。

USE `yiibaidb`;
DROP procedure IF EXISTS `CountOrderByStatus`;

DELIMITER $$
CREATE PROCEDURE CountOrderByStatus(
 IN orderStatus VARCHAR(25),
 OUT total INT)
BEGIN
 SELECT count(orderNumber)
 INTO total
 FROM orders
 WHERE status = orderStatus;
END$$
DELIMITER ;

要獲取發貨訂單的數量，我們呼叫CountOrderByStatus儲存過程，並將訂單狀態傳遞為已發貨，並傳遞引數(@total)以獲取返回值。

CALL CountOrderByStatus('Shipped',@total);
SELECT @total;

執行上面查詢語句後，得到以下結果 - 

+--------+
| @total |
+--------+
|    303 |
+--------+
1 row in set

要獲取正在處理的訂單數量，呼叫CountOrderByStatus儲存過程，如下所示：

執行上面查詢語句後，得到以下結果 - 

+------------------+
| total_in_process |
+------------------+
|                7 |
+------------------+
1 row in set

INOUT引數範例

以下範例演示如何在儲存過程中使用INOUT引數。如下查詢語句 - 

DELIMITER $$
CREATE PROCEDURE set_counter(INOUT count INT(4),IN inc INT(4))
BEGIN
 SET count = count + inc;
END$$
DELIMITER ;

上面查詢語句是如何執行的？

  • set_counter儲存過程接受一個INOUT引數(count)和一個IN引數(inc)。
  • 在儲存過程中，通過inc引數的值增加計數器(count)。

下面來看看如何呼叫set_counter儲存過程：

SET @counter = 1;
CALL set_counter(@counter,1); -- 2
CALL set_counter(@counter,1); -- 3
CALL set_counter(@counter,5); -- 8
SELECT @counter; -- 8

在本教學中，我們向您展示了如何在儲存過程中定義引數，並介紹了不同的引數模式：IN，OUT和INOUT。