2. 一個(gè)快速教程

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

   1.2.1 和 SQL 的集成

   MYSQL 存儲(chǔ)程序語(yǔ)言的最重要方面是它和 SQL 的緊密集成。你不需要依靠諸如 ODBC （開(kāi)放式數(shù)據(jù)庫(kù)連接）或者 JDBC （ Java 數(shù)據(jù)庫(kù)連接）等中間軟件“膠水”，在你的存儲(chǔ)程序語(yǔ)言程序中，來(lái)構(gòu)建和執(zhí)行 SQL 語(yǔ)句。相反，你只需簡(jiǎn)單的在你的代碼中直接編寫(xiě) UPDATE 、 INSERT 、 DELETE 和 SELECT 語(yǔ)句，如例 1-1 所示

   Example1-1 在存儲(chǔ)程序中嵌入 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

   讓我們?cè)谙卤碇懈釉敿?xì)的看一下這些代碼：

   行號(hào)	解釋
   1	本段是程序的頭部，定義了我們存儲(chǔ)程序的名稱(chēng) (example1) 和類(lèi)型 (PROCEDURE)
   2	BEGIN 關(guān)鍵字表示程序體的開(kāi)始。程序體包括了過(guò)程中的聲明和可執(zhí)行代碼。如果程序體包含的語(yǔ)句多余 1 條 ( 就像這個(gè)程序 ) ，那么多條語(yǔ)句應(yīng)該括在一個(gè) BEGIN-END 塊中
   3	這里，我們聲明了一個(gè)整數(shù)變量，來(lái)保存下面我們將要執(zhí)行的數(shù)據(jù)庫(kù)查詢(xún)返回的結(jié)果
   5-8	我們運(yùn)行了一個(gè)查詢(xún)，來(lái)確定由 Guy 撰寫(xiě)或者合編的書(shū)籍總數(shù)。需要特別注意第 6 行：在 SELECT 語(yǔ)句中出現(xiàn)的 INTO 子句充當(dāng)了從數(shù)據(jù)庫(kù)到本地存儲(chǔ)程序變量之間的“橋梁”。
   10-12	我們使用了一個(gè)簡(jiǎn)單 SELECT 語(yǔ)句 ( 例如，沒(méi)有 FROM 子句 ) 來(lái)顯示書(shū)籍的數(shù)量。當(dāng)我們發(fā)出一個(gè)不帶 INTO 的 SELECT 語(yǔ)句時(shí)，它返回的結(jié)果集就會(huì)直接返回到調(diào)用它的程序。這是一個(gè) non-ANSI 擴(kuò)展，來(lái)允許存儲(chǔ)程序輕松的返回結(jié)果集（使用 SQLServer 和其他 RDBMS 工作時(shí)的一個(gè)常見(jiàn)場(chǎng)景）。
   14	這是一個(gè)單行注釋?zhuān)瑏?lái)解釋 UPDATE 語(yǔ)句的目的
   15-17	Guy 已經(jīng)決定把他名字拼寫(xiě)成” Guillermo” 。他可能被 Oracle 書(shū)籍愛(ài)好者追蹤，所以我們對(duì)這個(gè)書(shū)籍表發(fā)出了一個(gè) UPDATE 命令。我們使用內(nèi)置的 REPLACE 函數(shù)來(lái)找到所有” GUY” 的實(shí)例，并使用” GUILLERMO” 來(lái)更換它。

2. 控制和條件邏輯

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

   IF 和 CASE 語(yǔ)句

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

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

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

   例 1-2 是一個(gè)賬戶(hù)的支付票據(jù) (paysout the balance of an account to cover outstandingbills) 的過(guò)程，來(lái)演示 MYSQL 的控制語(yǔ)句。

   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

讓我們?cè)谙卤碇懈釉敿?xì)的看一下這些代碼：

2. 存儲(chǔ)函數(shù)

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

   Example1-3 根據(jù)出生日期計(jì)算年齡的存儲(chǔ)函數(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;

   讓我們?cè)谙卤碇兄鸩娇匆幌逻@些代碼：

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

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

   例 1-4. 在一個(gè) SQL 語(yǔ)句中使用存儲(chǔ)函數(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)事情出錯(cuò) (WhenThings Go Wrong)

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

   Example1-5 存儲(chǔ)程序中的錯(cuò)誤處理

   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

   讓我們?cè)敿?xì)的看一下這些代碼的錯(cuò)誤處理部分：

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

   錯(cuò)誤處理是編寫(xiě)健壯的、可維護(hù)的 MYSQL 存儲(chǔ)程序的重要方面。第 6 章將給你一個(gè)更加完善的例子，它包含了 MYSQL 存儲(chǔ)程序中各種錯(cuò)誤處理機(jī)制。

4. 觸發(fā)器

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

   下表中說(shuō)明了這個(gè)相當(dāng)簡(jiǎn)短的觸發(fā)器：

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

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

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

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

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

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

?

存儲(chǔ)過(guò)程編程4