MySQL存儲(chǔ)過程和存儲(chǔ)函數(shù)的用法解讀

更新時(shí)間：2025年09月08日 09:36:18 作者：緑水長(zhǎng)流*z

這篇文章主要介紹了MySQL存儲(chǔ)過程和存儲(chǔ)函數(shù)的用法,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

MySQL存儲(chǔ)過程和存儲(chǔ)函數(shù)

MySQL中提供存儲(chǔ)過程與存儲(chǔ)函數(shù)機(jī)制，我們先將其統(tǒng)稱為存儲(chǔ)程序，一般的SQL語(yǔ)句需要先編譯然后執(zhí)行，存儲(chǔ)程序是一組為了完成特定功能的SQL語(yǔ)句集，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中，當(dāng)用戶通過指定存儲(chǔ)程序的名字并給定參數(shù)（如果該存儲(chǔ)程序帶有參數(shù)）來調(diào)用才會(huì)執(zhí)行。

1.1 存儲(chǔ)程序優(yōu)缺點(diǎn)

優(yōu)點(diǎn)：

通常存儲(chǔ)過程有助于提高應(yīng)用程序的性能。當(dāng)創(chuàng)建，存儲(chǔ)過程被編譯之后，就存儲(chǔ)在數(shù)據(jù)庫(kù)中。但是，MySQL實(shí)現(xiàn)的存儲(chǔ)過程略有不同。 MySQL存儲(chǔ)過程按需編譯。在編譯存儲(chǔ)過程之后，MySQL將其放入緩存中。 MySQL為每個(gè)連接維護(hù)自己的存儲(chǔ)過程高速緩存。如果應(yīng)用程序在單個(gè)連接中多次使用存儲(chǔ)過程，則使用編譯版本，否則存儲(chǔ)過程的工作方式類似于查詢。

1）性能：存儲(chǔ)過程有助于減少應(yīng)用程序和數(shù)據(jù)庫(kù)服務(wù)器之間的流量，因?yàn)閼?yīng)用程序不必發(fā)送多個(gè)冗長(zhǎng)的SQL語(yǔ)句，而只能發(fā)送存儲(chǔ)過程的名稱和參數(shù)。

2）復(fù)用：存儲(chǔ)的程序?qū)θ魏螒?yīng)用程序都是可重用的和透明的。存儲(chǔ)過程將數(shù)據(jù)庫(kù)接口暴露給所有應(yīng)用程序，以便開發(fā)人員不必開發(fā)存儲(chǔ)過程中已支持的功能。

3）安全：存儲(chǔ)的程序是安全的。數(shù)據(jù)庫(kù)管理員可以向訪問數(shù)據(jù)庫(kù)中存儲(chǔ)過程的應(yīng)用程序授予適當(dāng)?shù)臋?quán)限，而不向基礎(chǔ)數(shù)據(jù)庫(kù)表提供任何權(quán)限。

缺點(diǎn)：

1）如果使用大量存儲(chǔ)過程，那么使用這些存儲(chǔ)過程的每個(gè)連接的內(nèi)存使用量將會(huì)大大增加。此外，如果在存儲(chǔ)過程中過度使用大量邏輯操作，則CPU使用率也會(huì)增加，因?yàn)閿?shù)據(jù)庫(kù)服務(wù)器的設(shè)計(jì)不偏于邏輯運(yùn)算。

2）很難調(diào)試存儲(chǔ)過程。只有少數(shù)數(shù)據(jù)庫(kù)管理系統(tǒng)允許調(diào)試存儲(chǔ)過程。不幸的是，MySQL不提供調(diào)試存儲(chǔ)過程的功能。

1.2 數(shù)據(jù)準(zhǔn)備

創(chuàng)建數(shù)據(jù)庫(kù)：

DEFAULT CHARACTER SET utf8;
use test;

這里記得設(shè)置編碼！

創(chuàng)建測(cè)試表：

DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

