Stored Routines 進階

1 錯誤編號

使用SQL敘述請資料庫執行一些工作的時候，可能會因為輸入錯誤或其它的原因，造成資料庫產生錯誤訊息，下列的SQL敘述在SQL query browser中執行以後，MySQL會傳回一個錯誤編號與錯誤訊息，告訴你查詢的表格名稱不存在：

MySQL用來表示錯誤的編號有兩種，一種是MySQL資料庫伺服器用的錯誤編號，使用四位數的數字來表示各種不同的錯誤；另外一種是各種資料庫軟體都適用的「SQL state」編號，使用五個字元的字串，來表示執行一個敘述以後各種不同的狀況：

註： MySQL的錯誤編號稱為「Server Error Codes」，詳細的錯誤編號與對應的錯誤訊息可以參考MySQL參考手冊的附錄B(MySQL 5.0 Reference Manual、Appendix B. Error Codes and Messages、1584頁)。

2 Handlers

在撰寫stored routines時，MySQL提供一種很特別的宣告語法，你可以使用它宣告「handler」，handler用來處理stored routines中可能會發生的錯誤，讓你可以針對發生的錯誤執行必要的補救工作，也可以防止stored routines因為發生錯誤而中止。首先要特別注意宣告「handler」的位置：

Handler是用來處理錯誤用的，所以在宣告的時候，要設定處理的錯誤種類和決定後續的流程。下列是宣告handler的語法：

Handler的宣告包含發生的錯誤時要執行的敘述，如果有多個敘述時，就一定要使用「BEGIN-END」區塊，把這些敘述放在區塊中：

下列是一個新增部門資料的procedur，呼叫它的時候要提供部門編號、名稱與地點三個參數，這個procedure會使用你的參數幫你新增一筆紀錄到「cmdev.dept」表格中，新增後會顯示「Success!」的訊息：

下列是呼叫「cmdev.test_handler」procedure的範例：

因為在「cmdev.dept」表格的定義中，部門編號「deptno」欄位設定為primary key，所以它的欄位值是不可以重複的。所以如果再執行一次上列呼叫「cmdev.test_handler」procedure的範例：

在執行一個stored routine的過程中，如果發生任何錯誤，MySQL都會停止繼續執行，再傳回錯誤編號與錯誤訊息，告訴呼叫的人發生了什麼狀況：

撰寫stored routines處理資料庫的工作，除了之前已經討論過的許多好處外，使用handler來處理錯誤，讓執行工作的過程可以更加順利，也是使用stored routines的主要原因。

下列的範例同樣是提供新增部門資料功能的procedure，不過為了希望發生索引值重複的錯誤時，不要因為錯誤而中斷執行的工作，也不要傳回錯誤編號與錯誤訊息，而是自己顯示一個錯誤訊息，清楚的告訴使用者發生了什麼狀況。這樣的需求就必須在procedure中加入handler的宣告。索引值重複的SQL state是「23000」，這個編號會使用在handler的宣告中：

加入handler宣告的stored routines，在執行過程中如果沒有發生任何問題，handler是沒有任何作用的，stored routines會正常的執行完所有的敘述：

呼叫加入handler的宣告的「cmdev.test_handler2」，如果沒有發生任何問題，在新增部門紀錄後會顯示「Success!」的訊息：

如果在執行過程中發生任何問題了，MySQL會使用發生的錯誤編號，與你在handler宣告中指定的錯誤執行比對的工作，如果一樣的話，接下來就交由handler來處理這個錯誤，MySQL就不會中斷執行與回傳錯誤：

呼叫加入handler的宣告的「cmdev.test_handler2」時，如果指定的部門編號在資料表中已經存在，執行新增的敘述時就會發生發生索引值重複的錯誤。這種錯誤的SQL state是「23000」，MySQL錯誤編號是「1062」：

在宣告handler時，除了指定handler要處理哪一種錯誤外，還要根據自己的需求，決定處理錯誤以後的後續流程：

一個宣告為「EXIT」的handler，在執行完handler包含的敘述以後，會離開handler所在的區塊；而宣告為「CONTINUE」的handler，執行的流程會像這樣：

上列新增部門資料的procedure範例，根據新增紀錄的結果，會顯示「Success!」或「Error!」兩種結果。如果希望不論新增紀錄成功或發生問題，都要把結果儲存到下列的「cmdev.deptlog」表格中：

下列的範例使用「CONTINUE HANDLER」來執行新增部門紀錄資料，而且會記錄執行後的結果：

呼叫「test_handler3」procedure後，如果沒有發生任何問題，除了新增部門紀錄外，還會新增一筆成功的訊息到「cmdev.deptlop」表格：

