**目錄**
[TOC]
## 1 錯(cuò)誤編號(hào)
使用SQL敘述請(qǐng)資料庫(kù)執(zhí)行一些工作的時(shí)候，可能會(huì)因?yàn)檩斎脲e(cuò)誤或其它的原因，造成資料庫(kù)產(chǎn)生錯(cuò)誤訊息，下列的SQL敘述在SQL query browser中執(zhí)行以后，MySQL會(huì)傳回一個(gè)錯(cuò)誤編號(hào)與錯(cuò)誤訊息，告訴你查詢的表格名稱不存在：
MySQL用來(lái)表示錯(cuò)誤的編號(hào)有兩種，一種是MySQL資料庫(kù)伺服器用的錯(cuò)誤編號(hào)，使用四位數(shù)的數(shù)字來(lái)表示各種不同的錯(cuò)誤；另外一種是各種資料庫(kù)軟體都適用的「SQL state」編號(hào)，使用五個(gè)字元的字串，來(lái)表示執(zhí)行一個(gè)敘述以后各種不同的狀況：
注：MySQL的錯(cuò)誤編號(hào)稱為「Server Error Codes」，詳細(xì)的錯(cuò)誤編號(hào)與對(duì)應(yīng)的錯(cuò)誤訊息可以參考MySQL參考手冊(cè)的附錄B(MySQL 5.0 Reference Manual、Appendix B. Error Codes and Messages、1584頁(yè))。
## 2 Handlers
在撰寫(xiě)stored routines時(shí)，MySQL提供一種很特別的宣告語(yǔ)法，你可以使用它宣告「handler」，handler用來(lái)處理stored routines中可能會(huì)發(fā)生的錯(cuò)誤，讓你可以針對(duì)發(fā)生的錯(cuò)誤執(zhí)行必要的補(bǔ)救工作，也可以防止stored routines因?yàn)榘l(fā)生錯(cuò)誤而中止。首先要特別注意宣告「handler」的位置：
Handler是用來(lái)處理錯(cuò)誤用的，所以在宣告的時(shí)候，要設(shè)定處理的錯(cuò)誤種類(lèi)和決定后續(xù)的流程。下列是宣告handler的語(yǔ)法：
Handler的宣告包含發(fā)生的錯(cuò)誤時(shí)要執(zhí)行的敘述，如果有多個(gè)敘述時(shí)，就一定要使用「BEGIN-END」區(qū)塊，把這些敘述放在區(qū)塊中：
下列是一個(gè)新增部門(mén)資料的procedur，呼叫它的時(shí)候要提供部門(mén)編號(hào)、名稱與地點(diǎn)三個(gè)參數(shù)，這個(gè)procedure會(huì)使用你的參數(shù)幫你新增一筆紀(jì)錄到「cmdev.dept」表格中，新增后會(huì)顯示「Success!」的訊息：
下列是呼叫「cmdev.test_handler」procedure的范例：
因?yàn)樵凇竎mdev.dept」表格的定義中，部門(mén)編號(hào)「deptno」欄位設(shè)定為primary key，所以它的欄位值是不可以重復(fù)的。所以如果再執(zhí)行一次上列呼叫「cmdev.test_handler」procedure的范例：
在執(zhí)行一個(gè)stored routine的過(guò)程中，如果發(fā)生任何錯(cuò)誤，MySQL都會(huì)停止繼續(xù)執(zhí)行，再傳回錯(cuò)誤編號(hào)與錯(cuò)誤訊息，告訴呼叫的人發(fā)生了什么狀況：
撰寫(xiě)stored routines處理資料庫(kù)的工作，除了之前已經(jīng)討論過(guò)的許多好處外，使用handler來(lái)處理錯(cuò)誤，讓執(zhí)行工作的過(guò)程可以更加順利，也是使用stored routines的主要原因。
下列的范例同樣是提供新增部門(mén)資料功能的procedure，不過(guò)為了希望發(fā)生索引值重復(fù)的錯(cuò)誤時(shí)，不要因?yàn)殄e(cuò)誤而中斷執(zhí)行的工作，也不要傳回錯(cuò)誤編號(hào)與錯(cuò)誤訊息，而是自己顯示一個(gè)錯(cuò)誤訊息，清楚的告訴使用者發(fā)生了什么狀況。這樣的需求就必須在procedure中加入handler的宣告。索引值重復(fù)的SQL state是「23000」，這個(gè)編號(hào)會(huì)使用在handler的宣告中：
加入handler宣告的stored routines，在執(zhí)行過(guò)程中如果沒(méi)有發(fā)生任何問(wèn)題，handler是沒(méi)有任何作用的，stored routines會(huì)正常的執(zhí)行完所有的敘述：
呼叫加入handler的宣告的「cmdev.test_handler2」，如果沒(méi)有發(fā)生任何問(wèn)題，在新增部門(mén)紀(jì)錄后會(huì)顯示「Success!」的訊息：
如果在執(zhí)行過(guò)程中發(fā)生任何問(wèn)題了，MySQL會(huì)使用發(fā)生的錯(cuò)誤編號(hào)，與你在handler宣告中指定的錯(cuò)誤執(zhí)行比對(duì)的工作，如果一樣的話，接下來(lái)就交由handler來(lái)處理這個(gè)錯(cuò)誤，MySQL就不會(huì)中斷執(zhí)行與回傳錯(cuò)誤：
呼叫加入handler的宣告的「cmdev.test_handler2」時(shí)，如果指定的部門(mén)編號(hào)在資料表中已經(jīng)存在，執(zhí)行新增的敘述時(shí)就會(huì)發(fā)生發(fā)生索引值重復(fù)的錯(cuò)誤。這種錯(cuò)誤的SQL state是「23000」，MySQL錯(cuò)誤編號(hào)是「1062」：
在宣告handler時(shí)，除了指定handler要處理哪一種錯(cuò)誤外，還要根據(jù)自己的需求，決定處理錯(cuò)誤以后的后續(xù)流程：
一個(gè)宣告為「EXIT」的handler，在執(zhí)行完handler包含的敘述以后，會(huì)離開(kāi)handler所在的區(qū)塊；而宣告為「CONTINUE」的handler，執(zhí)行的流程會(huì)像這樣：
上列新增部門(mén)資料的procedure范例，根據(jù)新增紀(jì)錄的結(jié)果，會(huì)顯示「Success!」或「Error!」兩種結(jié)果。如果希望不論新增紀(jì)錄成功或發(fā)生問(wèn)題，都要把結(jié)果儲(chǔ)存到下列的「cmdev.deptlog」表格中：
| 欄位名稱 | 型態(tài) | NULL | 索引 | 預(yù)設(shè)值 | 其它資訊 | 說(shuō)明 |
| --- | --- | --- | --- | --- | --- | --- |
| logno | bigint(20) | NO | PRI | NULL | auto_increment | 紀(jì)錄編號(hào) |
| logdt | timestamp | NO | CURRENT_TIMESTAMP | 日期時(shí)間 |
| message | varchar(64) | YES | NULL | 訊息 |
下列的范例使用「CONTINUE HANDLER」來(lái)執(zhí)行新增部門(mén)紀(jì)錄資料，而且會(huì)記錄執(zhí)行后的結(jié)果：
呼叫「test_handler3」procedure后，??如果沒(méi)有發(fā)生任何問(wèn)題，除了新增部門(mén)紀(jì)錄外，還會(huì)新增一筆成功的訊息到「cmdev.deptlop」表格：
如果新增部門(mén)紀(jì)錄時(shí)發(fā)生錯(cuò)誤，「CONTINUE HANDLER」會(huì)把「v_message」變數(shù)值設(shè)定為「Error!」，然后再新增一筆錯(cuò)誤的訊息到「cmdev.deptlop」表格：
下列的范例是呼叫「test_handler3」procedure后，??紀(jì)錄在「cmdev.deptlop」表格中的結(jié)果：
索引值重復(fù)與不允許NULL值的錯(cuò)誤，都是屬于SQL state中的「23000」，如果你想要分別處理這兩種錯(cuò)誤的話，你可以針對(duì)每一種錯(cuò)誤，宣告不同的handler來(lái)處理，不過(guò)在指定錯(cuò)誤時(shí)，就要使用MySQL錯(cuò)誤編號(hào)：
下列的范例是呼叫「test_handler4」procedure后，??紀(jì)錄在「cmdev.deptlop」表格中的結(jié)果：
在宣告handler時(shí)指定的錯(cuò)誤情況有下列幾種：
## 3 Conditions
如果在stored routines中需要宣告handler來(lái)處理錯(cuò)誤的話，你還可以宣告「conditions」給handler使用，下列是區(qū)塊中conditions宣告的位置：
你可以宣告condition用來(lái)代表某一種問(wèn)題，下列是宣告condition的語(yǔ)法：
下列的范例宣告兩個(gè)condition，分別代表不允許NULL值與索引值重復(fù)的錯(cuò)誤，宣告好的condition，就可以使用在handler的宣告中：
## 4 Cursors
如果stored routines需要針對(duì)一個(gè)查詢結(jié)果中的每一筆紀(jì)錄執(zhí)行需要的處理工作，你可以宣告一個(gè)「cursor」來(lái)代表一個(gè)查詢的結(jié)果，并且使用cursor依序處理所有紀(jì)錄資料。下列是在區(qū)塊中宣告cursor的位置：
宣告好cursors以后，可以使用「OPEN」敘述來(lái)開(kāi)啟，接著使用「FETCH」敘述讀取資料，最后要使用「CLOSE」敘述關(guān)閉用完的cursor：
宣告cursor時(shí)所指定的查詢敘述，與使用「FETCH」讀取資料時(shí)，要特別注意相對(duì)的順序：
一般來(lái)說(shuō)，都會(huì)把cursor稱為「游標(biāo)」或「指標(biāo)」。當(dāng)你宣告好一個(gè)需要的cursor以后，接著使用「OPEN」敘述開(kāi)啟cursor，這時(shí)會(huì)有一個(gè)游標(biāo)指向查詢結(jié)果的第一筆紀(jì)錄：
當(dāng)你使用「FETCH」敘述時(shí)，除了讀取目前游標(biāo)的紀(jì)錄資料外，還會(huì)將游表指向下一筆紀(jì)錄：
以上列宣告的cursor來(lái)說(shuō)，從開(kāi)啟到讀取所有紀(jì)錄資料的游標(biāo)狀況會(huì)像這樣：
在stored routines中使用cursor，通常需要下列的流程：
下列是流程與對(duì)應(yīng)的敘述：
為了讀取cursor中所有的紀(jì)錄資料，要另外宣告handler來(lái)控制在沒(méi)有資料讀取時(shí)可以離開(kāi)回圈：
除了使用「EXIT HANDLER」外，也可以使用「CONTINUE HANDLER」來(lái)控制在沒(méi)有資料讀取時(shí)可以離開(kāi)回圈：
下列的說(shuō)明表示沒(méi)有資料可以讀取時(shí)的流程：
在資料庫(kù)的應(yīng)用中，通常是需要針對(duì)一個(gè)查詢的結(jié)果執(zhí)行比較復(fù)雜的工作，才會(huì)在sotred routines中宣告與使用cursor。如果你常常需要查詢?cè)滦皆谀硞€(gè)金額以上的員工資料，而且要把這些員工資料儲(chǔ)存到一個(gè)表格中。這樣的需求包含執(zhí)行查詢與處理新表格的工作，你就可以考慮使用包含cursor的procedure來(lái)完成這些工作。
下列的范例可以將月薪在指定金額以上的員工資料儲(chǔ)存到「cmdev.topemp」表格中：
## 5 設(shè)定、修改與刪除Stored routines
### 5.1 建立Stored routines時(shí)的設(shè)定
建立stored routines時(shí)，也可以加入一些額外的設(shè)定：
下列是這些額外設(shè)定的說(shuō)明：
* LANGUAGE {SQL}：設(shè)定Stored routine中用來(lái)撰寫(xiě)敘述的語(yǔ)言，目前只有支援SQL，所以只能在LANGUAGE后面指定SQL
* [NOT] DETERMINISTIC：如果傳送相同的參數(shù)給Stored routine，每次執(zhí)行它以后都會(huì)產(chǎn)生同樣的結(jié)果，這個(gè)Stored routine就應(yīng)該設(shè)定為「DETERMINISTIC」；否則就要設(shè)定為「NOT DETERMINISTIC」。預(yù)設(shè)值為「NOT DETERMINISTIC」
* SQL SECURITY { DEFINER | INVOKER }：設(shè)定Stored routine要以建立者或執(zhí)行者的權(quán)限執(zhí)行
* COMMENT '說(shuō)明字串'：設(shè)定Stored routine的說(shuō)明
### 5.2 修改Stored routines設(shè)定
使用「ALTER PROCEDURE」與「ALTER FUNCTION」可以修改它們的額外設(shè)定，如果要修改參數(shù)或里面的敘述，必須刪除后再重新建立。下列是修改stored routines設(shè)定的語(yǔ)法：
下列的范例執(zhí)行修改「cmdev.gen_top_emp」的設(shè)定：
### 5.3 刪除Stored routines
如果不再需要一個(gè)已經(jīng)建立的stored routines，你可以使用下列的語(yǔ)法來(lái)刪除它們：
## 6 查詢Stored routines的相關(guān)資訊
如果想要查詢stored routines的相關(guān)資訊，可以查詢「information_schema.ROUTINES」表格，下列是它的主要欄位：
| 欄位名稱 | 型態(tài) | 說(shuō)明 |
| --- | --- | --- |
| ROUTINE_SCHEMA | varchar(64) | 資料庫(kù) |
| ROUTINE_NAME | varchar(64) | 名稱 |
| ROUTINE_TYPE | varchar(9) | procedure或function |
| DTD_IDENTIFIER | varchar(64) | procedure固定為「NULL」；function為回傳值型態(tài) |
| ROUTINE_DEFINITION | longtext | Stored routine的內(nèi)容 |
| IS_DETERMINISTIC | varchar(3) | DETERMINISTIC的設(shè)定 |
| SECURITY_TYPE | varchar(7) | DEFINER或INVOKER |
| CREATED | datetime | 建立的日期時(shí)間 |
| LAST_ALTERED | datetime | 最后修改的日期時(shí)間 |
| ROUTINE_COMMENT | varchar(64) | 說(shuō)明 |
| DEFINER | varchar(77) | 建立Stored routine的資料庫(kù)使用者 |
你也可以使用MySQL提供的「SHOW」指令來(lái)查詢stored routines的相關(guān)資訊：
如果你想要查詢建立某個(gè)stored routines的詳細(xì)資訊，可以使用下列的語(yǔ)法：
- （1） 重新開(kāi)始
- （2） 數(shù)據(jù)庫(kù)概論和 MySQL 安裝
- （3） SELECT 基礎(chǔ)查詢
- （4） 運(yùn)算式和函數(shù)
- （5） JOIN 和 UNION 查詢
- （6） CRUD 和資料維護(hù)
- （7） 字符集和數(shù)據(jù)庫(kù)
- （8） 存儲(chǔ)引擎和數(shù)據(jù)類(lèi)型
- （9） 表格和索引
- （10） 子查詢
- （11） 視圖
- （12） 預(yù)處理語(yǔ)句
- （13） 存儲(chǔ)過(guò)程入門(mén)
- （14） 存儲(chǔ)過(guò)程的變量和流程
- （15） 存儲(chǔ)過(guò)程進(jìn)階
- （16） 觸發(fā)器
- （17） 資料庫(kù)資訊
- （18） 錯(cuò)誤處理和查詢
- （19） 導(dǎo)入和導(dǎo)出數(shù)據(jù)
- （20） 性能