MySQL存儲過程的創建、調用與管理詳解

存儲過程簡介

為什麼要用存儲過程？

MySQL5.0 版本開始支持存儲過程。

大多數 SQL 語句都是針對一個或多個表的單條語句。並非所有的操作都那麼簡單。經常會有一個完整的操作需要多條語句才能完成。

存儲過程簡單來說，就是為以後的使用而保存的一條或多條 MySQL 語句的集合。可將其視為批處理文件。雖然他們的作用不僅限於批處理。

存儲過程思想上很簡單，就是數據庫 SQL 語言層面的代碼封裝與重用。

存儲過程的優點

1. 通過把處理封裝在容易使用的單元中，簡化復雜的操作；
2. 簡化對變動的管理。如果表名、列名或業務邏輯有變化。隻需要更改存儲過程的代碼，使用它的人員不會改自己的代碼；
3. 通常存儲過程有助於提高應用程序的性能。當創建的存儲過程被編譯之後，就存儲在數據庫中。 但是，MySQL 實現的存儲過程略有不同。MySQL 存儲過程按需編譯。在編譯存儲過程之後，MySQL 將其放入緩存中。MySQL 為每個連接維護自己的存儲過程高速緩存。如果應用程序在單個連接中多次使用存儲過程，則使用編譯版本，否則存儲過程的工作方式類似於查詢；
4. 存儲過程有助於減少應用程序和數據庫服務器之間的流量，因為應用程序不必發送多個冗長的 SQL 語句，而隻用發送存儲過程的名稱和參數；
5. 存儲的程序對任何應用程序都是可重用的和透明的。存儲過程將數據庫接口暴露給所有應用程序，以便開發人員不必開發存儲過程中已支持的功能；
6. 存儲的程序是安全的。數據庫管理員可以向訪問數據庫中存儲過程的應用程序授予適當的權限，而不向基礎數據庫表提供任何權限。

存儲過程的缺點

1. 如果使用大量存儲過程，那麼使用這些存儲過程的每個連接的內存使用量將會大大增加。 此外，如果您在存儲過程中過度使用大量邏輯操作，則 CPU 使用率也會增加，因為 MySQL 數據庫最初的設計側重於高效的查詢，不利於邏輯運算；
2. 存儲過程的構造使得開發具有復雜業務邏輯的存儲過程變得更加困難；
3. 很難調試存儲過程。隻有少數數據庫管理系統允許您調試存儲過程。不幸的是，MySQL 不提供調試存儲過程的功能；
4. 開發和維護存儲過程並不容易。開發和維護存儲過程通常需要一個不是所有應用程序開發人員擁有的專業技能。這可能會導致應用程序開發和維護階段的問題。

MySQL 中的存儲過程

創建與調用過程

創建存儲過程，代碼如下所示：

-- 創建存儲過程 
create procedure mypro(in a int,in b int,out sum int) 
begin 
set sum = a+b; 
end;

運行結果如下

也可以在 Navicat 客戶端“函數”節點下查看過程，如下圖所示：

調用存儲過程，代碼如下所示：

call mypro(1,2,@s);-- 調用存儲過程 
select @s;-- 顯示過程輸出結果

運行結果

存儲過程語法解析

• create procedure 用來創建過程；
• mypro 用來定義過程名稱；
• (in a int,in b int,out sum int)表示過程的參數，其中 in 表示輸入參數，out 表示輸出參數。類似於 Java 定義方法時的形參和返回值；
• begin 與end 表示過程主體的開始和結束，相當於 Java 定義方法的一對大括號；
• call用來調用過程，@s 是用來接收過程輸出參數的變量

存儲過程的參數

MySQL 存儲過程的參數用在存儲過程的定義，共有三種參數類型：

• IN 輸入參數：表示調用者向過程傳入值（傳入值可以是字面量或變量）；
• OUT 輸出參數：表示過程向調用者傳出值(可以返回多個值)（傳出值隻能是變量）；
• INOUT輸入輸出參數：既表示調用者向過程傳入值，又表示過程向調用者傳出值（值隻能是變量）。

存儲過程根據參數可分為四種類別：

1).沒有參數的過程；

2).隻有輸入參數的過程；

3).隻有輸出參數的過程；

4).包含輸入和輸出參數的過程。

變量

MySQL 中的存儲過程類似 java 中的方法。

既然如此，在存儲過程中也同樣可以使用變量。java 中的局部變量作用域是變量所在的方法，而 MySQL 中的局部變量作用域是所在的存儲過程。

變量定義

DECLARE variable_name [,variable_name...] datatype [DEFAULT value];

declare用於聲明變量；

variable_name表示變量名稱；

datatype為 MySQL 的數據類型；

default用於聲明默認值;

例如：

declare name varchar(20) default ‘jack'。

變量賦值

SET 變量名 = 表達式值 [,variable_name = expression ...]

在存儲過程中使用變量，代碼如下所示