如果新增部門紀錄時發生錯誤，「CONTINUE HANDLER」會把「v_message」變數值設定為「Error!」，然後再新增一筆錯誤的訊息到「cmdev.deptlop」表格：

下列的範例是呼叫「test_handler3」procedure後，紀錄在「cmdev.deptlop」表格中的結果：

索引值重複與不允許NULL值的錯誤，都是屬於SQL state中的「23000」，如果你想要分別處理這兩種錯誤的話，你可以針對每一種錯誤，宣告不同的handler來處理，不過在指定錯誤時，就要使用MySQL錯誤編號：

下列的範例是呼叫「test_handler4」procedure後，紀錄在「cmdev.deptlop」表格中的結果：

在宣告handler時指定的錯誤情況有下列幾種：

3 Conditions

如果在stored routines中需要宣告handler來處理錯誤的話，你還可以宣告「conditions」給handler使用，下列是區塊中conditions宣告的位置：

你可以宣告condition用來代表某一種問題，下列是宣告condition的語法：

下列的範例宣告兩個condition，分別代表不允許NULL值與索引值重複的錯誤，宣告好的condition，就可以使用在handler的宣告中：

4 Cursors

如果stored routines需要針對一個查詢結果中的每一筆紀錄執行需要的處理工作，你可以宣告一個「cursor」來代表一個查詢的結果，並且使用cursor依序處理所有紀錄資料。下列是在區塊中宣告cursor的位置：

宣告好cursors以後，可以使用「OPEN」敘述來開啟，接著使用「FETCH」敘述讀取資料，最後要使用「CLOSE」敘述關閉用完的cursor：

宣告cursor時所指定的查詢敘述，與使用「FETCH」讀取資料時，要特別注意相對的順序：

一般來說，都會把cursor稱為「游標」或「指標」。當你宣告好一個需要的cursor以後，接著使用「OPEN」敘述開啟cursor，這時會有一個游標指向查詢結果的第一筆紀錄：

當你使用「FETCH」敘述時，除了讀取目前游標的紀錄資料外，還會將游表指向下一筆紀錄：

以上列宣告的cursor來說，從開啟到讀取所有紀錄資料的游標狀況會像這樣：

在stored routines中使用cursor，通常需要下列的流程：

下列是流程與對應的敘述：

為了讀取cursor中所有的紀錄資料，要另外宣告handler來控制在沒有資料讀取時可以離開迴圈：

除了使用「EXIT HANDLER」外，也可以使用「CONTINUE HANDLER」來控制在沒有資料讀取時可以離開迴圈：

下列的說明表示沒有資料可以讀取時的流程：

在資料庫的應用中，通常是需要針對一個查詢的結果執行比較複雜的工作，才會在sotred routines中宣告與使用cursor。如果你常常需要查詢月薪在某個金額以上的員工資料，而且要把這些員工資料儲存到一個表格中。這樣的需求包含執行查詢與處理新表格的工作，你就可以考慮使用包含cursor的procedure來完成這些工作。

下列的範例可以將月薪在指定金額以上的員工資料儲存到「cmdev.topemp」表格中：

5 設定、修改與刪除Stored routines

5.1 建立Stored routines時的設定

建立stored routines時，也可以加入一些額外的設定：

下列是這些額外設定的說明：

• LANGUAGE {SQL}：設定Stored routine中用來撰寫敘述的語言，目前只有支援SQL，所以只能在LANGUAGE後面指定SQL

• [NOT] DETERMINISTIC：如果傳送相同的參數給Stored routine，每次執行它以後都會產生同樣的結果，這個Stored routine就應該設定為「DETERMINISTIC」；否則就要設定為「NOT DETERMINISTIC」。預設值為「NOT DETERMINISTIC」

• SQL SECURITY { DEFINER | INVOKER }：設定Stored routine要以建立者或執行者的權限執行

• COMMENT ‘說明字串’：設定Stored routine的說明

5.2 修改Stored routines設定

使用「ALTER PROCEDURE」與「ALTER FUNCTION」可以修改它們的額外設定，如果要修改參數或裡面的敘述，必須刪除後再重新建立。下列是修改stored routines設定的語法：

下列的範例執行修改「cmdev.gen_top_emp」的設定：

5.3 刪除Stored routines

如果不再需要一個已經建立的stored routines，你可以使用下列的語法來刪除它們：

6 查詢Stored routines的相關資訊

如果想要查詢stored routines的相關資訊，可以查詢「information_schema.ROUTINES」表格，下列是它的主要欄位：

你也可以使用MySQL提供的「SHOW」指令來查詢stored routines的相關資訊：

如果你想要查詢建立某個stored routines的詳細資訊，可以使用下列的語法：