-
一個快速教程
讓我們來看幾個例子，來演示 MYSQL 存儲程序語言結構和功能的關鍵要素。對于一個完整的例子，請參閱第二章。
1.2.1 和 SQL 的集成
MYSQL 存儲程序語言的最重要方面是它和 SQL 的緊密集成。你不需要依靠諸如 ODBC （開放式數據庫連接）或者 JDBC （ Java 數據庫連接）等中間軟件“膠水”，在你的存儲程序語言程序中，來構建和執行 SQL 語句。相反，你只需簡單的在你的代碼中直接編寫 UPDATE 、 INSERT 、 DELETE 和 SELECT 語句，如例 1-1 所示
Example1-1 在存儲程序中嵌入 SQL
1 CREATE PROCEDURE example1( )
2 BEGIN
3 DECLARE l_book_count INTEGER;
4
5 SELECT COUNT(*)
6 INTO l_book_count
7 FROM books
8 WHERE author LIKE '%HARRISON,GUY%';
9
10 SELECT CONCAT('Guy has written (or co-written) ',
11 l_book_count ,
12 ' books.');
13
14 -- Oh, and I changed my name, so...
15 UPDATE books
16 SET author = REPLACE (author, 'GUY', 'GUILLERMO')
17 WHERE author LIKE '%HARRISON,GUY%';
18
19END
讓我們在下表中更加詳細的看一下這些代碼：
行號
解釋
1
本段是程序的頭部，定義了我們存儲程序的名稱 (example1) 和類型 (PROCEDURE)
2
BEGIN 關鍵字表示程序體的開始。程序體包括了過程中的聲明和可執行代碼。如果程序體包含的語句多余 1 條 ( 就像這個程序 ) ，那么多條語句應該括在一個 BEGIN-END 塊中
3
這里，我們聲明了一個整數變量，來保存下面我們將要執行的數據庫查詢返回的結果
5-8
我們運行了一個查詢，來確定由 Guy 撰寫或者合編的書籍總數。需要特別注意第 6 行：在 SELECT 語句中出現的 INTO 子句充當了從數據庫到本地存儲程序變量之間的“橋梁”。
10-12
我們使用了一個簡單 SELECT 語句 ( 例如，沒有 FROM 子句 ) 來顯示書籍的數量。當我們發出一個不帶 INTO 的 SELECT 語句時，它返回的結果集就會直接返回到調用它的程序。這是一個 non-ANSI 擴展，來允許存儲程序輕松的返回結果集（使用 SQLServer 和其他 RDBMS 工作時的一個常見場景）。
14
這是一個單行注釋，來解釋 UPDATE 語句的目的
15-17
Guy 已經決定把他名字拼寫成” Guillermo” 。他可能被 Oracle 書籍愛好者追蹤，所以我們對這個書籍表發出了一個 UPDATE 命令。我們使用內置的 REPLACE 函數來找到所有” GUY” 的實例，并使用” GUILLERMO” 來更換它。
-
控制和條件邏輯
當然，現實世界中的應用程序充滿了復雜的條件和特殊情況，因此你不大可能只簡單的執行一系列的 SQL 語句。存儲程序語言提供了完備的控制和條件語句，所以在一個給定的環境下，我們能夠控制我們程序的執行路徑。這些包括：
IF 和 CASE 語句
這兩個語句使用不同的結構都實現了條件邏輯。它們允許你像這樣描述邏輯“如果一本書的頁數大于 1000 ，那么 ......”
完整的循環和迭代控制
它們包含了簡單循環、 while 循環、 repeatuntil 循環。
例 1-2 是一個賬戶的支付票據 (paysout the balance of an account to cover outstandingbills) 的過程，來演示 MYSQL 的控制語句。
1 CREATE PROCEDURE pay_out_balance
2 (account_id_in INT)
3
4 BEGIN
5
6 DECLARE l_balance_remaining NUMERIC(10,2);
7
8 payout_loop:LOOP
9 SET l_balance_remaining = account_balance(account_id_in);
10
11 IF l_balance_remaining < 1000 THEN
12 LEAVE payout_loop;
13
14 ELSE
15 CALL apply_balance(account_id_in, l_balance_remaining);
16 END IF;
17
18 END LOOP;
19
20END
讓我們在下表中更加詳細的看一下這些代碼：
|
行號 |
解釋 |
|
1-3 |
這是我們的過程的頭部；第 2 行包括了該過程的參數列表，在本例中，包含了一個輸入參數（帳號的標識符編號） |
|
6 |
聲明了一個變量來保存帳號余額 |
|
8-18 |
這個簡短循環 ( 這樣稱呼是因為它以關鍵字 LOOP 開始，而不是以 WHILE 或 REPEAT) 一直迭代，直到賬戶余額低于 1000. 在 MYSQL 中，我們可以對循環進行命名 ( 第 8 行 ,payout_loop) ，這就允許我們使用 LEAVE 語句 ( 見第 12 行 ) 終止特定循環。當離開一個循環后， MYSQL 引擎接下來會執行 ENDLOOP 語句（第 18 行）后的下一個可執行語句。 |
|
9 |
調用 account_balance 函數（必須在先前已經定義）來查詢該賬戶余額。 MYSQL 允許你在另一個存儲程序中調用一個存儲程序，從而達到代碼的重用。因為這個程序 (account_balance) 是一個函數，它返回一個值，并且能夠被 MysqlSET 調用并進行賦值。 |
|
11-16 |
如果賬戶余額低于 $1000 ，這個 IF 語句將終止循環。否則（ ELSE 子句）它將應用于下一個支付。你也可以使用 ELSEIF 子句來構建更加復雜的布爾表達式。 |
|
15 |
調用 apply_balance 過程。這是一個代碼重用的例子；我們調用一個例程，而不是把 apply_balance 的邏輯在本過程中重寫。 |
-
存儲函數
存儲函數是只返回一個單一值的存儲程序，它可以被使用于內建函數可以使用的任何地方，例如，在 SQL 語句中。例 1-3 在提供了生日后，返回一個人的年齡。
Example1-3 根據出生日期計算年齡的存儲函數
1CREATE FUNCTION f_age (in_dob datetime) returns int
2 NO SQL
3BEGIN
4 DECLARE l_age INT;
5 IF DATE_FORMAT(NOW( ),'00-%m-%d') >=DATE_FORMAT(in_dob,'00-%m-%d') THEN
6 -- This person has had a birthday this year
7 SET l_age=DATE_FORMAT(NOW( ),'%Y')-DATE_FORMAT(in_dob,'%Y');
8 ELSE
9 -- Yet to have a birthday this year
10 SET l_age=DATE_FORMAT(NOW( ),'%Y')-DATE_FORMAT(in_dob,'%Y')-1;
11 END IF;
12 RETURN(l_age);
END;
讓我們在下表中逐步看一下這些代碼：
行號
解釋
1
定義一個函數：它的名稱、輸入參數（一個日期），和返回值（一個整數）
2
這個函數中不包含 SQL 語句。對于使用這個子句有一些爭議，第 3 章和第 10 章有更多的討論。
4
聲明了一個本地變量來保存我們計算的年齡結果。
5-11
IF-ELSE-ENDIF 塊來檢驗今年的生日是否已經過了的問題。
7
如果今年生日已經過了，我們可以使用今年減去出生日期來簡單的來計算生日。
10
否則 ( 也就是今年的生日還沒過 ) 我們需要在我們計算的年齡中減去附加的 1 年。
12
向調用程序返回計算的年齡。
在任何內置函數可以使用的地方，我們都可以使用我們的存儲函數。在另一個存儲程序中、在一個 SET 語句中，或者像例 1-4 顯示的那樣，在一個 SQL 語句中
例 1-4. 在一個 SQL 語句中使用存儲函數（續）
mysql>SELECT firstname,surname, date_of_birth, f_age(date_of_birth)AS age
-> FROM employees LIMIT 5;
+-----------+---------+---------------------+------+
|firstname | surname | date_of_birth | age |
+-----------+---------+---------------------+------+
|LUCAS | FERRIS | 1984-04-17 07:04:27 | 21 |
|STAFFORD | KIPP | 1953-04-22 06:04:50 | 52 |
|GUTHREY | HOLMES | 1974-09-12 08:09:22 | 31 |
|TALIA | KNOX | 1966-08-14 11:08:14 | 39 |
|JOHN | MORALES | 1956-06-22 07:06:14 | 49 |
+-----------+---------+---------------------+------+
-
當事情出錯 (WhenThings Go Wrong)
即使我們程序已經經過了測試，并且沒有 BUG ，用戶輸入也會引起出錯。 MYSQL 存儲程序語言提供了一個很強大的機制來處理錯誤。在例 1-5 中，我們創建了一個過程，該過程創建一個新的產品編號，或者如果這個產品編號已經存在，那么就為它更新一個新名字。這個過程使用異常處理來檢測試圖插入一個重復值錯誤。如果嘗試插入失敗，錯誤將會被捕獲，同時一個 UPDATE 語句將代替 INSERT 被發出。如果沒有異常處理器，存儲程序執行會被終止，異常將會原封不動的返回給調用程序。
Example1-5 存儲程序中的錯誤處理
1 CREATE PROCEDURE sp_product_code
2 (in_product_code VARCHAR(2),
3 in_product_name VARCHAR(30))
4
5 BEGIN
6
7 DECLARE l_dupkey_indicator INT DEFAULT 0;
8 DECLARE duplicate_key CONDITION FOR 1062;
9 DECLARE CONTINUE HANDLER FOR duplicate_key SETl_dupkey_indicator =1;
10
11 INSERT INTO product_codes (product_code, product_name)
12 VALUES (in_product_code, in_product_name);
13
14 IF l dupkey_indicator THEN
15 UPDATE product_codes
16 SET product_name=in_product_name
17 WHERE product_code=in_product_code;
18 END IF;
19
20END
讓我們詳細的看一下這些代碼的錯誤處理部分：
行號
解釋
1-4
存儲過程頭部，允許兩個 IN 參數：產品編號和產品名稱
7
聲明一個變量，我們用它來檢測重復鍵沖突的發生。這個變量被初始化為 0(false) ，隨后的代碼能夠確保只有在重復鍵沖突發生時，它才會被設置為 1(true)
8
定義一個命名條件， duplicate_key ，讓它關聯 MYSQL 錯誤碼 1062 。雖然這一步不是絕對必要，但是我們建議你這樣定義條件，來提高代碼的可讀性（現在你可以引用錯誤名稱，而不是錯誤碼）
9
定義一個錯誤處理器，它將捕獲重復鍵錯誤，然后，在后面的代碼的任何地方，如果發生重復鍵沖突，則把變量 l_dupkey_indicator 設置為 1(true)
11-12
使用用戶提供的編號和名稱，插入一個新的產品
14
檢查變量 l_dupkey_indicator 的值。如果它仍然為 0 ，那么 INSERT 操作已經成功，我們的任務已經完成。如果它的值已經被修改成了 1(true) ，我們知道，這里發生了重復鍵沖突，那么我們就運行第 15-17 行的 UPDATE 語句，來改變指定編號的產品名稱。
錯誤處理是編寫健壯的、可維護的 MYSQL 存儲程序的重要方面。第 6 章將給你一個更加完善的例子，它包含了 MYSQL 存儲程序中各種錯誤處理機制。
-
觸發器
觸發器是一個存儲程序，它響應于數據庫內部的一個事件而被自動調用。在 MYSQL5 的實現中，觸發器只響應一個特定表上的 DML 動作而被調用。觸發器可以自動計算衍生的或者不規范的數據。例 1-6 展示了一個觸發器，它保持這樣一個衍生值，當雇員的工資發生變動時， contrib_401K 列的值也會自動的設置為適當的值。
例 1-6 保持衍生值的觸發器
1 CREATE TRIGGER employees_trg_bu
2 BEFORE UPDATE ON employees
3 FOR EACH ROW
4 BEGIN
5 IF NEW.salary <50000 THEN
6 SET NEW.contrib_401K=500;
7 ELSE
8 SET NEW.contrib_401K=500+(NEW.salary-50000)*.01;
9 END IF;
10 END
下表中說明了這個相當簡短的觸發器：
行號
解釋
1
觸發器有一個獨一無二的名字。通常，你希望這樣來命名觸發器，從而解釋其本質。例如，這個觸發器名稱中的” bu” 表示這是一個 BEFOREUPDATE 觸發器。
2
定義觸發器被激活的條件。在這個例子中，觸發代碼在對 employees 表執行 UPDATE 語句之前執行。
3
FOREACHROW 表示觸發代碼將會在由 DML 語句所影響的每一行上都執行一次。在目前的 MYSQL5 的觸發器實現上，這一條款是強制性的。
4-10
BEGIN-END 塊定義了觸發器被觸發時執行的代碼
5-9
自動填充 employees 表的 contrib_401K 列。如果 salary 列的新數值小于 50000 ， contrib_401K 列被設置為 500 ，否則，該值如所示第 8 行的方式計算。
當然，關于 MYSQL 存儲程序語言有更加多的多的東西，這就是為什么在本書中你有數百頁材料來學習。但是，對于你將要使用存儲程序語言來編寫的這種代碼，這些最初的例子應該給你一些好感，其最重要的語法元素和易用性，使得你可以讀寫這些存儲程序語言代碼。
--------------------------------------------------------------------------------
第一次嘗試翻譯一些東西，希望得到大家的支持。如果有什么錯誤，請和我交流。
本書書名：《 MySQLStored Procedure Programming 》，作者： StevenFeuerstein, Guy Harrison
這個翻譯是供我學習 MYSQL 以及和同行交流的，不作為商業用途。
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
