什么是存儲(chǔ)過程？

MySQL 5.0 版本開始支持存儲(chǔ)過程。

簡(jiǎn)單的說，存儲(chǔ)過程就是一組SQL語句集，功能強(qiáng)大，可以實(shí)現(xiàn)一些比較復(fù)雜的邏輯功能，類似于JAVA語言中的方法；Python里面的函數(shù)；

存儲(chǔ)過就是數(shù)據(jù)庫 SQL 語言層面的代碼封裝與重用。

特點(diǎn)：

有輸入輸出參數(shù)，可以聲明變量，有if/else, case,while等控制語句，通過編寫存儲(chǔ)過程，可以實(shí)現(xiàn)復(fù)雜的邏輯功能；

函數(shù)的普遍特性：模塊化，封裝，代碼復(fù)用；

速度快，只有首次執(zhí)行需經(jīng)過編譯和優(yōu)化步驟，后續(xù)被調(diào)用可以直接執(zhí)行，省去以上步驟；

語法格式

delimiter 自定義結(jié)束符號(hào)
create procedure 儲(chǔ)存名([ in ,out ,inout ] 參數(shù)名 數(shù)據(jù)類形...)
begin
sql語句
end 自定義的結(jié)束符合
delimiter ;

delimiter $$
create procedure proc01()
begin
select empno,ename from emp;
end $$
delimiter ;
-- 調(diào)用存儲(chǔ)過程
call proc01();

注意創(chuàng)建的時(shí)候需要指定delimiter $$（自定義的，但是通常使用這個(gè)），然后使用create procedure 儲(chǔ)存名(有輸入?yún)?shù) 輸出參數(shù)) 然后記住有begin 和end 之間就寫SQL語句，并且需要在end后面加上$$，最后用delimiter ;

局部變量

用戶自定義，在begin/end塊中有效 ，也就是相對(duì)于在函數(shù)內(nèi)部中聲明變量

• 語法： 聲明變量 declare var_name type [default var_value];
• 舉例：declare nickname varchar(32);

關(guān)鍵詞：declare 變量名 類型（長(zhǎng)度）可以加默認(rèn)值——類似于我們python或者java中的 int a=10；

delimiter $$
create procedure proc02()
begin
declare var_name01 varchar(20) default ‘a(chǎn)aa'; -- 定義局部變量
set var_name01 = ‘zhangsan';
select var_name01;
end $$
delimiter ;
-- 調(diào)用存儲(chǔ)過程
call proc02();

定義變量之后，使用set給變量賦值

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

其基本語法如下：

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

其中：

• col_name 參數(shù)表示查詢的字段名稱；
• var_name 參數(shù)是變量的名稱；
• table_name 參數(shù)指表的名稱；
• condition 參數(shù)指查詢條件。

注意：當(dāng)將查詢結(jié)果賦值給變量時(shí)，該查詢語句的返回結(jié)果只能是單行單列（單元格值）

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 ;
-- 調(diào)用存儲(chǔ)過程
call proc03();

如果是在內(nèi)部聲明的變量，并且進(jìn)行了賦值之后，那么在內(nèi)部調(diào)用顯示的時(shí)候，可以直接select就可以實(shí)現(xiàn)展示數(shù)據(jù)的效果

用戶變量

用戶自定義，當(dāng)前會(huì)話（連接）有效。類比java的成員變量

語法：

@var_name

不需要提前聲明，使用即聲明

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

這里使用@進(jìn)行聲明，前提不需要對(duì)其類型進(jìn)行聲明，直接可以賦值，但是調(diào)用的時(shí)候也需要@變量名

聲明一般有兩種方法：

1、declare 變量名 類型（長(zhǎng)度）

• 然后使用set 變量名 = 值 接收
• 或者使用新值 into 變量名

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

• 但是這個(gè)在整個(gè)會(huì)話都有作用，調(diào)用的時(shí)候需要使用@變量名

系統(tǒng)變量

系統(tǒng)變量又分為全局變量與會(huì)話變量

全局變量在MYSQL啟動(dòng)的時(shí)候由服務(wù)器自動(dòng)將它們初始化為默認(rèn)值，這些默認(rèn)值可以通過更改my.ini這個(gè)文件來更改。

會(huì)話變量在每次建立一個(gè)新的連接的時(shí)候，由MYSQL來初始化。MYSQL會(huì)將當(dāng)前所有全局變量的值復(fù)制一份。來做為會(huì)話變量。

也就是說，如果在建立會(huì)話以后，沒有手動(dòng)更改過會(huì)話變量與全局變量的值，那所有這些變量的值都是一樣的。

全局變量與會(huì)話變量的區(qū)別就在于，對(duì)全局變量的修改會(huì)影響到整個(gè)服務(wù)器，但是對(duì)會(huì)話變量的修改，只會(huì)影響到當(dāng)前的會(huì)話（也就是當(dāng)前的數(shù)據(jù)庫連接）。

