mysql中的存儲過程傳參問題

更新時間：2023年10月12日 08:57:37 作者：IT_CODE.

這篇文章主要介紹了mysql中的存儲過程傳參問題,具有很好的參考價值,希望對大家有所幫助,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

1.無參數(shù) call 存儲過程名()

DELIMITER $
CREATE   PROCEDURE  select_all_data()
BEGIN 
        SELECT  *  FROM  dog;
 END $
 DELIMITER ;
--  2.存儲過程的調(diào)用       
CALL  select_all_data();
/*
  mysql> CALL  select_all_data();
+------+--------------+------+
| id   | name         | age  |
+------+--------------+------+
|    2 | 旺財小七     | 19   |
+------+--------------+------+
1 row in set (0.00 sec)

2.in類型

PS：如果僅僅想傳給mysql存儲過程，那就用in類型

#創(chuàng)建存儲過程
DELIMITER $$
CREATE PROCEDURE  getUserBySex (IN p_sex VARCHAR(255))
BEGIN
SELECT *  FROM  USER  WHERE sex=p_sex;
SET p_sex = '未知';
SELECT p_sex;
END
$$
DELIMITER;
mysql>  SET  @p_sex="女";   -- 把這個參數(shù)設(shè)置成女，傳入到存儲過程
Query OK, 0 rows affected (0.00 sec)
mysql>   CALL getUserBySex(@p_sex);
+----+-----------+------+------+----------+-------+----------------------+
| id | name      | sex  | age  | password | phone | address              |
+----+-----------+------+------+----------+-------+----------------------+
|  8 | 小綠      | 女   |   34 | 222222   | NULL  | 01王賢達20計網(wǎng)2      |
| 10 | 小影      | 女   |   30 | 444444   | NULL  | 01王賢達20計網(wǎng)2      |
| 11 | 大梅      | 女   |   27 | 555555   | NULL  | 01王賢達20計網(wǎng)2      |
| 12 | 陳光明    | 女   |   27 | 555555   | NULL  | 01王賢達20計網(wǎng)2      |
+----+-----------+------+------+----------+-------+----------------------+
4 rows in set (0.00 sec)
+--------+
| p_sex  |
+--------+
| 未知   |
+--------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)

ps：這里注意：

可以發(fā)現(xiàn)把這個參數(shù)設(shè)置成會話用戶變量女傳進去，并且我們在存儲過程中對這個變量作了修改，里面是改成了性別:未知，但是出了begin and 局部變量，這個變量還是女，因為這參數(shù)設(shè)置是in型，不會返回到會話的用戶變量中。

可以查詢下：

mysql>  SELECT   @p_sex;
+--------+
| @p_sex |
+--------+
| 女     |
+--------+
1 row in set (0.00 sec)

3.out型

如果僅僅從mysql存儲過程返回值，那就用out類型

#  創(chuàng)建存儲過程
DELIMITER $$   
  CREATE  PROCEDURE  p_count ( OUT  p_x INT)
  BEGIN
  SELECT  p_x;
  SET  p_x = 2;
  SELECT  p_x;
  END $$
  DELIMITER ; 
#調(diào)用  CALL p_count(2)
錯誤代碼： 1414
OUT or INOUT argument 1 for routine 01wangxianda.p_count is not a variable or NEW pseudo-variable in BEFORE trigger
#大概意思得傳參數(shù)(變量)

set @x=1;
mysql> set @x=1;
Query OK, 0 rows affected (0.00 sec)
mysql> call p_count(@x);
+------+
| p_x  |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
+------+
| p_x  |
+------+
|    2 |
+------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> select  @x;
+------+
| @x   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)
mysql>

mysql> call  p_count (@s);
+------+
| p_x  |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
+------+
| p_x  |
+------+
|    2 |
+------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql>

ps：這個變量在局部變量里面并沒有實際意思，只是一個變量中轉(zhuǎn)站(大概這樣理解，因為第一個select始終為空，不管你設(shè)置什么變量進去，有值變量也是一樣)

實際應用中用的比較多的是in out 結(jié)合：

例題：用in參數(shù)empname輸入員工姓名，用Out參數(shù)empsalary輸出員工薪資

DELIMITER //
                                            --    這兩都是形參    empanme,empsalary
CREATE  PROCEDURE  show_someone_salary2(IN  empname VARCHAR(20), OUT empsalary  DECIMAL (10,2))
BEGIN 
      SELECT  salary INTO  empsalary
      FROM  employees
      WHERE  last_name = empname;
END //
DELIMITER;

調(diào)用：

mysql> SET @empname = 'Abel';     --  設(shè)置用戶變量并且賦值
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CALL  show_someone_salary2(@empname,@empsalary);  -- 調(diào)用存儲過程傳入?yún)?shù)
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT  @empsalary;    --  查詢輸出變量
+------------+
| @empsalary |
+------------+
|   11000.00 |
+------------+
1 row in set (0.00 sec)

mysql> CALL  show_someone_salary2('Weiss',@隨便什么變量);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT  @隨便什么變量
    -> ;
+---------------------+
| @隨便什么變量       |
+---------------------+
|             8000.00 |
+---------------------+
1 row in set (0.00 sec)

ps：也可以in類型直接傳入需要字段名,不用再外面設(shè)置會話用戶變量，存儲過程自己會接受這值作為局部變量使用，out類型的調(diào)用時一定得傳@變量,因為這個存儲調(diào)用完，out類型變量得輸出

4.inout型

如果需要把數(shù)據(jù)傳給mysql存儲過程，還要經(jīng)過一些計算再回傳我們，此時，要使用inout類型

#創(chuàng)建
DELIMITER  $$
CREATE  PROCEDURE  p_inout(INOUT s_x INT)
BEGIN
SELECT  s_x;
SET s_x=3;
SELECT s_x;
END $$
DELIMITER ;
#調(diào)用存儲過程
mysql> set  @s_x=99;
Query OK, 0 rows affected (0.00 sec)
mysql> call p_inout (@s_x);
+------+
| s_x  |
+------+
|   99 |
+------+
1 row in set (0.00 sec)
+------+
| s_x  |
+------+
|    3 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

ps：注意第一個select 是99

測試：雖然你調(diào)用之前把@s_x用戶會話變量設(shè)置成了99，但是你傳入存儲過程之后set賦值了，并且inout型參數(shù)可以輸出，打印出來影響用戶會話變量，所以打印出依舊是3

mysql> select  @s_x;
+------+
| @s_x |
+------+
|    3 |
+------+
1 row in set (0.00 sec)
mysql>

inout 實際運用

-- 創(chuàng)建存儲過程,查詢某個員工領(lǐng)導的姓名，并用inout類型

-- ‘empname’輸入員工姓名查詢出其領(lǐng)導的名字

 
DELIMITER $
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
BEGIN 
    SELECT manager_id,last_name    -- 上級的id
    FROM  employees
    WHERE employee_id = (    -- 過濾的條件   上級的編號 ==    查出來的編號
        SELECT manager_id FROM employees
        WHERE last_name = empname        -- (傳入一個名字)根據(jù)上級的名字查出上級的編號編號                  
    );
 END $
DELIMITER ;

調(diào)用

mysql> SET @empname := 'Abel';  -- 傳入名字變量
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> SELECT @empname;    -- 查詢這個變量
+----------+
| @empname |
+----------+
| Abel     |
+----------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> CALL show_mgr_name(@empname);  -- 調(diào)用存儲

--測試