MySQL由淺入深探究儲存過程

2022-07-25 18:06:33

什麼是儲存過程

儲存過程（Stored Procedure）也成為儲存程式，是一種在資料庫中儲存複雜程式，以便外部程式呼叫的一種資料庫物件。即預先編輯好SQL語句的集合，這個集合完成了某項具體的功能集合，需要這個功能的時候，只要呼叫這個過程就好。在業務開發工過程中，一般不要求使用儲存過程實現業務流程，編寫的儲存過程不方便偵錯和擴充套件，同時沒有移植性。

簡單來說儲存過程就是具有名字的一段程式碼，用來完成一個特定的功能。他和函數很像，但是他不是函數，

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

儲存過程和儲存函數的區別

儲存函數的限制比較多，例如不能用臨時表,只能用表變數，而儲存過程的限制較少，儲存過程的實現功能要複雜些,而函數的實現功能針對性比較強。

返回值不同。儲存函數必須有返回值,且僅返回一個結果值；儲存過程可以沒有返回值,但是能返回結果集(out，inout)。

呼叫時的不同。儲存函數嵌入在SQL中使用，可以在select 儲存函數名(變數值)；儲存過程通過call語句呼叫 call 儲存過程名。

引數的不同。儲存函數的引數型別類似於IN引數，沒有類似於OUT和INOUT的引數。儲存過程的引數型別有三種，in、out和inout：

in：資料只是從外部傳入內部使用(值傳遞),可以是數值也可以是變數
out：只允許過程內部使用(不用外部資料),給外部使用的(參照傳遞:外部的資料會被先清空才會進入到內部),只能是變數
inout：外部可以在內部使用,內部修改的也可以給外部使用,典型的參照傳遞,只能傳遞變數。

優點

儲存過程可封裝，並隱藏複雜的商業邏輯。
儲存過程可以回傳值，並可以接受引數。
儲存過程無法使用 SELECT 指令來執行，因為它是子程式，與檢視表，資料表或使用者定義函數不同。
儲存過程可以用在資料檢驗，強制實行商業邏輯等。

缺點

儲存過程，往往客製化化於特定的資料庫上，因為支援的程式語言不同。當切換到其他廠商的資料庫系統時，需要重寫原有的儲存過程。
儲存過程的效能調校與撰寫，受限於各種資料庫系統。

儲存過程的建立和呼叫

建立的儲存過程儲存在資料庫的資料字典中。

建立語法：

create procedure 儲存過程的名字(參數列)
begin
儲存過程體(SQL語句的集合);
end

注意:

①參數列包含三個部分:

引數模式引數名引數型別

(比如: in s_name varchar(20) )

宣告儲存過程:

CREATE PROCEDURE demo_in_parameter(IN s_name varchar(20))

引數模式:

in : 該引數可以作為輸入,需要呼叫方傳入值來給儲存過程

out : 該引數可以作為輸出,該引數可以作為返回值給呼叫方

inout : 該引數既可以做輸入,也可以作為輸出

儲存過程開始和結束符號:

BEGIN .... END

②如果儲存體只要一句SQL語句,begin和end可以省略,儲存體裡的sql語句結尾處必須加分號,避免資料庫誤判為儲存過程的結束標記,所以需要我們自定義命令的結尾符號:

delimiter 結尾標記比如:

delimiter $

如果沒有下面用表，先建立

drop table ages;
drop table students;
create table ages(id int,age int);
create table students(id int,name varchar(4),ta_id int);
insert into ages(id,age) values(1,12);
insert into ages(id,age) values(2,22);
insert into ages(id,age) values(3,32);
insert into ages(id,age) values(4,42);
insert into ages(id,age) values(5,52);
insert into ages(id,age) values(6,62);
insert into students(id,name,ta_id) values(1,'任波濤',2);
insert into students(id,name,ta_id) values(2,'田興偉',1);
insert into students(id,name,ta_id) values(3,'唐崇俊',3);
insert into students(id,name,ta_id) values(4,'夏銘睿',8);
insert into students(id,name,ta_id) values(5,'包琪',1);
insert into students(id,name,ta_id) values(6,'夏雨',10);
insert into students(id,name,ta_id) values(7,'夏銘雨',10);
insert into students(id,name,ta_id) values(8,'白芳芳',6);

無引數儲存過程：

delimiter $		#將語句的結束符號從分號;臨時改為兩個$(可以是自定義)
create procedure myp1()
begin
insert into ages(id,`age`) values (11,'12');
insert into ages(id,`age`) values (21,'13');
insert into ages(id,`age`) values (31,'14');
insert into ages(id,`age`) values (41,'15');
end $
delimiter ;		#將語句的結束符號恢復為分號

儲存過程的呼叫:

call 儲存過程名(參數列);

呼叫:

call myp1();

帶in引數模式的儲存過程

案例:通過學生名查詢對應的年齡

delimiter $
create procedure myp2(in s_name varchar(10))
begin
select s.name, a.age from students s
inner join ages a
on s.ta_id = a.id
where s.name=s_name;
end $

呼叫:call myp2(‘任波濤’) $

out引數模式的儲存過程

案例:根據學生姓名,返回對應的年齡

create procedure myp3(in sname varchar(10),out age int)
begin
select a.age into age
from students s
inner join ages a
on s.ta_id = a.id
where s.name=sname;
end $

呼叫:

call myp3(‘任波濤’,@age) $ #把值取出來放到變數裡去

select @age $ #檢視值了

案例:根據學生姓名,返回對應的年齡和學生編號

create procedure myp4(in sname varchar(10),out age int,out sid int)
begin
select a.age ,s.id into age,sid
from students s
inner join ages a
on s.ta_id = a.id
where s.name=sname;
end $

呼叫:

call myp4(‘任波濤’,@age,@sid) $

select @age,@sid $

inout引數模式儲存過程和刪除檢視儲存過程

案例:傳入a和b兩個數,然後讓a和b都乘以2後返回

create procedure myp5(inout a int , inout b int)
begin
set a=a*2;
set b=b*2;
end $

呼叫:

set @a=10$
set @b=20$
call myp5(@a,@b)$
select @a,@b $
delimiter ;

#檢視儲存過程

show procedure status like 'myp%';

刪除儲存過程:

drop procedure 儲存過程名;
drop procedure myp1; #每次只能刪除一個

檢視儲存過程的資訊:

show create procedure 儲存名;
show create procedure myp1;

到此這篇關於MySQL由淺入深探究儲存過程的文章就介紹到這了,更多相關MySQL儲存過程內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！