mysql預存程序有什麼用

mysql預存程序的作用：1、透過把處理封裝在容易使用的單元中，簡化複雜的操作；2、簡化對變動的管理；3、有助於提升應用程式的效能；4 、有助於減少應用程式和資料庫伺服器之間的流量，因為應用程式不必發送多個冗長的SQL語句，而只使用發送預存程序的名稱和參數；5、可增強SQL語句的功能和靈活性，使mysql能完成複雜的判斷和較複雜的運算；6、可提高資料庫的安全性和資料的完整性等等。

本教學操作環境：windows7系統、mysql8版本、Dell G3電腦。

預存程序簡介

#預存程序是一組為了完成特定功能的 SQL 語句集合。使用預存程序的目的是將常用或複雜的工作預先用 SQL 語句寫好並用一個指定名稱儲存起來，這個過程經過編譯和優化後儲存在資料庫伺服器中，因此稱為預存程序。當日後需要資料庫提供與已定義好的預存程序的功能相同的服務時，只需呼叫「CALL預存程序名稱」即可自動完成。

常用操作資料庫的 SQL 語句在執行的時候需要先編譯，然後再執行。預存程序則採用另一種方​​式來執行 SQL 語句。

一個預存程序是一個可程式化的函數，它在資料庫中建立並保存，一般由 SQL 語句和一些特殊的控制結構組成。當希望在不同的應用程式或平台上執行相同的特定功能時，預存程序尤其合適。

MySQL 5.0 版本以前不支援預存程序，這讓 MySQL 在應用程式上大打折扣。 MySQL 從5.0 版本開始支援預存程序，既提高了資料庫的處理速度，同時也提高了資料庫程式設計的靈活性

預存程序是資料庫中的重要功能，預存程序可以用來轉換資料、資料遷移、製作報表，它類似程式語言，一次執行成功，就可以隨時被調用，完成指定的功能操作。

使用預存程序不僅可以提高資料庫的存取效率，同時也可以提高資料庫使用的安全性。

對於呼叫者來說，預存程序封裝了 SQL 語句，呼叫者無需考慮邏輯功能的特定實作過程。只是簡單呼叫即可，它可以被 Java 和 C# 等程式語言呼叫。

預存程序的作用（優點）

• #透過將處理封裝在容易使用的單元中，簡化複雜的操作；

• 簡化對變動的管理。如果表名、列名或業務邏輯有變化。只需要更改預存程序的程式碼，使用它的人員不會改變自己的程式碼；

• 通常預存程序有助於提高應用程式的效能。當創建的預存程序被編譯之後，就儲存在資料庫中。但是，MySQL 實作的預存程序略有不同。 MySQL 預存程序按需編譯。在編譯預存程序之後，MySQL 將其放入快取中。 MySQL 為每個連線維護自己的預存程序快取。如果應用程式在單一連線中多次使用預存程序，則使用編譯版本，否則預存程序的工作方式類似於查詢；

• 預存程序有助於減少應用程式和資料庫伺服器之間的流量，因為應用程式不必傳送多個冗長的SQL 語句，而只使用傳送預存程序的名稱和參數；

• ##可增強SQL 語句的功能和彈性

  
  

  預存程序可以用流程控制語句編寫，有很強的彈性，可以完成複雜的判斷和較複雜的運算。

• 提高資料庫的安全性和資料的完整性
  預存程序提高安全性的一個方案就是把它當作中間元件，在儲存過程中可以對某些表做相關操作，然後預存程序作為介面提供給外部程式。這樣，外部程式無法直接操作資料庫表，只能透過預存程序來操作對應的表，因此在一定程度上，安全性是可以提高的。

• 讓資料獨立
  資料的獨立可以達到解耦的效果，也就是說，程式可以呼叫預存過程，來取代執行多條的 SQL 語句。這種情況下，預存程序把資料同用戶隔離開來，優點就是當資料表的結構改變時，呼叫表不用修改程序，只需要資料庫管理者重新編寫預存程序即可。

預存程序的缺點

• #如果使用大量儲存過程，那麼使用這些存儲過程的每個連接的記憶體使用量將會大大增加。此外，如果您在預存程序中過度使用大量邏輯操作，則 CPU 使用率也會增加，因為 MySQL 資料庫最初的設計專注於高效的查詢，不利於邏輯運算；

• 預存程序的建構使得開發具有複雜業務邏輯的預存程序變得更加困難；

• 很難偵錯預存程序。只有少數資料庫管理系統允許您調試預存程序。不幸的是，MySQL 不提供偵錯預存程序的功能；

• 開發和維護預存程序並不容易。開發和維護預存程序通常需要一個不是所有應用程式開發人員擁有的專業技能。這可能會導致應用程式開發和維護階段的問題。

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執行結果<p><img src="https://img.php.cn/upload/article/000/000/024/fe178bfb1a0eb7dd09df67d79c722d95-4.png" alt="mysql預存程序有什麼用"></p><h4>
<a id="case__169"></a><strong></strong>case 條件語句</h4><p><code></code>case<code>是另一個條件判斷的語句，類似於程式語言中的</code>choose<code>、</code>when<code>語法。 MySQL 中的 </code>case<br>語句有兩種文法</p> 格式。 <p></p>定義預存程序，輸入整數，使用case 語句判斷是正數還是負數，程式碼如下所示：<p></p><pre class="brush:php;toolbar:false">-- 创建过程
create procedure mypro3(in num int)
begin
case -- 条件开始
when num執行結果<p><img src="https://img.php.cn/upload/article/000/000/024/fcb90e661eadc288d2d1d57364b7978a-5.png" alt="mysql預存程序有什麼用"></p><p></p><p></p>###################################################### ##定義預存程序，輸入整數，使用case 語句判斷是1 還是2，程式碼如下：###<pre class="brush:php;toolbar:false">-- 创建过程
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<p>运行结果</p><p><img src="https://img.php.cn/upload/article/000/000/024/fcb90e661eadc288d2d1d57364b7978a-7.png" alt="mysql預存程序有什麼用"></p><h4>
<a id="repeat__243"></a><strong>repeat 循环语句</strong>
</h4><p><code>repeat</code>语句的用法和 <code>java</code>中的 <code>do…while</code> 语句类似，都是先执行循环操作，再判断条件，区别是 <code>repeat</code>表达<br> 式值为 <code>false</code>时才执行循环操作，直到表达式值为 <code>true</code>停止。</p><p>定义存储过程，使用 repeat 循环输出 1 到 10 的累加和，代码如下所示：</p><pre class="brush:php;toolbar:false">-- 创建过程
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預存程序有什麼用的詳細內容。更多資訊請關注PHP中文網其他相關文章！