MySQL 超新手入門(15)Stored Routines 進階 by Michael | CodeData
top

MySQL 超新手入門(15)Stored Routines 進階

分享:

專欄作者新書出版:Android App程式開發剖析 第三版(適用Android 8 Oreo與Android Studio 3)

MySQL 超新手入門(14)Stored Routines 的變數與流程 << 前情

1 錯誤編號

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

mysql_15_snap_01

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

mysql_15_snap_02

註: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」的位置:

mysql_15_snap_03

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

mysql_15_snap_04

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

mysql_15_snap_05

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

mysql_15_snap_06

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

mysql_15_snap_07

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

mysql_15_snap_08

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

mysql_15_snap_09

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

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

mysql_13_snap_66

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

mysql_15_snap_11

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

mysql_15_snap_12

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

mysql_15_snap_13

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

mysql_15_snap_14

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

mysql_15_snap_15

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

mysql_15_snap_16

上列新增部門資料的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」來執行新增部門紀錄資料,而且會記錄執行後的結果:

mysql_15_snap_17

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

mysql_15_snap_18

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

mysql_15_snap_19

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

mysql_15_snap_20

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

mysql_15_snap_21

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

mysql_15_snap_22

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

mysql_15_snap_23

3 Conditions

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

mysql_15_snap_24

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

mysql_15_snap_25

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

mysql_15_snap_26

4 Cursors

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

mysql_15_snap_27

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

mysql_15_snap_28

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

mysql_15_snap_29

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

mysql_15_snap_30

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

mysql_15_snap_46

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

mysql_15_snap_32

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

mysql_15_snap_33

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

mysql_15_snap_34

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

mysql_15_snap_35

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

mysql_15_snap_36

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

mysql_15_snap_37

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

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

mysql_15_snap_38

5 設定、修改與刪除Stored routines

5.1 建立Stored routines時的設定

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

mysql_15_snap_39

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

  • 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設定的語法:

mysql_15_snap_40

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

mysql_15_snap_41

5.3 刪除Stored routines

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

mysql_15_snap_42

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的相關資訊:

mysql_15_snap_43

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

mysql_15_snap_44

後續 >> MySQL 超新手入門(16)Triggers

 

分享:
按讚!加入 CodeData Facebook 粉絲群

相關文章

留言

留言請先。還沒帳號註冊也可以使用FacebookGoogle+登錄留言

熱門論壇文章

熱門技術文章