MySQL基礎教程之存儲過程

註意：

1.使用phpMyAdmin創建存儲過程時定界符要改成‘//’。

2.只能在資料庫管理工具創建存儲過程，不能用php動態創建，php只能調用存儲過程，連接時後面要加上參數

存儲過程

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

在我看來， 存儲過程就是有業務邏輯和流程的集合， 可以在存儲過程中創建表，更新數據， 刪除等等。

為什麼要使用存儲過程

1. 通過把處理封裝在容易使用的單元中，簡化複雜的操作(正如前面例子所述)。
2. 由於不要求反覆建立一系列處理步驟，這保證了數據的完整性。如果所有開發人員和應用程式都使用同一(試驗和測試)存儲過程，則所使用的代碼都是相同的。這一點的延伸就是防止錯誤。需要執行的步驟越多，出錯的可能性就越大。防止錯誤保證了數據的一致性。
3. 簡化對變動的管理。如果表名、列名或業務邏輯(或別的內容)有變化，只需要更改存儲過程的代碼。使用它的人員甚至不需要知道這些變化。

一個簡單的存儲過程

create procedure porcedureName () 
begin 
    select name from user; 
end;  

存儲過程用create procedure 創建， 業務邏輯和sql寫在begin和end之間。mysql中可用call porcedureName ();來調用過程。

-- 調用過程 
call porcedureName ();  

該存儲過程沒有參數， 只是在調用的時候查詢了用戶表的用戶名而已， 調用結果如下

刪除存儲過程

DROP PROCEDURE IF EXISTS porcedureName; -- 沒有括弧() 

使用參數的存儲過程

create procedure procedureName( 
    out min decimal(8,2), 
    out avg decimal(8,2), 
    out max decimal(8,2) 
) 
BEGIN 
    select MIN(price) INTO min from order; 
    select AVG(price) into avg from order; 
    select MAX(price) into max from order; 
END;  

此過程接受三個參數， 分別用於獲取訂單表的最小、平均、最大價格。每個參數必須具有指定的類

型，這裡使用十進位值(decimal(8,2))， 關鍵字OUT指出相應的參數用來從存儲過程傳出

一個值(返回給調用者)

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

為調用此修改過的存儲過程，必須指定3個變數名，如下所示：(所有MySQL變數都必須以@開始。)

-- 由於過程指定三個參數， 故調用必須要參數匹配 
call procedureName(@min, @avg, @max);  

該調用並沒有任何輸出， 只是把調用的結果賦給了調用時傳入的變數(@min, @avg, @max)。然後即可調用顯示該變數的值。

select @min, @avg, @max; 

結果如下

使用in參數, 輸入一個用戶id， 返回該用戶所有訂單的總價格。

create procedure getTotalById ( 
    in userId int, 
    out total decimal(8,2) 
) 
BEGIN 
    select SUM(r.price) from order r 
    where r.u_id = userId 
    into total; 
END;  

調用存儲過程

call getTotalById(1, @total); 
select @total;  

結果將返回該用戶所有訂單的合計價格。

複雜一點的過程， 根據用戶id獲取該用戶的所有訂單價格， 並動態的選擇是否加稅。代碼設計如下

create procedure getTotalByUser2( 
    in userId int, 
    in falg boolean, -- 是否加稅標記 
    out total decimal(8,2) 
) 
begin 
    DECLARE tmptotal DECIMAL(8,2); 
    DECLARE taxrate int DEFAULT 6;-- 預設的加稅的利率 
     
    select SUM(r.price) from order r 
    where r.u_id = userId 
    into tmptotal; 
     
    if flag then 
        select tmptotal + (tmptotal/1000*taxrate) into tmptotal; 
    end if; 
     
    select tmptotal into total; 
END;  

該過程傳入三個參數， 用戶id， 是否加稅以及返回的總價格，在過程內部， 定義兩個局部變數tmptotal和taxrate，把查詢出來的結果賦給臨時變數， 在判斷是否加稅。最後把局部變數的值賦給輸出參數。

call getTotalByUser2(1, false, @total); -- 不加稅 
call getTotalByUser2(1, true, @total);  -- 加稅 
select @total;