**目錄**
[TOC]
## 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」表格中:
| 欄位名稱 | 型態 | NULL | 索引 | 預設值 | 其它資訊 | 說明 |
| --- | --- | --- | --- | --- | --- | --- |
| logno | bigint(20) | NO | PRI | NULL | auto_increment | 紀錄編號 |
| logdt | timestamp | NO | CURRENT_TIMESTAMP | 日期時間 |
| message | varchar(64) | YES | NULL | 訊息 |
下列的范例使用「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」表格,下列是它的主要欄位:
| 欄位名稱 | 型態 | 說明 |
| --- | --- | --- |
| ROUTINE_SCHEMA | varchar(64) | 資料庫 |
| ROUTINE_NAME | varchar(64) | 名稱 |
| ROUTINE_TYPE | varchar(9) | procedure或function |
| DTD_IDENTIFIER | varchar(64) | procedure固定為「NULL」;function為回傳值型態 |
| ROUTINE_DEFINITION | longtext | Stored routine的內容 |
| IS_DETERMINISTIC | varchar(3) | DETERMINISTIC的設定 |
| SECURITY_TYPE | varchar(7) | DEFINER或INVOKER |
| CREATED | datetime | 建立的日期時間 |
| LAST_ALTERED | datetime | 最后修改的日期時間 |
| ROUTINE_COMMENT | varchar(64) | 說明 |
| DEFINER | varchar(77) | 建立Stored routine的資料庫使用者 |
你也可以使用MySQL提供的「SHOW」指令來查詢stored routines的相關資訊:

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