存儲程序(1)——MYSQL

MySQL支持把幾種對象存放在服務器端供以后使用。這幾種對象有一些可以根據情況通過程序代碼調用，有一些會在數據表被修改時自動執行，還有一些可以在預定時刻自動執行。它們包括以下幾種:

1.存儲函數(stored function)。返回一個計算結果，該結果可以用在表達式里。
2.存儲過程(stored procedure)。不直接返回一個結果，但可以用來完成一般的運算或是生成一個結果集并傳遞回客戶。
3.觸發器(trigger)。與數據表相關聯，當那個數據表被工NSERT、DELETE或UPDATE語句修改時，觸發器將自動執行。
4.事件(event)。根據時間表在預定時刻自動執行。

MySQL對存儲函數和存儲過程的支持始于5.0.0版本，對觸發器和事件的支持分別始于5.0.2版本和5.1.6版本。存儲程序有以下優點和能力：

1.存儲程序對象的可執行部分可以用復合語句來編寫，復合語句對SQL語法進行了擴展，可以包括代碼塊、循環和條件語句。
2.存儲程序都被保存在服務器端，定義它們所需要的代碼只需在它們被創建時通過網絡傳遞一次，而不是每次執行都要傳遞一次。這大大減少了開銷。
3.它們可以把復雜的計算封裝為程序單元，而你可以簡單地通過程序單元的名字來調用它們。你甚至可以把一組存儲程序打包為一個“函數庫”供其他應用程序調用。
4.它們提供了一種錯誤處理機制。
5.它們可以提高數據庫的安全性。你可以通過選擇存儲程序執行時所需的權限下來對敏感數據的訪問情況進行限制和調控。

存儲程序。泛指各種類型的存儲對象(存儲函數、存儲過程、觸發器、事件)。存儲例程(stored routine )，特指存儲函數和存儲過程。這兩種對象的定義語法很相似，所以很自然地把它們放在一起討論。在開始討論各種類型的存儲程序之前，我們首先學習一下：復合語句。

1.復合語句和語句分隔符

簡單的存儲程序只包含一條SQL語句，在編寫時不需要特殊對待。下面的存儲過程使用了一條SELECT語句來列出sampdb數據庫里的數據表的名字:

PROCEDURE sampdb_tables()
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA=’sampdb’ ORDER BY TABLE_NAME;

不過，存儲程序并非只能包含一條簡單的SQL語句。它們可以包含多條SQL語句，可以使用局部變量、條件語句、循環和嵌套語句塊等多種語法構造。要使用這些構造編寫存儲程序，就需要用到復合語句。復合語句由BEGIN開頭，END結束，在它們之間可以寫出任意數量的語句，這些語句構成了一個語句塊。下面的存儲過程將顯示一條歡迎消息，其中有你的用戶名;如果你是一位匿名用戶，用戶名將是“earthing"：

CREATE PROCEDURE greetings()
BEGIN#77=16  for username+60 for hostname+1 for '@'DECLARE user CHAR(77) CHARACTER SET utf8;SET user = (SELECT CURRENT USER());IF INSTR(user,’@’)>0 THEN#返回字符串在某一個字段的內容中的位置, 沒有找到字符串返回0，否則返回位置（從1開始）SET user=SUBSTRING_INDEX(user, '@', 1);END IF;IF user = '' THENSET user='earthling';END IF;SELECT CONCAT('Greetings，',user, '!') AS greeting;
END;

在使用復合語句時，必須考慮和解決這樣一個問題：復合語句塊里的語句必須以分號(;)彼此隔開，但因為分號同時也是mysql程序默認使用的語句分隔符，所以在使用mysql程序定義存儲程序時會發生沖突。
解決這個問題的辦法是使用delimiter命令把mysql程序的語句分隔符重定義為另一個字符或字符串，它必須是在存儲例程的定義里沒有出現過的。這樣一來，mysql程序就不會把分號解釋為語句終止符了，它將把整個對象定義作為一條語句傳遞給服務器。在定義完存儲程序之后，可以把mysql程序的語句終止符重新定義為分號。

use weibo;
delimiter $
CREATE PROCEDURE show_times()
BEGINSELECT 'Local time is:', CURRENT_TIMESTAMP;SELECT 'UTC time is:', UTC_TIMESTAMP;
END$
delimiter ;-- delimiter后空格加分號，將分號設置為分隔符
CALL show_times();

定義一個存儲過程時把mysql程序的默認分隔符臨時改變為$，然后在恢復了mysql程序的默認分隔符之后執行了那個存儲過程:

?

分隔符不必非得是$字符，也不必非得是單個的字符:

delimiter EOF

這里的原則是:只要在某個存儲程序內部的語句里會用到分號，就應該在定義這個存儲程序時臨時改變mysql程序的分隔符。

2.存儲函數和存儲過程

存儲函數將向調用者返回一個計算結果，這個結果可以用在表達式里(就像COS()或HEX()這樣的內建函數那樣)。存儲過程需要使用CALL語句來調用，是一個獨立的操作，不能用在表達式里。使用存儲過程的情況主要有兩種:(1)只需通過運算來實現某種效果或動作而無需返回一個值，(2)運算會返回多個結果集(函數做不到這一點)。這只是些指導性建議，不是硬性規定。
比如說，如果你需要返回兩個或更多的值，就不能使用函數。但你可以使用一個過程，因為過程支持的參數類型允許它們的值在過程執行期間被設置，而調用者可以在過程返回后去訪問那些值。

存儲函數要用CREATE FUNCTION語句來創建，存儲過程要用CREATE PROCEDURE語句來創建。下面的例子將創建一個函數，該函數有一個代表著年份的整數參數。(為了與數據表或數據列的名字有所區別，參數命名時將使用p_前綴)。