use schooldb;-- 使用 schooldb 數據庫
-- 創建過程
create procedure mypro1()
begin
declare name varchar(20);
set name = '丘處機';
select * from studentinfo where studentname = name;
end;
-- 調用過程
call mypro1();

運行結果

流程控制語句

if 條件語句

IF 語句包含多個條件判斷，根據結果為 TRUE、FALSE執行語句，與編程語言中的 if、else if、else 語法類似。

定義存儲過程，輸入一個整數，使用 if 語句判斷是正數還是負數，代碼如下所示：

-- 創建過程
create procedure mypro2(in num int)
begin
if num<0 then -- 條件開始
select '負數';
elseif num=0 then
select '不是正數也不是負數';
else
select '正數';
end if;-- 條件結束
end;
-- 調用過程
call mypro2(-1);

運行結果

case 條件語句

case是另一個條件判斷的語句，類似於編程語言中的 choose、when語法。MySQL 中的 case語句有兩種語法
格式。

定義存儲過程，輸入一個整數，使用 case 語句判斷是正數還是負數，代碼如下所示：

-- 創建過程
create procedure mypro3(in num int)
begin
case -- 條件開始
when num<0 then select '負數';
when num=0 then select '不是正數也不是負數';
else select '正數';
end case; -- 條件結束
end;
-- 調用過程
call mypro3(1);

運行結果

定義存儲過程，輸入一個整數，使用 case 語句判斷是 1 還是 2，代碼如下所示：

-- 創建過程
create procedure mypro4(in num int)
begin
case num -- 條件開始
when 1 then select '數值是 1';
when 2 then select '數值是 2';
else select '不是 1 也不是 2';
end case; -- 條件結束
end;
-- 調用過程
call mypro4(3);

運行結果

兩種 case 語法都可以實現條件判斷，但第一種適合范圍值判斷，而第二種適合確定值判斷。

while 循環語句

while語句的用法和 java中的 while循環類似。

定義存儲過程，使用 while 循環輸出 1 到 10 的累加和，代碼如下所示：

-- 創建過程
create procedure mypro5(out sum int)
begin
declare num int default 0;
set sum = 0;
while num<10 do -- 循環開始
set num = num+1;
set sum = sum+num;
end while; -- 循環結束
end;
-- 調用過程
call mypro5(@sum);
-- 查詢變量值
select @sum;

運行結果

repeat 循環語句

repeat語句的用法和 java中的 do…while 語句類似，都是先執行循環操作，再判斷條件，區別是 repeat表達
式值為 false時才執行循環操作，直到表達式值為 true停止。

定義存儲過程，使用 repeat 循環輸出 1 到 10 的累加和，代碼如下所示：

-- 創建過程
create procedure mypro6(out sum int)
begin
declare num int default 0;
set sum = 0;
repeat-- 循環開始
set num = num+1;
set sum = sum+num;
until num>=10
end repeat; -- 循環結束
end;
-- 調用過程
call mypro6(@sum);
-- 查詢變量值
select @sum;

運行結果

loop 循環語句

循環語句，用來重復執行某些語句。

執行過程中可使用 leave語句或 iterate 跳出循環，也可以嵌套 IF等判斷語句。

leave語句效果相當於 java 中的 break，用來終止循環;

iterate語句效果相當於 java 中的 continue，用來結束本次循環操作，進入下一次循環。

定義存儲過程，使用 loop 循環輸出 1 到 10 的累加和，代碼如下所示：

-- 創建過程
create procedure mypro7(out sum int)
begin
declare num int default 0;
set sum = 0;
loop_sum:loop-- 循環開始
set num = num+1;
set sum = sum+num;
if num>=10 then
leave loop_sum;
end if;
end loop loop_sum; -- 循環結束
end;
-- 調用過程
call mypro7(@sum);
-- 查詢變量值
select @sum;

運行結果

代碼中的 loop_sum 相當於給循環貼個標簽，方便多重循環時靈活操作。

存儲過程的管理

存儲過程的管理主要包括：顯示過程、顯示過程源碼、刪除過程。

比較簡單的方式就是利用 navicat 客戶端工具進行管理，鼠標點擊操作即可，如下圖所示：

顯示存儲過程

SHOW PROCEDURE STATUS;

顯示特定數據庫的存儲過程

SHOW PROCEDURE status where db = 'schooldb';

顯示特定模式的存儲過程，要求顯示名稱中包含“my”的存儲過程

SHOW PROCEDURE status where name like '%my%';

顯示存儲過程“mypro1”的源碼

SHOW CREATE PROCEDURE mypro1;

刪除存儲過程“mypro1”

drop PROCEDURE mypro1;

總結

到此這篇關於MySQL存儲過程的創建、調用與管理的文章就介紹到這瞭,更多相關MySQL存儲過程內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：

• MySQL中存儲過程的詳細詳解
• MySQL 遊標的定義與使用方式
• MySQL存儲過程之流程控制while,repeat,loop循環
• MySQL中的遊標和綁定變量
• MySQL數據庫之存儲過程 procedure