mysql存儲過程參數輸入輸出IN/OUT/INOUT

mysql存儲過程中，​

​IN​

​​用來輸入參數，​

​OUT​

​​用來輸出參數，​

​INOUT​

​輸入和輸出。

1. IN參數

   建立存儲過程

mysql> delimiter //
mysql> create procedure proce1(IN count int)
    -> begin
    -> select count;
    -> set count = 2;
    -> select count;
    -> end
    -> //      

執行存儲過程

mysql> set @count = 1//
Query OK, 0 rows affected (0.00 sec)

mysql> call proce1(@count)//
+-------+
| count |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

+-------+
| count |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @count
    -> //
+--------+
| @count |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

mysql>      

在上面存儲過程中，我們定義了一個參數​

​@count​

​​，指派為1，傳入到存儲過程中，在存儲過程中指派為2，當存儲過程結束後，列印​

​@count​

​的值依舊是1.

1. OUT參數

   建立存儲過程

mysql> create procedure proce_out(OUT count int)
    -> begin
    -> select count;
    -> set count = 2;
    -> select count;
    -> end//
Query OK, 0 rows affected (0.05 sec)      

執行存儲過程

mysql> set @count=1;
    -> call proce_out(@count);
    -> select @count;
    -> //
Query OK, 0 rows affected (0.00 sec)

+-------+
| count |
+-------+
|  NULL |
+-------+
1 row in set (0.00 sec)

+-------+
| count |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+--------+
| @count |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

mysql>      

​

​@count​

​​在外面指派為1，因為參數是​

​OUT​

​​類型，是以存儲過程中列印出來為​

​null​

​​，存儲過程中指派為2，存儲過程結束，檢視​

​@count​

​的值，依舊為2。

1. INOUT 輸入輸出參數

   建立存儲過程

mysql> create procedure proce_inout(INOUT count int)
    -> begin
    -> select count;
    -> set count = 2;
    -> select count;
    -> end;
    -> //      

執行存儲過程

mysql> set @count = 1;
    -> call proce_inout(@count);
    -> select @count;
    -> //
Query OK, 0 rows affected (0.00 sec)

+-------+
| count |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

+-------+
| count |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+--------+
| @count |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)      

我們在外面把​

​@count​

​指派為1，存儲過程裡面擷取到了值，然後指派為2，存儲過程結束後，列印出來的值依舊是2