2. 一個快速教程

   讓我們來看幾個例子，來演示 MYSQL 存儲程序語言結(jié)構(gòu)和功能的關(guān)鍵要素。對于一個完整的例子，請參閱第二章。

   1.2.1 和 SQL 的集成

   MYSQL 存儲程序語言的最重要方面是它和 SQL 的緊密集成。你不需要依靠諸如 ODBC （開放式數(shù)據(jù)庫連接）或者 JDBC （ Java 數(shù)據(jù)庫連接）等中間軟件“膠水”，在你的存儲程序語言程序中，來構(gòu)建和執(zhí)行 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 關(guān)鍵字表示程序體的開始。程序體包括了過程中的聲明和可執(zhí)行代碼。如果程序體包含的語句多余 1 條 ( 就像這個程序 ) ，那么多條語句應(yīng)該括在一個 BEGIN-END 塊中
   3	這里，我們聲明了一個整數(shù)變量，來保存下面我們將要執(zhí)行的數(shù)據(jù)庫查詢返回的結(jié)果
   5-8	我們運行了一個查詢，來確定由 Guy 撰寫或者合編的書籍總數(shù)。需要特別注意第 6 行：在 SELECT 語句中出現(xiàn)的 INTO 子句充當(dāng)了從數(shù)據(jù)庫到本地存儲程序變量之間的“橋梁”。
   10-12	我們使用了一個簡單 SELECT 語句 ( 例如，沒有 FROM 子句 ) 來顯示書籍的數(shù)量。當(dāng)我們發(fā)出一個不帶 INTO 的 SELECT 語句時，它返回的結(jié)果集就會直接返回到調(diào)用它的程序。這是一個 non-ANSI 擴展，來允許存儲程序輕松的返回結(jié)果集（使用 SQLServer 和其他 RDBMS 工作時的一個常見場景）。
   14	這是一個單行注釋，來解釋 UPDATE 語句的目的
   15-17	Guy 已經(jīng)決定把他名字拼寫成” Guillermo” 。他可能被 Oracle 書籍愛好者追蹤，所以我們對這個書籍表發(fā)出了一個 UPDATE 命令。我們使用內(nèi)置的 REPLACE 函數(shù)來找到所有” GUY” 的實例，并使用” GUILLERMO” 來更換它。

2. 控制和條件邏輯

   當(dāng)然，現(xiàn)實世界中的應(yīng)用程序充滿了復(fù)雜的條件和特殊情況，因此你不大可能只簡單的執(zhí)行一系列的 SQL 語句。存儲程序語言提供了完備的控制和條件語句，所以在一個給定的環(huán)境下，我們能夠控制我們程序的執(zhí)行路徑。這些包括：

   IF 和 CASE 語句

   這兩個語句使用不同的結(jié)構(gòu)都實現(xiàn)了條件邏輯。它們允許你像這樣描述邏輯“如果一本書的頁數(shù)大于 1000 ，那么 ......”

   完整的循環(huán)和迭代控制

   它們包含了簡單循環(huán)、 while 循環(huán)、 repeatuntil 循環(huán)。

   例 1-2 是一個賬戶的支付票據(jù) (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

讓我們在下表中更加詳細的看一下這些代碼：

2. 存儲函數(shù)

   存儲函數(shù)是只返回一個單一值的存儲程序，它可以被使用于內(nèi)建函數(shù)可以使用的任何地方，例如，在 SQL 語句中。例 1-3 在提供了生日后，返回一個人的年齡。

   Example1-3 根據(jù)出生日期計算年齡的存儲函數(shù)

   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	定義一個函數(shù)：它的名稱、輸入?yún)?shù)（一個日期），和返回值（一個整數(shù)）
   2	這個函數(shù)中不包含 SQL 語句。對于使用這個子句有一些爭議，第 3 章和第 10 章有更多的討論。
   4	聲明了一個本地變量來保存我們計算的年齡結(jié)果。
   5-11	IF-ELSE-ENDIF 塊來檢驗今年的生日是否已經(jīng)過了的問題。
   7	如果今年生日已經(jīng)過了，我們可以使用今年減去出生日期來簡單的來計算生日。
   10	否則 ( 也就是今年的生日還沒過 ) 我們需要在我們計算的年齡中減去附加的 1 年。
   12	向調(diào)用程序返回計算的年齡。

   在任何內(nèi)置函數(shù)可以使用的地方，我們都可以使用我們的存儲函數(shù)。在另一個存儲程序中、在一個 SET 語句中，或者像例 1-4 顯示的那樣，在一個 SQL 語句中

   例 1-4. 在一個 SQL 語句中使用存儲函數(shù)（續(xù)）

   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 | +-----------+---------+---------------------+------+