insert  into `class`(`id`,`name`) values 
(1,'Java'),
(2,'UI'),
(3,'產(chǎn)品');


DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`id`,`name`,`class_id`) values 
(1,'張三',1),
(2,'李四',1),
(3,'王五',2),
(4,'趙劉',1),
(5,'錢七',3);

查詢數(shù)據(jù)：

select * from class;

select * from student;

1.3 存儲(chǔ)過程的使用

語(yǔ)法

CREATE PROCEDURE procedure_name ([parameters[,...]])
begin
-- SQL語(yǔ)句
end ;

示例

create procedure test1()
begin
	select 'Hello';
end;

調(diào)用存儲(chǔ)過程

call test1();

查看存儲(chǔ)過程

-- 查看db01數(shù)據(jù)庫(kù)中的所有存儲(chǔ)過程
select name from mysql.proc where db='test';

-- 查看存儲(chǔ)過程的狀態(tài)信息
show procedure status;

-- 查看存儲(chǔ)過程的創(chuàng)建語(yǔ)句
show create procedure test1;

刪除存儲(chǔ)過程

drop procedure test1;

1.2 存儲(chǔ)過程的語(yǔ)法

1.2.1 變量

declare：聲明變量

CREATE PROCEDURE test2 ()
begin
	
	declare num int default 0;		-- 聲明變量,賦默認(rèn)值為0
	select num+10;
	
end ;

call test2();			-- 調(diào)用存儲(chǔ)過程

set：賦值操作

CREATE PROCEDURE test3 ()
begin
	
	declare num int default 0;
	set num =20;			-- 給num變量賦值
	select num;
	
end ;

call test3();

into：賦值

CREATE PROCEDURE test4 ()
begin
	
	declare num int default 0;			
	select count(1) into num from student;
	select num;
end ;

call test4();

1.2.2 if語(yǔ)句

需求：根據(jù)class_id判斷是Java還是UI還是產(chǎn)品

CREATE PROCEDURE test5 ()
begin
	
	declare id int default 1;			
	declare class_name varchar(30);
	
	if id=1 then
		set class_name='哇塞，Java大佬！';
	elseif id=2 then
		set class_name='原來是UI的啊';
	else
		set class_name='不用想了，肯定是產(chǎn)品小樣';
	end if;
	
	select class_name;
end ;

call test5();

1.2.3 傳遞參數(shù)

語(yǔ)法：

create procedure procedure_name([in/out/inout] 參數(shù)名  參數(shù)類型)

in：該參數(shù)可以作為輸入，也就是需要調(diào)用方傳入值 , 默認(rèn)
out：該參數(shù)作為輸出，也就是該參數(shù)可以作為返回值
inout：既可以作為輸入?yún)?shù)，也可以作為輸出參數(shù)

1.2.3.1 in-輸入?yún)?shù)

-- 定義一個(gè)輸入?yún)?shù)
CREATE PROCEDURE test6 (in id int)
begin
	
	declare class_name varchar(30);
	
	if id=1 then
		set class_name='哇塞，Java大佬！';
	elseif id=2 then
		set class_name='原來是UI的啊';
	else
		set class_name='不用想了，肯定是產(chǎn)品小樣';
	end if;
	
	select class_name;
end ;

call test6(3);

1.2.3.2 out-輸出參數(shù)

-- 定義一個(gè)輸入?yún)?shù)和一個(gè)輸出參數(shù)
CREATE PROCEDURE test7 (in id int,out class_name varchar(100))
begin
	if id=1 then
		set class_name='哇塞，Java大佬！';
	elseif id=2 then
		set class_name='原來是UI的啊';
	else
		set class_name='不用想了，肯定是產(chǎn)品小樣';
	end if;
	
end ;


call test7(1,@class_name);	-- 創(chuàng)建會(huì)話變量		

select @class_name;		-- 引用會(huì)話變量

@xxx：代表定義一個(gè)會(huì)話變量，整個(gè)會(huì)話都可以使用，當(dāng)會(huì)話關(guān)閉（連接斷開）時(shí)銷毀
@@xxx：代表定義一個(gè)系統(tǒng)變量，永久生效。

