目錄
- 存儲(chǔ)過(guò)程簡(jiǎn)介
- 為什么要用存儲(chǔ)過(guò)程?
- 存儲(chǔ)過(guò)程的優(yōu)點(diǎn)
- 存儲(chǔ)過(guò)程的缺點(diǎn)
- MySQL 中的存儲(chǔ)過(guò)程
- 創(chuàng)建與調(diào)用過(guò)程
- 存儲(chǔ)過(guò)程語(yǔ)法解析
- 存儲(chǔ)過(guò)程的參數(shù)
- 變量
- 變量賦值
- 流程控制語(yǔ)句
- if 條件語(yǔ)句
- case 條件語(yǔ)句
- while 循環(huán)語(yǔ)句
- repeat 循環(huán)語(yǔ)句
- loop 循環(huán)語(yǔ)句
- 存儲(chǔ)過(guò)程的管理
- 總結(jié)
存儲(chǔ)過(guò)程簡(jiǎn)介
為什么要用存儲(chǔ)過(guò)程?
MySQL5.0 版本開(kāi)始支持存儲(chǔ)過(guò)程。
大多數(shù) SQL 語(yǔ)句都是針對(duì)一個(gè)或多個(gè)表的單條語(yǔ)句。并非所有的操作都那么簡(jiǎn)單。經(jīng)常會(huì)有一個(gè)完整的操作需要多條語(yǔ)句才能完成。
存儲(chǔ)過(guò)程簡(jiǎn)單來(lái)說(shuō),就是為以后的使用而保存的一條或多條 MySQL 語(yǔ)句的集合。可將其視為批處理文件。雖然他們的作用不僅限于批處理。
存儲(chǔ)過(guò)程思想上很簡(jiǎn)單,就是數(shù)據(jù)庫(kù) SQL 語(yǔ)言層面的代碼封裝與重用。
存儲(chǔ)過(guò)程的優(yōu)點(diǎn)
- 通過(guò)把處理封裝在容易使用的單元中,簡(jiǎn)化復(fù)雜的操作;
- 簡(jiǎn)化對(duì)變動(dòng)的管理。如果表名、列名或業(yè)務(wù)邏輯有變化。只需要更改存儲(chǔ)過(guò)程的代碼,使用它的人員不會(huì)改自己的代碼;
- 通常存儲(chǔ)過(guò)程有助于提高應(yīng)用程序的性能。當(dāng)創(chuàng)建的存儲(chǔ)過(guò)程被編譯之后,就存儲(chǔ)在數(shù)據(jù)庫(kù)中。 但是,MySQL 實(shí)現(xiàn)的存儲(chǔ)過(guò)程略有不同。MySQL 存儲(chǔ)過(guò)程按需編譯。在編譯存儲(chǔ)過(guò)程之后,MySQL 將其放入緩存中。MySQL 為每個(gè)連接維護(hù)自己的存儲(chǔ)過(guò)程高速緩存。如果應(yīng)用程序在單個(gè)連接中多次使用存儲(chǔ)過(guò)程,則使用編譯版本,否則存儲(chǔ)過(guò)程的工作方式類似于查詢;
- 存儲(chǔ)過(guò)程有助于減少應(yīng)用程序和數(shù)據(jù)庫(kù)服務(wù)器之間的流量,因?yàn)閼?yīng)用程序不必發(fā)送多個(gè)冗長(zhǎng)的 SQL 語(yǔ)句,而只用發(fā)送存儲(chǔ)過(guò)程的名稱和參數(shù);
- 存儲(chǔ)的程序?qū)θ魏螒?yīng)用程序都是可重用的和透明的。存儲(chǔ)過(guò)程將數(shù)據(jù)庫(kù)接口暴露給所有應(yīng)用程序,以便開(kāi)發(fā)人員不必開(kāi)發(fā)存儲(chǔ)過(guò)程中已支持的功能;
- 存儲(chǔ)的程序是安全的。數(shù)據(jù)庫(kù)管理員可以向訪問(wèn)數(shù)據(jù)庫(kù)中存儲(chǔ)過(guò)程的應(yīng)用程序授予適當(dāng)?shù)臋?quán)限,而不向基礎(chǔ)數(shù)據(jù)庫(kù)表提供任何權(quán)限。
存儲(chǔ)過(guò)程的缺點(diǎn)
- 如果使用大量存儲(chǔ)過(guò)程,那么使用這些存儲(chǔ)過(guò)程的每個(gè)連接的內(nèi)存使用量將會(huì)大大增加。 此外,如果您在存儲(chǔ)過(guò)程中過(guò)度使用大量邏輯操作,則 CPU 使用率也會(huì)增加,因?yàn)?MySQL 數(shù)據(jù)庫(kù)最初的設(shè)計(jì)側(cè)重于高效的查詢,不利于邏輯運(yùn)算;
- 存儲(chǔ)過(guò)程的構(gòu)造使得開(kāi)發(fā)具有復(fù)雜業(yè)務(wù)邏輯的存儲(chǔ)過(guò)程變得更加困難;
- 很難調(diào)試存儲(chǔ)過(guò)程。只有少數(shù)數(shù)據(jù)庫(kù)管理系統(tǒng)允許您調(diào)試存儲(chǔ)過(guò)程。不幸的是,MySQL 不提供調(diào)試存儲(chǔ)過(guò)程的功能;
- 開(kāi)發(fā)和維護(hù)存儲(chǔ)過(guò)程并不容易。開(kāi)發(fā)和維護(hù)存儲(chǔ)過(guò)程通常需要一個(gè)不是所有應(yīng)用程序開(kāi)發(fā)人員擁有的專業(yè)技能。這可能會(huì)導(dǎo)致應(yīng)用程序開(kāi)發(fā)和維護(hù)階段的問(wèn)題。
MySQL 中的存儲(chǔ)過(guò)程
創(chuàng)建與調(diào)用過(guò)程
創(chuàng)建存儲(chǔ)過(guò)程,代碼如下所示:
-- 創(chuàng)建存儲(chǔ)過(guò)程
create procedure mypro(in a int,in b int,out sum int)
begin
set sum = a+b;
end;
運(yùn)行結(jié)果如下
也可以在 Navicat 客戶端“函數(shù)”節(jié)點(diǎn)下查看過(guò)程,如下圖所示:
調(diào)用存儲(chǔ)過(guò)程,代碼如下所示:
call mypro(1,2,@s);-- 調(diào)用存儲(chǔ)過(guò)程
select @s;-- 顯示過(guò)程輸出結(jié)果
運(yùn)行結(jié)果
存儲(chǔ)過(guò)程語(yǔ)法解析
- create procedure 用來(lái)創(chuàng)建過(guò)程;
- mypro 用來(lái)定義過(guò)程名稱;
- (in a int,in b int,out sum int)表示過(guò)程的參數(shù),其中 in 表示輸入?yún)?shù),out 表示輸出參數(shù)。類似于 Java 定義方法時(shí)的形參和返回值;
- begin 與end 表示過(guò)程主體的開(kāi)始和結(jié)束,相當(dāng)于 Java 定義方法的一對(duì)大括號(hào);
- call用來(lái)調(diào)用過(guò)程,@s 是用來(lái)接收過(guò)程輸出參數(shù)的變量
存儲(chǔ)過(guò)程的參數(shù)
MySQL 存儲(chǔ)過(guò)程的參數(shù)用在存儲(chǔ)過(guò)程的定義,共有三種參數(shù)類型:
- IN 輸入?yún)?shù):表示調(diào)用者向過(guò)程傳入值(傳入值可以是字面量或變量);
- OUT 輸出參數(shù):表示過(guò)程向調(diào)用者傳出值(可以返回多個(gè)值)(傳出值只能是變量);
- INOUT輸入輸出參數(shù):既表示調(diào)用者向過(guò)程傳入值,又表示過(guò)程向調(diào)用者傳出值(值只能是變量)。
存儲(chǔ)過(guò)程根據(jù)參數(shù)可分為四種類別:
1).沒(méi)有參數(shù)的過(guò)程;
2).只有輸入?yún)?shù)的過(guò)程;
3).只有輸出參數(shù)的過(guò)程;
4).包含輸入和輸出參數(shù)的過(guò)程。
變量
MySQL 中的存儲(chǔ)過(guò)程類似 java 中的方法。
既然如此,在存儲(chǔ)過(guò)程中也同樣可以使用變量。java 中的局部變量作用域是變量所在的方法,而 MySQL 中的局部變量作用域是所在的存儲(chǔ)過(guò)程。
變量定義
DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
declare
用于聲明變量;
variable_name
表示變量名稱;
datatype
為 MySQL 的數(shù)據(jù)類型;
default
用于聲明默認(rèn)值;
例如:
declare name varchar(20) default ‘jack'。
變量賦值
SET 變量名 = 表達(dá)式值 [,variable_name = expression ...]
在存儲(chǔ)過(guò)程中使用變量,代碼如下所示
use schooldb;-- 使用 schooldb 數(shù)據(jù)庫(kù)
-- 創(chuàng)建過(guò)程
create procedure mypro1()
begin
declare name varchar(20);
set name = '丘處機(jī)';
select * from studentinfo where studentname = name;
end;
-- 調(diào)用過(guò)程
call mypro1();
運(yùn)行結(jié)果
流程控制語(yǔ)句
if 條件語(yǔ)句
IF
語(yǔ)句包含多個(gè)條件判斷,根據(jù)結(jié)果為 TRUE
、FALSE
執(zhí)行語(yǔ)句,與編程語(yǔ)言中的 if
、else if
、else
語(yǔ)法類似。
定義存儲(chǔ)過(guò)程,輸入一個(gè)整數(shù),使用 if 語(yǔ)句判斷是正數(shù)還是負(fù)數(shù),代碼如下所示:
-- 創(chuàng)建過(guò)程
create procedure mypro2(in num int)
begin
if num0 then -- 條件開(kāi)始
select '負(fù)數(shù)';
elseif num=0 then
select '不是正數(shù)也不是負(fù)數(shù)';
else
select '正數(shù)';
end if;-- 條件結(jié)束
end;
-- 調(diào)用過(guò)程
call mypro2(-1);
運(yùn)行結(jié)果
case 條件語(yǔ)句
case
是另一個(gè)條件判斷的語(yǔ)句,類似于編程語(yǔ)言中的 choose
、when
語(yǔ)法。MySQL 中的 case
語(yǔ)句有兩種語(yǔ)法
格式。
定義存儲(chǔ)過(guò)程,輸入一個(gè)整數(shù),使用 case 語(yǔ)句判斷是正數(shù)還是負(fù)數(shù),代碼如下所示:
-- 創(chuàng)建過(guò)程
create procedure mypro3(in num int)
begin
case -- 條件開(kāi)始
when num0 then select '負(fù)數(shù)';
when num=0 then select '不是正數(shù)也不是負(fù)數(shù)';
else select '正數(shù)';
end case; -- 條件結(jié)束
end;
-- 調(diào)用過(guò)程
call mypro3(1);
運(yùn)行結(jié)果
定義存儲(chǔ)過(guò)程,輸入一個(gè)整數(shù),使用 case 語(yǔ)句判斷是 1 還是 2,代碼如下所示:
-- 創(chuàng)建過(guò)程
create procedure mypro4(in num int)
begin
case num -- 條件開(kāi)始
when 1 then select '數(shù)值是 1';
when 2 then select '數(shù)值是 2';
else select '不是 1 也不是 2';
end case; -- 條件結(jié)束
end;
-- 調(diào)用過(guò)程
call mypro4(3);
運(yùn)行結(jié)果
兩種 case 語(yǔ)法都可以實(shí)現(xiàn)條件判斷,但第一種適合范圍值判斷,而第二種適合確定值判斷。
while 循環(huán)語(yǔ)句
while
語(yǔ)句的用法和 java
中的 while
循環(huán)類似。
定義存儲(chǔ)過(guò)程,使用 while 循環(huán)輸出 1 到 10 的累加和,代碼如下所示:
-- 創(chuàng)建過(guò)程
create procedure mypro5(out sum int)
begin
declare num int default 0;
set sum = 0;
while num10 do -- 循環(huán)開(kāi)始
set num = num+1;
set sum = sum+num;
end while; -- 循環(huán)結(jié)束
end;
-- 調(diào)用過(guò)程
call mypro5(@sum);
-- 查詢變量值
select @sum;
運(yùn)行結(jié)果
repeat 循環(huán)語(yǔ)句
repeat
語(yǔ)句的用法和 java
中的 do…while
語(yǔ)句類似,都是先執(zhí)行循環(huán)操作,再判斷條件,區(qū)別是 repeat
表達(dá)
式值為 false
時(shí)才執(zhí)行循環(huán)操作,直到表達(dá)式值為 true
停止。
定義存儲(chǔ)過(guò)程,使用 repeat 循環(huán)輸出 1 到 10 的累加和,代碼如下所示:
-- 創(chuàng)建過(guò)程
create procedure mypro6(out sum int)
begin
declare num int default 0;
set sum = 0;
repeat-- 循環(huán)開(kāi)始
set num = num+1;
set sum = sum+num;
until num>=10
end repeat; -- 循環(huán)結(jié)束
end;
-- 調(diào)用過(guò)程
call mypro6(@sum);
-- 查詢變量值
select @sum;
運(yùn)行結(jié)果
loop 循環(huán)語(yǔ)句
循環(huán)語(yǔ)句,用來(lái)重復(fù)執(zhí)行某些語(yǔ)句。
執(zhí)行過(guò)程中可使用 leave
語(yǔ)句或 iterate
跳出循環(huán),也可以嵌套 IF
等判斷語(yǔ)句。
leave
語(yǔ)句效果相當(dāng)于 java 中的 break
,用來(lái)終止循環(huán);
iterate
語(yǔ)句效果相當(dāng)于 java 中的 continue
,用來(lái)結(jié)束本次循環(huán)操作,進(jìn)入下一次循環(huán)。
定義存儲(chǔ)過(guò)程,使用 loop 循環(huán)輸出 1 到 10 的累加和,代碼如下所示:
-- 創(chuàng)建過(guò)程
create procedure mypro7(out sum int)
begin
declare num int default 0;
set sum = 0;
loop_sum:loop-- 循環(huán)開(kāi)始
set num = num+1;
set sum = sum+num;
if num>=10 then
leave loop_sum;
end if;
end loop loop_sum; -- 循環(huán)結(jié)束
end;
-- 調(diào)用過(guò)程
call mypro7(@sum);
-- 查詢變量值
select @sum;
運(yùn)行結(jié)果
代碼中的 loop_sum 相當(dāng)于給循環(huán)貼個(gè)標(biāo)簽,方便多重循環(huán)時(shí)靈活操作。
存儲(chǔ)過(guò)程的管理
存儲(chǔ)過(guò)程的管理主要包括:顯示過(guò)程、顯示過(guò)程源碼、刪除過(guò)程。
比較簡(jiǎn)單的方式就是利用 navicat 客戶端工具進(jìn)行管理,鼠標(biāo)點(diǎn)擊操作即可,如下圖所示:
顯示存儲(chǔ)過(guò)程
顯示特定數(shù)據(jù)庫(kù)的存儲(chǔ)過(guò)程
SHOW PROCEDURE status where db = 'schooldb';
顯示特定模式的存儲(chǔ)過(guò)程,要求顯示名稱中包含“my”的存儲(chǔ)過(guò)程
SHOW PROCEDURE status where name like '%my%';
顯示存儲(chǔ)過(guò)程“mypro1”的源碼
SHOW CREATE PROCEDURE mypro1;
刪除存儲(chǔ)過(guò)程“mypro1”
總結(jié)
到此這篇關(guān)于MySQL存儲(chǔ)過(guò)程的創(chuàng)建、調(diào)用與管理的文章就介紹到這了,更多相關(guān)MySQL存儲(chǔ)過(guò)程內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
您可能感興趣的文章:- Mysql 用戶權(quán)限管理實(shí)現(xiàn)
- 詳解MySQL 用戶權(quán)限管理
- 詳解MySQL InnoDB存儲(chǔ)引擎的內(nèi)存管理
- Mysql實(shí)戰(zhàn)練習(xí)之簡(jiǎn)單圖書管理系統(tǒng)