3. 當(dāng)事情出錯 (WhenThings Go Wrong)

   即使我們程序已經(jīng)經(jīng)過了測試，并且沒有 BUG ，用戶輸入也會引起出錯。 MYSQL 存儲程序語言提供了一個很強大的機制來處理錯誤。在例 1-5 中，我們創(chuàng)建了一個過程，該過程創(chuàng)建一個新的產(chǎn)品編號，或者如果這個產(chǎn)品編號已經(jīng)存在，那么就為它更新一個新名字。這個過程使用異常處理來檢測試圖插入一個重復(fù)值錯誤。如果嘗試插入失敗，錯誤將會被捕獲，同時一個 UPDATE 語句將代替 INSERT 被發(fā)出。如果沒有異常處理器，存儲程序執(zhí)行會被終止，異常將會原封不動的返回給調(diào)用程序。

   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 參數(shù)：產(chǎn)品編號和產(chǎn)品名稱
   7	聲明一個變量，我們用它來檢測重復(fù)鍵沖突的發(fā)生。這個變量被初始化為 0(false) ，隨后的代碼能夠確保只有在重復(fù)鍵沖突發(fā)生時，它才會被設(shè)置為 1(true)
   8	定義一個命名條件， duplicate_key ，讓它關(guān)聯(lián) MYSQL 錯誤碼 1062 。雖然這一步不是絕對必要，但是我們建議你這樣定義條件，來提高代碼的可讀性（現(xiàn)在你可以引用錯誤名稱，而不是錯誤碼）
   9	定義一個錯誤處理器，它將捕獲重復(fù)鍵錯誤，然后，在后面的代碼的任何地方，如果發(fā)生重復(fù)鍵沖突，則把變量 l_dupkey_indicator 設(shè)置為 1(true)
   11-12	使用用戶提供的編號和名稱，插入一個新的產(chǎn)品
   14	檢查變量 l_dupkey_indicator 的值。如果它仍然為 0 ，那么 INSERT 操作已經(jīng)成功，我們的任務(wù)已經(jīng)完成。如果它的值已經(jīng)被修改成了 1(true) ，我們知道，這里發(fā)生了重復(fù)鍵沖突，那么我們就運行第 15-17 行的 UPDATE 語句，來改變指定編號的產(chǎn)品名稱。

   錯誤處理是編寫健壯的、可維護的 MYSQL 存儲程序的重要方面。第 6 章將給你一個更加完善的例子，它包含了 MYSQL 存儲程序中各種錯誤處理機制。

4. 觸發(fā)器

   觸發(fā)器是一個存儲程序，它響應(yīng)于數(shù)據(jù)庫內(nèi)部的一個事件而被自動調(diào)用。在 MYSQL5 的實現(xiàn)中，觸發(fā)器只響應(yīng)一個特定表上的 DML 動作而被調(diào)用。觸發(fā)器可以自動計算衍生的或者不規(guī)范的數(shù)據(jù)。例 1-6 展示了一個觸發(fā)器，它保持這樣一個衍生值，當(dāng)雇員的工資發(fā)生變動時， contrib_401K 列的值也會自動的設(shè)置為適當(dāng)?shù)闹怠?

   例 1-6 保持衍生值的觸發(fā)器

   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

   下表中說明了這個相當(dāng)簡短的觸發(fā)器：

   行號	解釋
   1	觸發(fā)器有一個獨一無二的名字。通常，你希望這樣來命名觸發(fā)器，從而解釋其本質(zhì)。例如，這個觸發(fā)器名稱中的” bu” 表示這是一個 BEFOREUPDATE 觸發(fā)器。
   2	定義觸發(fā)器被激活的條件。在這個例子中，觸發(fā)代碼在對 employees 表執(zhí)行 UPDATE 語句之前執(zhí)行。
   3	FOREACHROW 表示觸發(fā)代碼將會在由 DML 語句所影響的每一行上都執(zhí)行一次。在目前的 MYSQL5 的觸發(fā)器實現(xiàn)上，這一條款是強制性的。
   4-10	BEGIN-END 塊定義了觸發(fā)器被觸發(fā)時執(zhí)行的代碼
   5-9	自動填充 employees 表的 contrib_401K 列。如果 salary 列的新數(shù)值小于 50000 ， contrib_401K 列被設(shè)置為 500 ，否則，該值如所示第 8 行的方式計算。

   當(dāng)然，關(guān)于 MYSQL 存儲程序語言有更加多的多的東西，這就是為什么在本書中你有數(shù)百頁材料來學(xué)習(xí)。但是，對于你將要使用存儲程序語言來編寫的這種代碼，這些最初的例子應(yīng)該給你一些好感，其最重要的語法元素和易用性，使得你可以讀寫這些存儲程序語言代碼。

--------------------------------------------------------------------------------

第一次嘗試翻譯一些東西，希望得到大家的支持。如果有什么錯誤，請和我交流。

本書書名：《 MySQLStored Procedure Programming 》，作者： StevenFeuerstein, Guy Harrison

這個翻譯是供我學(xué)習(xí) MYSQL 以及和同行交流的，不作為商業(yè)用途。

?

存儲過程編程4