23. 使用MySQL之使用儲存過程

hisun9發表於2024-11-10
MySql儲存過程

1. 儲存過程

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

例如，考慮以下的情形。

  • 為了處理訂單，需要核對以保證庫存中有相應的物品。

  • 如果庫存有物品，這些物品需要預定以便不將它們再賣給別的人，並且要減少可用的物品數量以反映正確的庫存量。

  • 庫存中沒有的物品需要訂購，這需要與供應商進行某種互動。

  • 關於哪些物品入庫（並且可以立即發貨）和哪些物品退訂，需要通知相應的客戶。

這顯然不是一個完整的例子，它甚至超出了本書中所用樣例表的範圍，但足以幫助表達我們的意思了。

執行這個處理需要針對許多表的多條MySQL語句。此外，需要執行的具體語句及其次序也不是固定的，它們可能會（和將）根據哪些物品在庫存中哪些不在而變化。

那麼，怎樣編寫此程式碼？

可以單獨編寫每條語句，並根據結果有條件地執行另外的語句。在每次需要這個處理時（以及每個需要它的應用中）都必須做這些工作。

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

2. 為什麼要使用儲存過程

既然我們知道了什麼是儲存過程，那麼為什麼要使用它們呢？

有許多理由，下面列出一些主要的理由。

  • 透過把處理封裝在容易使用的單元中，簡化複雜的操作（正如前面例子所述）。

  • 由於不要求反覆建立一系列處理步驟，這保證了資料的完整性。如果所有開發人員和應用程式都使用同一（試驗和測試）儲存過程，則所使用的程式碼都是相同的。

    這一點的延伸就是防止錯誤。需要執行的步驟越多，出錯的可能性就越大。防止錯誤保證了資料的一致性。

  • 簡化對變動的管理。如果表名、列名或業務邏輯（或別的內容）有變化，只需要更改儲存過程的程式碼。使用它的人員甚至不需要知道這些變化。

    這一點的延伸就是安全性。透過儲存過程限制對基礎資料的訪問減少了資料訛誤（無意識的或別的原因所導致的資料訛誤）的機會。

  • 提高效能。因為使用儲存過程比使用單獨的SQL語句要快。

  • 存在一些只能用在單個請求中的MySQL元素和特性，儲存過程可以使用它們來編寫功能更強更靈活的程式碼（在下一章的例子中可以看到。）

換句話說，使用儲存過程有3個主要的好處，即簡單、安全、高效能。

顯然，它們都很重要。不過，在將SQL程式碼轉換為儲存過程前，也必須知道它的一些缺陷。

  • 一般來說，儲存過程的編寫比基本SQL語句複雜，編寫儲存過程需要更高的技能，更豐富的經驗。

  • 你可能沒有建立儲存過程的安全訪問許可權。許多資料庫管理員限制儲存過程的建立許可權，允許使用者使用儲存過程，但不允許他們建立儲存過程。

儘管有這些缺陷，儲存過程還是非常有用的，並且應該儘可能地使用。

補充：

  • 不能編寫儲存過程？你依然可以使用：

    MySQL將編寫儲存過程的安全和訪問與執行儲存過程的安全和訪問區分開來。這是好事情。即使你不能（或不想）編寫自己的儲存過程，也仍然可以在適當的時候執行別的儲存過程。

3. 使用儲存過程

使用儲存過程需要知道如何執行（執行）它們。

儲存過程的執行遠比其定義更經常遇到，因此，將從執行儲存過程開始介紹。然後再介紹建立和使用儲存過程。

3.1 執行儲存過程

MySQL稱儲存過程的執行為呼叫，因此MySQL執行儲存過程的語句為CALL。CALL接受儲存過程的名字以及需要傳遞給它的任意引數。

比如：

call productpricing(@pricelow, 
                    @pricehigh, 
                    @priceavg);

其中，執行名為productpricing的儲存過程，它計算並返回產品的最低、最高和平均價格。

儲存過程可以顯示結果，也可以不顯示結果，如稍後所述。

3.2 建立儲存過程

請看一個例子 —— 一個返回產品平均價格的儲存過程。

create procedure productpricing()
begin
	select avg(prod_price) as priceaverage
    from products;
end;

插個題外話

自己按照上面那個寫的話會報錯，寫成這樣就不必報錯了

img

然後也成功建立了

img

迴歸正題

稍後介紹第一條和最後一條語句。此儲存過程名為productpricing，用CREATE PROCEDURE productpricing()語句定義。如果儲存過程接受引數，它們將在()中列舉出來。此儲存過程沒有引數，但後跟的()仍然需要。BEGIN和END語句用來限定儲存過程體，過程體本身僅是一個簡單的SELECT語句（使用第12章介紹的Avg()函式）。

在MySQL處理這段程式碼時，它建立一個新的儲存過程productpricing。沒有返回資料，因為這段程式碼並未呼叫儲存過程，這裡只是為以後使用而建立它。

