MySQL儲存過程輸入引數(in),輸出引數(out),輸入輸出引數(inout)

2022-07-11 22:02:30

什麼是儲存過程？

MySQL 5.0 版本開始支援儲存過程。

簡單的說，儲存過程就是一組SQL語句集，功能強大，可以實現一些比較複雜的邏輯功能，類似於JAVA語言中的方法；Python裡面的函數；

儲存過就是資料庫 SQL 語言層面的程式碼封裝與重用。

特點：

有輸入輸出引數，可以宣告變數，有if/else, case,while等控制語句，通過編寫儲存過程，可以實現複雜的邏輯功能；

函數的普遍特性：模組化，封裝，程式碼複用；

速度快，只有首次執行需經過編譯和優化步驟，後續被呼叫可以直接執行，省去以上步驟；

語法格式

delimiter 自定義結束符號
create procedure 儲存名([ in ,out ,inout ] 引數名 資料類形...)
begin
sql語句
end 自定義的結束符合
delimiter ;

delimiter $$
create procedure proc01()
begin
select empno,ename from emp;
end $$
delimiter ;
-- 呼叫儲存過程
call proc01();

注意建立的時候需要指定delimiter $$（自定義的，但是通常使用這個），然後使用create procedure 儲存名(有輸入引數輸出引數) 然後記住有begin 和end 之間就寫SQL語句，並且需要在end後面加上$$，最後用delimiter ;

區域性變數

使用者自定義，在begin/end塊中有效，也就是相對於在函數內部中宣告變數

語法： 宣告變數 declare var_name type [default var_value];
舉例：declare nickname varchar(32);

關鍵詞：declare 變數名型別（長度）可以加預設值——類似於我們python或者java中的 int a=10；

delimiter $$
create procedure proc02()
begin
declare var_name01 varchar(20) default ‘aaa'; -- 定義區域性變數
set var_name01 = ‘zhangsan';
select var_name01;
end $$
delimiter ;
-- 呼叫儲存過程
call proc02();

定義變數之後，使用set給變數賦值

MySQL 中還可以使用 SELECT..INTO 語句為變數賦值。

其基本語法如下：

select col_name [...] into var_name[,...]
from table_name wehre condition

其中：

col_name 參數列示查詢的欄位名稱；
var_name 引數是變數的名稱；
table_name 引數指表的名稱；
condition 引數指查詢條件。

注意：當將查詢結果賦值給變數時，該查詢語句的返回結果只能是單行單列（單元格值）

delimiter $$
create procedure proc03()
begin
declare my_ename varchar(20) ;
select ename into my_ename from emp where empno=1001;
select my_ename;
end $$
delimiter ;
-- 呼叫儲存過程
call proc03();

如果是在內部宣告的變數，並且進行了賦值之後，那麼在內部呼叫顯示的時候，可以直接select就可以實現展示資料的效果

使用者變數

使用者自定義，當前對談（連線）有效。類比java的成員變數

語法：

@var_name

不需要提前宣告，使用即宣告

delimiter $$
create procedure proc04()
begin
set @var_name01 = 'ZS';
end $$
delimiter;
call proc04() ;
select @var_name01 ; --可以看到結果

這裡使用@進行宣告，前提不需要對其型別進行宣告，直接可以賦值，但是呼叫的時候也需要@變數名

宣告一般有兩種方法：

1、declare 變數名型別（長度）

然後使用set 變數名 = 值接收
或者使用新值 into 變數名

2、直接使用@變數名=？

但是這個在整個對談都有作用，呼叫的時候需要使用@變數名

系統變數

系統變數又分為全域性變數與對談變數

全域性變數在MYSQL啟動的時候由伺服器自動將它們初始化為預設值，這些預設值可以通過更改my.ini這個檔案來更改。

對談變數在每次建立一個新的連線的時候，由MYSQL來初始化。MYSQL會將當前所有全域性變數的值複製一份。來做為對談變數。

也就是說，如果在建立對談以後，沒有手動更改過對談變數與全域性變數的值，那所有這些變數的值都是一樣的。

全域性變數與對談變數的區別就在於，對全域性變數的修改會影響到整個伺服器，但是對對談變數的修改，只會影響到當前的對談（也就是當前的資料庫連線）。

有些系統變數的值是可以利用語句來動態進行更改的，但是有些系統變數的值卻是唯讀的，對於那些可以更改的系統變數，我們可以利用set語句進行更改。

檢視修改全域性變數：