有些系統(tǒng)變量的值是可以利用語句來動(dòng)態(tài)進(jìn)行更改的，但是有些系統(tǒng)變量的值卻是只讀的，對(duì)于那些可以更改的系統(tǒng)變量，我們可以利用set語句進(jìn)行更改。

查看修改全局變量：

-- 查看全局變量
show global variables;
-- 查看某全局變量
select @@global.auto_increment_increment;
-- 修改全局變量的值
set global sort_buffer_size = 40000;
set @@global.sort_buffer_size = 40000;

查看修改會(huì)話變量：

-- 查看會(huì)話變量
show session variables;
-- 查看某會(huì)話變量
select @@session.auto_increment_increment;
-- 修改會(huì)話變量的值
set session sort_buffer_size = 50000;
set @@session.sort_buffer_size = 50000 ;

傳入?yún)?shù)

in 表示傳入的參數(shù)， 可以傳入數(shù)值或者變量，即使傳入變量，并不會(huì)更改變量的值，可以內(nèi)部更改，僅僅作用在函數(shù)范圍內(nèi)。

注意：這里參入?yún)?shù)如果和字段名一樣的話，一般不要命名一樣的名字，如果不小心命名一樣的，那么也需要在表中指定，表.字段

而且需要注意的是聲明輸入?yún)?shù)：in 參數(shù)名 類型（長(zhǎng)度）

-- 封裝有參數(shù)的存儲(chǔ)過程，傳入員工編號(hào)，查找員工信息
delimiter $$
create procedure dec_param01(in param_empno varchar(20))
begin
select * from emp where empno = param_empno;
end $$
delimiter ;
call dec_param01('1001');

-- 封裝有參數(shù)的存儲(chǔ)過程，可以通過傳入部門名和薪資，查詢指定部門，并且薪資大于指定值的員工信息
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('學(xué)工部',20000);

通過上述的案例，我們也可以發(fā)現(xiàn)，一般如果傳入?yún)?shù)的時(shí)候，是字符串就用varchar()，如果是數(shù)值就用decimal（），當(dāng)然有時(shí)候也可以使用數(shù)值類型，字符類型。

輸出參數(shù)

out 表示從存儲(chǔ)過程內(nèi)部傳值給調(diào)用者

-- ---------傳出參數(shù)：out---------------------------------
use mysql7_procedure;
-- 封裝有參數(shù)的存儲(chǔ)過程，傳入員工編號(hào)，返回員工名字
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;

輸出的時(shí)候，需要在存儲(chǔ)方法中指定，并@變量名，最后才能讓這個(gè)變量接收到數(shù)值

其次輸出參數(shù)的定義的時(shí)候，也和輸入?yún)?shù)一樣的，out 參數(shù)名 類型（長(zhǎng)度）

-- 封裝有參數(shù)的存儲(chǔ)過程，傳入員工編號(hào)，返回員工名字和薪資
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;

內(nèi)部接收的時(shí)候就需要使用 into 變量名，變量名……

注意：也就是說，在存儲(chǔ)過程中使用out聲明輸出參數(shù)，在內(nèi)部視圖into 進(jìn)行賦值之后，最后在調(diào)用存儲(chǔ)的時(shí)候需要在里面自定義一個(gè)變量進(jìn)行接收 但是必須要 使用@變量名

修改傳入?yún)?shù)值

inout 表示從外部傳入的參數(shù)經(jīng)過修改后可以返回的變量，既可以使用傳入變量的值也可以修改變量的值（即使函數(shù)執(zhí)行完）

-- 傳入員工名，拼接部門號(hào)，傳入薪資，求出年薪
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 = '關(guān)羽';
set @inout_sal = 3000;
call proc10(@inout_ename, @inout_sal) ;
select @inout_ename ;
select @inout_sal ;

相對(duì)于，如果我們使用inout關(guān)鍵詞，這個(gè)參數(shù)可以作為輸入?yún)?shù)，也可以作為輸出參數(shù)，輸出參數(shù)時(shí)候，可以在內(nèi)部的語句進(jìn)行修改，然后覆蓋原來的值，最后調(diào)用的時(shí)候可以定義一樣的變量名，也可以不一樣。

• in 輸入?yún)?shù)，意思說你的參數(shù)要傳到存過過程的過程里面去，在存儲(chǔ)過程中修改該參數(shù)的值不能被返回
• out 輸出參數(shù):該值可在存儲(chǔ)過程內(nèi)部被改變，并向外輸出
• inout 輸入輸出參數(shù)，既能輸入一個(gè)值又能傳出來一個(gè)值)

到此這篇關(guān)于MySQL存儲(chǔ)過程輸入?yún)?shù)(in),輸出參數(shù)(out),輸入輸出參數(shù)(inout)的文章就介紹到這了,更多相關(guān)MySQL存儲(chǔ)過程輸入輸出內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論