注意：

  • mysql命令列客戶機的分隔符

    如果使用的是mysql命令列實用程式，應該仔細閱讀此說明。預設的MySQL語句分隔符為;（正如已經在迄今為止所使用的MySQL語句中所看到的那樣）。mysql命令列實用程式也使用;作為語句分隔符。如果命令列實用程式要解釋儲存過程自身內的;字元，則它們最終不會成為儲存過程的成分，這會使儲存過程中的SQL出現句法錯誤。

    插個題外話

    這句話的意思是，在 MySQL 命令列中使用 CREATE PROCEDURE 定義儲存過程時，BEGIN...END 程式碼塊中的每條 SQL 語句通常以分號 (😉 結尾。然而，MySQL 預設將分號視為語句的結束符號，所以如果不改變分隔符，MySQL 會在遇到第一個分號時誤認為儲存過程定義已經結束，導致出現語法錯誤。

    迴歸正題

    解決辦法是臨時更改命令列實用程式的語句分隔符，如下所示：

    delimiter //

create procedure productpricing()
begin
    select avg(prod_price) as priceaverage
    from products;
end //   

delimiter ;

    其中，DELIMITER //告訴命令列實用程式使用//作為新的語句結束分隔符，可以看到標誌儲存過程結束的END定義為END //而不是END;。這樣，儲存過程體內的;仍然保持不動，並且正確地傳遞給資料庫引擎。最後，為恢復為原來的語句分隔符，可使用DELIMITER ;。

    除\符號外，任何字元都可以用作語句分隔符。

那麼，如何使用這個儲存過程？如下所示：

call productpricing();

輸出如下：

img

CALL productpricing();執行剛建立的儲存過程並顯示返回的結果。因為儲存過程實際上是一種函式，所以儲存過程名後需要有()符號（即使不傳遞引數也需要）。

3.3 刪除儲存過程

儲存過程在建立之後，被儲存在伺服器上以供使用，直至被刪除。刪除命令（類似於第21章所介紹的語句）從伺服器中刪除儲存過程。

為刪除剛建立的儲存過程，可使用以下語句：

drop procedure productpricing;

這條語句刪除剛建立的儲存過程。請注意沒有使用後面的()，只給出儲存過程名。

補充：

  • 僅當存在時刪除：

    如果指定的過程不存在，則DROP PROCEDURE將產生一個錯誤。當過程存在想刪除它時（如果過程不存在也不產生錯誤）可使用DROP PROCEDURE IF EXISTS。

    比如：

    img

3.4 使用引數

productpricing只是一個簡單的儲存過程，它簡單地顯示SELECT語句的結果。一般，儲存過程並不顯示結果，而是把結果返回給指定的變數。

補充：

  • 變數（variable）：

    記憶體中一個特定的位置，用來臨時儲存資料

以下是productpricing的修改版本（如果不先刪除此儲存過程，則不能再次建立它）：

delimiter //

create procedure productpricing(
	out p1 decimal(8,2),
    out ph decimal(8,2),
    out pa decimal(8,2)
)
begin
	select min(prod_price)
    into p1
    from products;
    select max(prod_price)
    into ph
    from products;
    select avg(prod_price)
    into pa
    from products;
end // 

delimiter ;

此儲存過程接受3個引數：pl儲存產品最低價格，ph儲存產品最高價格，pa儲存產品平均價格。每個引數必須具有指定的型別，這裡使用十進位制值。關鍵字OUT指出相應的引數用來從儲存過程傳出一個值（返回給呼叫者）。MySQL支援IN（傳遞給儲存過程）、OUT（從儲存過程傳出，如這裡所用）和INOUT（對儲存過程傳入和傳出）型別的引數。儲存過程的程式碼位於BEGIN和END語句內，如前所見，它們是一系列SELECT語句，用來檢索值，然後儲存到相應的變數（透過指定INTO關鍵字）。

補充：

  • 引數的資料型別：

    儲存過程的引數允許的資料型別與表中使用的資料型別相同。

    注意，記錄集不是允許的型別，因此，不能透過一個引數返回多個行和列。這就是前面的例子為什麼要使用3個引數（和3條SELECT語句）的原因。

為呼叫此修改過的儲存過程，必須指定3個變數名，如下所示：

call productpricing(@pricelow,
					@pricehigh,
                    @priceaverage);

由於此儲存過程要求3個引數，因此必須正好傳遞3個引數，不多也不少。所以，這條CALL語句給出3個引數。它們是儲存過程將儲存結果的3個變數的名字。

注意：

  • 變數名：

    所有MySQL變數都必須以@開始。

在呼叫時，這條語句並不顯示任何資料。它返回以後可以顯示（或在其他處理中使用）的變數。

為了顯示檢索出的產品平均價格，可如下進行：

select @priceaverage;

輸出如下：

img

為了獲得3個值，可使用以下語句：

select @pricelow, @pricehigh, @priceaverage;

輸出如下：

img

下面是另外一個例子，這次使用IN和OUT引數。ordertotal接受訂單
號並返回該訂單的合計：

delimiter //

create procedure ordertotal(
	in onumber int,
    out ototal decimal(8,2)
)
begin
	select sum(item_price*quantity)
    from orderitems
    where order_num = onumber
    into ototal;
end //

delimiter ;

onumber定義為IN，因為訂單號被傳入儲存過程。ototal定義為OUT，因為要從儲存過程返回合計。SELECT語句使用這兩個引數，WHERE子句使用onumber選擇正確的行，INTO使用ototal儲存計算出來的合計。

為呼叫這個新儲存過程，可使用以下語句：

call ordertotal(20005, @total);

必須給ordertotal傳遞兩個引數；第一個引數為訂單號，第二個引數為包含計算出來的合計的變數名。

為了顯示此合計，可如下進行：

select @total;

輸出如下：

img

@total已由ordertotal的CALL語句填寫，SELECT顯示它包含的值。

為了得到另一個訂單的合計顯示，需要再次呼叫儲存過程，然後重新顯示變數：

call ordertotal(20009, @total);
select @total;

輸出如下：

img

3.5 建立智慧儲存過程

迄今為止使用的所有儲存過程基本上都是封裝MySQL簡單的SELECT語句。雖然它們全都是有效的儲存過程例子，但它們所能完成的工作你直接用這些被封裝的語句就能完成（如果說它們還能帶來更多的東西，那就是使事情更復雜）。

只有在儲存過程內包含業務規則和智慧處理時，它們的威力才真正顯現出來。

考慮這個場景。你需要獲得與以前一樣的訂單合計，但需要對合計增加營業稅，不過只針對某些顧客（或許是你所在州中那些顧客）。那麼，你需要做下面幾件事情：

  • 獲得合計（與以前一樣）；

  • 把營業稅有條件地新增到合計；

  • 返回合計（帶或不帶稅）。

儲存過程的完整工作如下：

delimiter //
create procedure ordertotal(
	in onumber int,
    in taxable boolean,
    out ototal decimal(8,2)
) comment 'Obtain order total, optionally adding tax'
begin
	-- Declare variable for total
    declare total decimal(8,2);
    -- Declare tax percentage
    declare taxrate int default 6;
    
    -- Get the order total
    select sum(item_price*quanitity)
    from orderitems
    where order_num = onumber
    into total;
    
    -- Is this taxable?
    if taxable then
		-- Yes, so add taxrate to the total
        select total+(total/100*taxrate) into total;
	end if;
    
    -- And finally, save to out variable
    select total into ototal;
end //
delimiter ;

此儲存過程有很大的變動。首先，增加了註釋（前面放置--）。在儲存過程複雜性增加時，這樣做特別重要。新增了另外一個引數taxable，它是一個布林值（如果要增加稅則為真，否則為假）。在儲存過程體中，用DECLARE語句定義了兩個區域性變數。DECLARE要求指定變數名和資料型別，它也支援可選的預設值（這個例子中的taxrate的預設被設定為6%）。SELECT語句已經改變，因此其結果儲存到total（區域性變數）而不是ototal。IF語句檢查taxable是否為真，如果為真，則用另一SELECT語句增加營業稅到區域性變數total。最後，用另一SELECT語句將total（它增加或許不增加營業稅）儲存到ototal。

補充：

  • COMMENT關鍵字：

    本例子中的儲存過程在CREATE PROCEDURE語句中包含了一個COMMENT值。它不是必需的，但如果給出，將在SHOW PROCEDURE STATUS的結果中顯示。

    比如：

    img

這顯然是一個更高階，功能更強的儲存過程。為試驗它，請用以下兩條語句：

call ordertotal(20005, 0, @total);
select @total;

輸出如下：

img

call ordertotal(20005, 1, @total);
select @total;

img

BOOLEAN值指定為1表示真，指定為0表示假（實際上，非零值都考慮為真，只有0被視為假）。透過給中間的引數指定0或1，可以有條件地將營業稅加到訂單合計上。

補充：

  • IF語句：

    這個例子給出了MySQL的IF語句的基本用法。IF語句還支援ELSEIF和ELSE子句（前者還使用THEN子句，後者不使用）。在以後章節中我們將會看到IF的其他用法（以及其他流控制語句）。

3.6 檢查儲存過程

為顯示用來建立一個儲存過程的CREATE語句，使用SHOW CREATE PROCEDURE 語句：

show create procedure ordertotal;

輸出如下：

img

img

為了獲得包括何時、由誰建立等詳細資訊的儲存過程列表，使用SHOW PROCEDURE STATUS。

比如：

img

補充：

  • 限制過程狀態結果：

    SHOW PROCEDURE STATUS列出所有儲存過程。為限制其輸出，可使用LIKE指定一個過濾模式，例如：

    show procedure status like 'ordertotal';

    輸出如下：

    img

相關文章