-- 檢視全域性變數
show global variables;
-- 檢視某全域性變數
select @@global.auto_increment_increment;
-- 修改全域性變數的值
set global sort_buffer_size = 40000;
set @@global.sort_buffer_size = 40000;

檢視修改對談變數：

-- 檢視對談變數
show session variables;
-- 檢視某對談變數
select @@session.auto_increment_increment;
-- 修改對談變數的值
set session sort_buffer_size = 50000;
set @@session.sort_buffer_size = 50000 ;

傳入引數

in 表示傳入的引數，可以傳入數值或者變數，即使傳入變數，並不會更改變數的值，可以內部更改，僅僅作用在函數範圍內。

注意：這裡參入引數如果和欄位名一樣的話，一般不要命名一樣的名字，如果不小心命名一樣的，那麼也需要在表中指定，表.欄位

而且需要注意的是宣告輸入引數：in 引數名型別（長度）

-- 封裝有引數的儲存過程，傳入員工編號，查詢員工資訊
delimiter $$
create procedure dec_param01(in param_empno varchar(20))
begin
select * from emp where empno = param_empno;
end $$
delimiter ;
call dec_param01('1001');

-- 封裝有引數的儲存過程，可以通過傳入部門名和薪資，查詢指定部門，並且薪資大於指定值的員工資訊
delimiter $$
create procedure dec_param0x(in dname varchar(50),in sal decimal(7,2),)
begin
select * from dept a, emp b where b.sal > sal and a.dname = dname;
end $$
delimiter ;
call dec_param0x('學工部',20000);

通過上述的案例，我們也可以發現，一般如果傳入引數的時候，是字串就用varchar()，如果是數值就用decimal（），當然有時候也可以使用數值型別，字元型別。

輸出引數

out 表示從儲存過程內部傳值給呼叫者

-- ---------傳出引數：out---------------------------------
use mysql7_procedure;
-- 封裝有引數的儲存過程，傳入員工編號，返回員工名字
delimiter $$
create procedure proc08(in empno int ,out out_ename varchar(50) )
begin
select ename into out_ename from emp where emp.empno = empno;
end $$
delimiter ;
call proc08(1001, @o_ename);
select @o_ename;

輸出的時候，需要在儲存方法中指定，並@變數名，最後才能讓這個變數接收到數值

其次輸出引數的定義的時候，也和輸入引數一樣的，out 引數名型別（長度）

-- 封裝有引數的儲存過程，傳入員工編號，返回員工名字和薪資
delimiter $$
create procedure proc09(in empno int ,out out_ename varchar(50) ,out out_sal decimal(7,2))
begin
select ename,sal into out_ename,out_sal from emp where emp.empno = empno;
end $$
delimiter ;
call proc09(1001, @o_dname,@o_sal);
select @o_dname;
select @o_sal;

內部接收的時候就需要使用 into 變數名，變數名……

注意：也就是說，在儲存過程中使用out宣告輸出引數，在內部檢視into 進行賦值之後，最後在呼叫儲存的時候需要在裡面自定義一個變數進行接收但是必須要使用@變數名

修改傳入引數值

inout 表示從外部傳入的引數經過修改後可以返回的變數，既可以使用傳入變數的值也可以修改變數的值（即使函數執行完）

-- 傳入員工名，拼接部門號，傳入薪資，求出年薪
delimiter $$
create procedure proc10(inout inout_ename varchar(50),inout inout_sal int)
begin
select concat(deptno,"_",inout_ename) into inout_ename from emp where ename = inout_ename;
set inout_sal = inout_sal * 12;
end $$
delimiter ;
set @inout_ename = '關羽';
set @inout_sal = 3000;
call proc10(@inout_ename, @inout_sal) ;
select @inout_ename ;
select @inout_sal ;

相對於，如果我們使用inout關鍵詞，這個引數可以作為輸入引數，也可以作為輸出引數，輸出引數時候，可以在內部的語句進行修改，然後覆蓋原來的值，最後呼叫的時候可以定義一樣的變數名，也可以不一樣。

in 輸入引數，意思說你的引數要傳到存過過程的過程裡面去，在儲存過程中修改該引數的值不能被返回
out 輸出引數:該值可在儲存過程內部被改變，並向外輸出
inout 輸入輸出引數，既能輸入一個值又能傳出來一個值)

到此這篇關於MySQL儲存過程輸入引數(in),輸出引數(out),輸入輸出引數(inout)的文章就介紹到這了,更多相關MySQL儲存過程輸入輸出內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！