1.2.4 case語(yǔ)句

需求：傳遞一個(gè)月份值，返回所在的季節(jié)。

CREATE PROCEDURE test8 (in month int,out season varchar(10))
begin
	
	case 
		when month >=1 and month<=3 then
			set season='spring';
		when month >=4 and month<=6 then
			set season='summer';
		when month >=7 and month<=9 then
			set season='autumn';
		when month >=10 and month<=12 then
			set season='winter';
	end case;
end ;

call test8(9,@season);			-- 定義會(huì)話變量來接收test8存儲(chǔ)過程返回的值

select @season;

1.3.5 while循環(huán)

需求：計(jì)算任意數(shù)的累加和

CREATE PROCEDURE test10 (in count int)
begin
	declare total int default 0;
	declare i int default 1;
	
	while i<=count do
		set total=total+i;
		set i=i+1;
	end while;
	select total;
end ;

call test10(10);

1.3.6 repeat循環(huán)

需求：計(jì)算任意數(shù)的累加和

CREATE PROCEDURE test11 (count int)		-- 默認(rèn)是輸入(in)參數(shù)
begin
	declare total int default 0;
	repeat 
		set total=total+count;
		set count=count-1;
		until count=0				-- 結(jié)束條件,注意不要打分號(hào)
	end repeat;
	select total;
end ;

call test11(10);

1.3.7 loop循環(huán)

需求：計(jì)算任意數(shù)的累加和

CREATE PROCEDURE test12 (count int)		-- 默認(rèn)是輸入(in)參數(shù)
begin
	declare total int default 0;	
	sum:loop							-- 定義循環(huán)標(biāo)識(shí)
		set total=total+count;
		set count=count-1;
		
		if count < 1 then
			leave sum;					-- 跳出循環(huán)
		end if;
	end loop sum;						-- 標(biāo)識(shí)循環(huán)結(jié)束
	select total;
	
end ;

call test12(10);

1.3.8 游標(biāo)

游標(biāo)是用來存儲(chǔ)查詢結(jié)果集的數(shù)據(jù)類型，可以幫我們保存多條行記錄結(jié)果，我們要做的操作就是讀取游標(biāo)中的數(shù)據(jù)獲取每一行的數(shù)據(jù)。

聲明游標(biāo)

declare cursor_name cursor for statement;

打開游標(biāo)

open cursor_name;

關(guān)閉游標(biāo)

close cursor_name;

案例：

CREATE PROCEDURE test13 ()		-- 默認(rèn)是輸入(in)參數(shù)
begin
	
	declare id int(11);
	declare `name` varchar(20);
	declare class_id int(11);
	-- 定義游標(biāo)結(jié)束標(biāo)識(shí)符
	declare has_data int default 1;
	
	declare stu_result cursor for select * from student;
	-- 監(jiān)測(cè)游標(biāo)結(jié)束
	declare exit handler for not FOUND set has_data=0;
	
	-- 打開游標(biāo)
	open stu_result;
	
	repeat 
		fetch stu_result into id,`name`,class_id;
		
		select concat('id: ',id,';name: ',`name`,';class_id',class_id);
		until has_data=0		-- 退出條件,注意不要打分號(hào)
	end repeat;
	
	-- 關(guān)閉游標(biāo)
	close stu_result;
	
end ;

call test13();

1.3 存儲(chǔ)過程和存儲(chǔ)函數(shù)的區(qū)別

存儲(chǔ)函數(shù)的限制比較多，例如不能用臨時(shí)表,只能用表變量，而存儲(chǔ)過程的限制較少，存儲(chǔ)過程的實(shí)現(xiàn)功能要復(fù)雜些,而函數(shù)的實(shí)現(xiàn)功能針對(duì)性比較強(qiáng)。

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

