MySQL預存程序深入理解

MySQL預存程序深入理解

預存程序是儲存在資料庫伺服器中的一組sql語句，通過在查詢中調用一個指定的名稱來執行這些sql語句命令。

簡介

SQL語句需要先編譯然後執行，而預存程序（Stored Procedure）是一組為了完成特定功能的SQL語句集，經編譯後儲存在資料庫中，使用者通過指定預存程序的名字並給定參數（如果該預存程序帶有參數）來調用執行它。

預存程序是可程式化的函數，在資料庫中建立並儲存，可以由SQL語句和控制結構組成。當想要在不同的應用程式或平台上執行相同的函數，或者封裝特定功能時，預存程序是非常有用的。資料庫中的預存程序可以看做是對編程中物件導向方法的類比，它允許控制資料的訪問方式。

MySQL預存程序的優點

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

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

儲存的程式對任何應用程式都是可重用的和透明的。 預存程序將資料庫介面暴露給所有應用程式，以便開發人員不必開發儲存體過程中已支援的功能。

儲存的程式是安全的。 資料庫管理員可以向訪問資料庫中預存程序的應用程式授予適當的許可權，而不向基礎資料庫表提供任何許可權。

MySQL預存程序的缺點

如果使用大量預存程序，那麼使用這些預存程序的每個串連的記憶體使用量量將會大大增加。 此外，如果您在預存程序中過度使用大量邏輯操作，則CPU使用率也會增加，因為資料庫伺服器的設計不當於邏輯運算。

預存程序的構造使得開發具有複雜商務邏輯的預存程序變得更加困難。

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

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

過程體

過程體的開始與結束使用BEGIN與END進行標識。

預存程序分為有參和無參

無參預存程序：

DELIMITER ;;
CREATE PROCEDURE `select_students_count`()
BEGIN
    SELECT count(id) from students;
END;;
DELIMITER ;
call select_students_count();

有參預存程序：

MySQL支援IN(傳遞給預存程序)，OUT(從預存程序傳出)和INOUT(對預存程序傳入和傳出)類型的參數。預存程序的代碼位於BEGIN和END語句內，如前所見，它們是一系列SELECT語句，用來檢索值，然後儲存到相應的變數(通過指定INTO關鍵字)

預存程序根據需要可能會有輸入、輸出、輸入輸出參數，如果有多個參數用","分割開。MySQL預存程序的參數用在預存程序的定義，共有三種參數類型,IN,OUT,INOUT

IN：僅帶入參的預存程序

參數的值必須在調用預存程序時指定，在預存程序中修改該參數的值不能被返回，為預設值

DELIMITER //
  CREATE PROCEDURE in_param(IN p_in int)
    BEGIN
    SELECT p_in;
    SET p_in=2;
    SELECT p_in;
    END//
DELIMITER ;
#調用
SET @p_in=1;
CALL in_param(@p_in);
SELECT @p_in;

結果：

OUT:僅帶出參的預存程序

該值可在預存程序內部被改變，並可返回

#預存程序OUT參數
DELIMITER //
  CREATE PROCEDURE out_param(OUT p_out int)
    BEGIN
      SELECT p_out;
      SET p_out=2;
      SELECT p_out;
    END//
DELIMITER ;
#調用
SET @p_out=1;
CALL out_param(@p_out);
SELECT @p_out;

結果：

INOUT:帶入參和出參的預存程序

調用時指定，並且可被改變和返回

#預存程序INOUT參數
DELIMITER //
  CREATE PROCEDURE inout_param(INOUT p_inout int)
    BEGIN
      SELECT p_inout;
      SET p_inout=2;
      SELECT p_inout;
    END//
DELIMITER ;
#調用
SET @p_inout=1;
CALL inout_param(@p_inout) ;
SELECT @p_inout;

結果：

本文永久更新連結地址：https://www.bkjia.com/Linux/2018-02/151073.htm