-
一個快速教程
讓我們來看幾個例子,來演示 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元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