delimiter $
CREATE FUNCTION count_born_in_year(p_year INT)
RETURNS INT
READS SQL DATA
BEGINRETURN (SELECT COUNT(*) FROM president WHERE YEAR(birtb) = p_year);
END$
delimiter ;

這個函數有一條用來表明其返回值數據類型的RETURNS子句和一個用來計算那個值的函數體。函數體至少需要包含一條RETURN語句，用來向調用者返回一個值。把計算定義為函數的好處是可以方便地執行它而無須每次都寫出所有的邏輯，你可以像使用內建函數那樣來調用存儲函數:

SELECT count_born_in_year(1990);

你無法讓一個給定的函數返回多個值。你可以編寫任意多個函數，然后在同一條語句里調用它們全體。另一個辦法是使用一個存儲過程并通過它的OUT參數“返回”多個值。存儲過程負責計算那些值并把它們賦值給相應的參數，而那些參數可以在過程返回后由調用者訪問。如果你定義了一個與某個MySQL內建函數同名的存儲函數，在調用它時就必須用數據庫的名字對該函數的名字進行限定以避免歧義。

存儲過程和存儲函數很相似，但它不返回值。因此，它沒有RETURNS子句或任何RETURN語句。下面這個簡單的存儲過程和count_born_in_year()函數很相似，它將顯示一個結果集而不是把計算結果作為其返回值。

delimiter $
CREATE PROCEDURE show_born_in_year(p_year INT)
BEGINSELECT first_name, last_name, birth, deathFROM presidentWHERE YEAR(birth)=P_year;
END$
delimiter ;

與存儲函數不同，存儲過程不能用在表達式里，它們只能通過CALL語句來調用。如下所示:

CALL show_born_in_year(1990);

前面的例子都是選取信息，但存儲例程還可以用來修改數據表，如下例所示:

delimiter $
CREATE PROCEDURE update_expiration (p_id INT UNSIGNED, p_date DATE)
BEGINUPDATE member SET expiration=p_date WHERE member_id=p_id;
END$
delimiter ;

存儲函數必須遵守這樣一條限制:不允許對調用本函數的語句正在讀或寫的數據表進行修改。存儲過程通常沒有這個限制，但如果它們是從存儲函數里被調用，就需要遵守這條限制。

3.存儲函數和存儲過程的權限

存儲函數和存儲過程屬于數據庫。要想創建存儲函數或存儲過程，必須擁有那個數據庫的CREATE ROUTINE權限。在默認的情況下，當你創建一個存儲例程時，服務器將自動地把EXECUTE和ALTER ROUTINE權限授予你(如果你還沒有獲得這些權限)，這樣你才可以執行那個例程或刪除它。當你刪除那個例程時，服務器將自動撤銷那些權限。如果你不想使用這種自動化的權限授予/撤銷機制，把automatic_sp_privileges系統變量設置為0即可。

如果服務器啟用了二進制日志功能，存儲函數還需要遵守一些額外的限制條件(不允許創建不確定或是會修改數據的存儲函數)以保證二進制日志能夠安全地完成備份和復制操作。這些限制條件如下：
1.如果log_bin_trust_function_creators系統變量沒有被激活，你就必須具備SUPER權限才能創建存儲函數。在此前提下，你創建的每一個函數都必須是確定的，并且不得修改數據。為了表明這一點，需要使用DETERMINISTIC、NO SQL或READS SQL DATA之一來定義存儲函數。
2.如果log bin_trust_function_creators系統變量已被激活，則沒有任何限制。只有當你可以相信MySQL服務器上的所有用戶都不會去定義不安全的存儲函數時，這種設置才是最適當的。

與log_bin_trust_function_creators系統變量有關的限制條件同樣適用于觸發器的創建工作。

4.存儲過程的參數類型

存儲過程的參數分為3種類型。對于IN參數，調用者把一個值傳遞給過程，過程可以對這個值進行修改，但任何修改在過程返回后對調用者是不可見的。OUT參數剛好相反，過程把一個值賦值給OUT參數，這個值在過程返回后可以由調用者訪問。INOUT參數允許調用者向過程傳遞一個值，然后再取回一個值。
要想明確地為參數指定類型，在參數表里把IN, OUT或INOUT寫在參數名字前面即可。如果沒有為參數指定類型，其默認類型將是IN。

在使用OUT或INOUT參數時，在調用過程時需要給出一個變量名。過程可以設置參數的值，相應的變量將在過程返回時獲得那個值。如果想讓某個存儲過程返回多個結果值，OUT和INOUT參數類型將非常有用(存儲函數只能返回一個值，不能勝任)。下面的過程演示了OUT參數的用法。它將分別統計出student數據表里的男生和女生人數并通過它的參數返回這兩個計數值，讓調用者可以訪問它們:

delimiter $
CREATE PROCEDURE count_students_by_sex(OUT p_male INT, OUT p_female INT)
BEGINSELECT COUNT(*) FROM student WHERE sex= 'F' INTO p_female;SELECT COUNT(*) FROM student WHERE sex= 'F' INTO p_female;
END$
delimiter ;

在調用這個過程時，請把各個參數替換為相應的用戶定義變量。這個過程將把計數值放到這些參數里，在它返回之后，那些變量將包含計數值:

CALL count_students_by_sex(@mcoant, @fcount);
SELECT 'Number of male students:'，@mcount;

IN、OUT和INOUT關鍵字不適用于存儲函數、觸發器或事件。對于存儲函數，所有的參數都像IN參數。觸發器和事件則根本沒有任何參數。下一部分介紹：觸發器和事件。