MySQL存儲過程輸入參數(in),輸出參數(out),輸入輸出參數(inout)
什麼是存儲過程?
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存儲過程輸入輸出內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!