調(diào)用時(shí)的不同。存儲(chǔ)函數(shù)嵌入在SQL中使用，可以在select 存儲(chǔ)函數(shù)名(變量值)；存儲(chǔ)過程通過call語(yǔ)句調(diào)用 call 存儲(chǔ)過程名。

參數(shù)的不同。存儲(chǔ)函數(shù)的參數(shù)類型類似于IN參數(shù)，沒有類似于OUT和INOUT的參數(shù)。存儲(chǔ)過程的參數(shù)類型有三種，in、out和inout：

in：數(shù)據(jù)只是從外部傳入內(nèi)部使用(值傳遞),可以是數(shù)值也可以是變量
out：只允許過程內(nèi)部使用(不用外部數(shù)據(jù)),給外部使用的(引用傳遞:外部的數(shù)據(jù)會(huì)被先清空才會(huì)進(jìn)入到內(nèi)部),只能是變量
inout：外部可以在內(nèi)部使用,內(nèi)部修改的也可以給外部使用,典型的引用傳遞,只能傳遞變量。

1.3.1 臨時(shí)表

臨時(shí)表顧名思義就是臨時(shí)要用創(chuàng)建的表，臨時(shí)表的作用僅限于本次會(huì)話，等連接關(guān)閉后重新打開連接臨時(shí)表將不存在

創(chuàng)建一張臨時(shí)表：

create temporary table temp_table(
	id int,
	name varchar(10)
);
insert into temp_table values (1,'1');

select * from temp_table ;

temporary：代表創(chuàng)建的表是一張臨時(shí)表；

注意：臨時(shí)表示查詢不到的

show tables;   -- 不會(huì)顯示臨時(shí)表的存在

測(cè)試存儲(chǔ)過程創(chuàng)建臨時(shí)表：

create procedure pro1()
begin
	create temporary table temp_table(
		id int
	);
	
	insert into temp_table values(1);
	
	select * from temp_table;
end;

call pro1();

運(yùn)行沒有任何問題

測(cè)試存儲(chǔ)函數(shù)創(chuàng)建臨時(shí)表

create function fun2()
returns int
begin

	declare id int ;
	create table temp_table(				
		id int
	);
	
	insert into temp_table values(1);
	
	select id from into id temp_table;	
	return id;
end;

發(fā)現(xiàn)報(bào)錯(cuò)。

1.4 談?wù)劄槭裁创蟛糠止緸槭裁床挥么鎯?chǔ)過程（函數(shù)）？

1.4.1 原因一

參考1.1小結(jié)說的存儲(chǔ)過程缺點(diǎn)

1.4.2 原因二

咱們分析三層架構(gòu)就知道了，咱們的業(yè)務(wù)邏輯應(yīng)該放到咱們的業(yè)務(wù)層，也就Tomcat，而不是把業(yè)務(wù)滯留到數(shù)據(jù)庫(kù)來處理，將業(yè)務(wù)和數(shù)據(jù)庫(kù)嚴(yán)重耦合在一起了！

這是導(dǎo)致公司開發(fā)不使用存儲(chǔ)過程的一個(gè)重要原因

1.4.3 原因三

咱們平時(shí)對(duì)業(yè)務(wù)性能進(jìn)行擴(kuò)容非常好，搭建集群、使用緩存提高響應(yīng)速度等等。

總之，大多數(shù)情況下并不是業(yè)務(wù)層是整個(gè)項(xiàng)目性能的瓶頸，而是數(shù)據(jù)庫(kù)！我們應(yīng)該盡可能的優(yōu)化數(shù)據(jù)庫(kù)方面的性能，而且業(yè)務(wù)層性能擴(kuò)容相對(duì)于數(shù)據(jù)庫(kù)性能擴(kuò)容要方便的多。

因此我們應(yīng)該盡可能的優(yōu)化數(shù)據(jù)庫(kù)方面的性能，降低數(shù)據(jù)層的壓力，把所有壓力能分單到其他地方就分擔(dān)，而不是讓數(shù)據(jù)庫(kù)增加壓力！