MySql的存儲過程

存儲過程和函數是在資料庫中定義一些SQL語句的集合，然後直接調用這些存儲過程和函數來執行已經定義好的SQL語句。存儲過程和函數可以避免開發人員重複的編寫相同的SQL語句。而且，存儲過程和函數是在MySQL伺服器中存儲和執行的。可以減少用戶端和伺服器端的資料傳輸。

資料庫系統中，為了保證資料的完整性、一緻性，同時也為提高其應用性能，大多資料庫常采用存儲過程和存儲函數技術。存儲過程和存儲函數經常是一組SQL語句的組合，這些語句被當作整體存入MySQL資料庫伺服器中。使用者定義的存儲函數不能用于修改全局庫狀态，但該函數可從查詢中被喚醒調用，也可以像存儲過程一樣通過語句執行。

CREATE PROCEDURE sp_name ([proc_parameter[，……]]) [characteristic……] routine_body

sp_name參數是存儲過程的名稱；

proc_parameter表示存儲過程的參數清單,proc_parameter中的參數由3部分組成，它們分别是輸入輸出類型、參數名稱和參數類型。其形式為[IN|OUT|INOUT] param_name type。其中IN表示輸入參數；OUT表示輸出參數；INOUT表示既可以輸入也可以輸出；param_name參數是存儲過程參數名稱；type參數指定存儲過程的參數類型，該類型可以為MySQL資料庫的任意資料類型;

characteristic參數指定存儲過程的特性；

routine_body參數是SQL代碼的内容，可以用BEGIN..END來辨別SQL代碼的開始和結束。

CREATE FUNCTION sp_name ([func_parameter[，……]]) RETURNS type [characteristic……] routine_body

func_parameter可以由多個參數組成，其中每個參數均由參數名稱和參數類型組成，其結構如下：

param_name參數是存儲函數的函數名稱；type參數用于指定存儲函數的參數類型。該類型可以是MySQL資料庫所支援的類型。

1.局部變量

局部變量以關鍵字declare聲明，後跟變量名和變量類型，例如：

declare a int

當然在聲明局部變量時也可以用關鍵字default為變量指定預設值，例如：

declare a int default 10

2.全局變量

MySQL中的會話變量不必聲明即可使用，會話變量在整個過程中有效，會話變量名以字元“@”作為起始字元。下述代碼為會話變量的使用方法。

3.為變量指派

MySQL中可以使用DECLARE關鍵字來定義變量。定義變量的基本文法如下：

DECLARE var_name ，…… type [DEFAULT value]

DECLARE是用來聲明變量的；var_name參數是設定變量的名稱。如果使用者需要，也可以同時定義多個變量；type參數用來指定變量的類型；DEFAULT value的作用是指定變量的預設值，不對該參數進行設定時，其預設值為NULL。

MySQL中可以使用SET關鍵字為變量指派。SET語句的基本文法如下：

SET var_name1=expr1，var_name2=expr2……

SET關鍵字是用來為變量指派；var_name參數是變量的名稱；expr參數是指派表達式。一個SET語句可以同時為多個變量指派，各個變量的指派語句之間用“，”隔開。例如：為變量mr_soft指派，代碼如下：

SET mr_soft=10；

另外，MySQL中還可以應用另一種方式為變量指派。其文法結構如下：

SELECT col_name，…… INTO var_name，…… FROM table_name where condition

其中，col_name參數辨別查詢的字段名稱；var_name參數是變量的名稱；table_name參數為指定資料表的名稱；condition參數為指定查詢條件。

例如：從studentinfo表中查詢name為“LeonSK”的記錄。将該記錄下的tel字段内容指派給變量customer_tel。其關鍵代碼如下：

SELECT tel INTO customer_tel FROM studentinfo WHERE name='LeonSK'；

說明：上述指派語句必須存在于建立的存儲過程中，且需将指派語句放置在BEGIN……END之間。若脫離此範圍，該變量将不能使用或被指派。

通過MySQL查詢資料庫，其結果可能為多條記錄。在存儲過程和函數中使用光标可以實作逐條讀取結果集中的記錄。光标使用包括聲明光标（DECLARE CURSOR）、打開光标（OPEN CURSOR）、使用光标（FETCH CURSOR）和關閉光标（CLOSE CURSIR）。值得一提的是，光标必須聲明在處理程式之前，且聲明在變量和條件之後。

1.聲明光标

在MySQL中，聲明光标仍使用DECLARE關鍵字，其文法如下：

DECLARE cursor_name CURSOR FOR select_statement

cursor_name是光标的名稱，光标名稱使用與表名同樣的規則；select_statement是一個SELECT語句，傳回一行或多行資料。其中，這個語句也可以在存儲過程中定義多個光标，但是必須保證每個光标名稱的唯一性，即每一個光标必須有自己唯一的名稱。

通過上述定義來聲明光标info_of_student，代碼如下：

DECLARE info_of_student CURSOR FOR

SELECT

sid, name, age, sex, age

FROM studentinfo

WHERE sid=1；

這裡SELECT子句中不能包含INTO子句，并且光标隻能在存儲過程或存儲函數中使用。

2.打開光标

在聲明光标之後，要從光标中提取資料，必須首先打開光标。在MySQL中，使用OPEN關鍵字來打開光标。其基本的文法如下：

OPEN cursor_name

其中，cursor_name參數表示光标的名稱。在程式中，一個光标可以打開多次。由于可能在使用者打開光标後，其他使用者或程式正在更新資料表。是以可能會導緻使用者在每次打開光标後，顯示的結果都不同。

打開上面已經聲明的光标info_of_student，其代碼如下：

OPEN info_of_student

3.使用光标

光标在順利打開後，可以使用FETCH……INTO語句來讀取資料。其文法如下：

FETCH cursor_name INTO var_name1，var_name2.....

其中，cursor_name代表已經打開光标的名稱；var_name參數表示将光标中的SELECT語句查詢出來的資訊存入該參數中。var_name是存放資料的變量名，必須在聲明光标前定義好。FETCH……INTO語句與SELECT……INTO語句具有相同的意義。

将已打開的光标info_of_student中SELECT語句查詢出來的資訊存入tmp_name和tmp_tel中。其中tmp_name和tmp_tel必須在使用前定義。

代碼如下：

FETCH info_of_student INTO tmp_name, tmp_tel；

4.關閉光标

光标使用完畢後，要及時關閉，在MySQL中采用CLOSE關鍵字關閉光标，其文法格式如下：

CLOSE cursor_name

cursor_name參數表示光标名稱。下面關閉已打開的光标info_of_student。代碼如下：

CLOSE info_of_student

對于已關閉的光标，在其關閉之後則不能使用FETCH來使用光标。光标在使用完畢後一定要關閉。

在MySQL中，常見的過程式SQL語句可以用在一個存儲過程體中。其中包括IF語句、CASE語句、LOOP語句、WHILE語句、ITERATE語句和LEAVE語句，它們可以進行流程控制。

IF語句用來進行條件判斷，根據不同的條件執行不同的操作。該語句在執行時首先判斷IF後的條件是否為真，則執行THEN後的語句，如果為假則繼續判斷IF語句直到為真為止，當以上都不滿足時則執行ELSE語句後的内容。IF語句表示形式如下：

case語句為多分支語句結構，該語句首先從when後的value中查找與case後的value相等的值，如果查找到則執行該分支的内容，否則，執行else後的内容。case語句表示形式如下：

while循環語句執行時首先判斷condition條件是否為真，如果是，則執行循環體，否則退出循環。該語句表示形式：

該循環沒有内置的循環條件，但可以通過leave語句退出循環。loop語句表示形式：

LOOP允許某特定語句或語句群的重複執行，實作一個簡單的循環構造，其中中間省略的部分是需要重複執行的語句。在循環内的語句一直重複直至循環被退出，退出循環應用LEAVE語句。

LEAVE語句經常和BEGIN……END或循環一起使用，其結構如下：

LEAVE label

label是語句中标注的名字，這個名字是自定義的。加上LEAVE關鍵字就可以用來退出被标注的循環語句。

call example_loop（@s）

select @s

該語句先執行一次循環體，之後判斷condition條件是否為真，則退出循環，否則繼續執行循環。repeat語句表示形式：

它可以出現在LOOP、REPEAT和WHILE語句内，其意為“再次循環”。

ITERATE label

該語句的格式與LEAVE大同小異，差別在于：LEAVE語句是離開一個循環，而ITERATE語句是重新開始一個循環。

注意,與一般程式設計流程控制不同的是：存儲過程并不支援FOR循環。

CALL sp_name（[parameter，……]）；

其中，sp_name是存儲過程的名稱；parameter是存儲過程的參數。

在MySQL中，存儲函數的使用方法與MySQL内部函數的使用方法基本相同，使用者自定義的存儲函數與MySQL内部函數性質相同。

SELECT function_name（[parameter，……]）；

建立存儲過程和函數以後，使用者可以檢視存儲過程和函數的狀态和定義。使用者可以通過SHOW STATUS語句檢視存儲過程和函數狀态，也可以通過SHOW CREATE語句來檢視存儲過程和函數的定義。

在MySQL中可以通過SHOW STATUS語句檢視存儲過程和函數的狀态。其基本文法結構如下：

SHOW {PROCEDURE|FUNCTION} STATUS [LIKE 'pattern']

其中，PROCEDURE參數表示查詢存儲過程；FUNCTION參數表示查詢存儲函數；LIKE 'pattern'參數用來比對存儲過程或函數名稱。

MySQL中可以通過SHOW CREATE語句來檢視存儲過程和函數的狀态。其文法結果如下：

SHOW CREATE {PROCEDURE|FUNCTION} sp_name；

其中，PROCEDURE參數表示存儲過程；FUNCTION參數表示查詢存儲函數；sp_name參數表示存儲過程或函數的名稱。

SHOW STATUS語句隻能檢視存儲過程或函數所操作的資料庫對象，如存儲過程或函數的名稱、類型、定義者、修改時間等資訊，并不能查詢存儲過程或函數的具體定義。如果需要檢視詳細定義，需要使用SHOW CREATE語句。

修改存儲過程和存儲函數是指修改已經定義好的存儲過程和函數。在MySQL中可通過ALTER PROCEDURE語句來修改存儲過程，通過ALTER FUNCTION語句來修改存儲函數。

在MySQL中修改存儲過程和函數的語句的文法形式如下：

ALTER FUNCTION name_of_student READS SQL DATA COMMENT'FIND NAME'；

SELECT SPCIFIC_NAME, SQL_DATA_ACCESS, ROUTINE_COMMENT FROM information_schema.Routines WHERE ROUTINE_NAME='name_of_student'；

删除存儲過程和函數指删除資料庫中已經存在的存儲過程或函數。在MySQL中可以使用DROP PROCEDURE語句來删除存儲過程，通過DROP FUNCTION語句來删除存儲函數。在删除之前，必須确認該存儲過程或函數沒有任何依賴關系，否則可能會導緻其他與其關聯的存儲過程無法運作。

删除存儲過程和函數的文法如下：

DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name

其中，sp_name參數表示存儲過程或函數的名稱；IF EXISTS是MySQL的擴充，判斷存儲過程或函數是否存在，以免發生錯誤。

當傳回結果沒有提示警告或報錯時，則說明存儲過程或存儲函數已經被順利删除。

在使用者執行某些程式時，可能會産生一些問題，為了增強程式本身處理問題的能力，避免程式因異常而終止運作。我們往往在處理程式執行前，預測程式在執行過程中可能出現或遇到的問題。定義條件和處理程式來提示使用者的同時，也為使用者提出解決辦法。MySQL通過DECLARE關鍵字來定義條件和處理程式。

在MySQL中，應用DECLARE語句定義條件。其文法格式如下：

DECLARE condition_name CONDITION FOR condition_value

condition_value：

SQLSTATE [VALUE] sqlstate_value  |   mysql_error_code

其中，condition_name參數表示條件名稱；condition_value參數表示類型：sqlstate_value參數可以表示MySQL的錯誤。另外也可以應用mysql_error_code來表示錯誤代碼。其中第一種定義方法格式如下：

DECLARE can_not_find CONDITION FOR SQLSTATE '42S02'；

第二種定義格式如下：

DECLARE can_not_find CONDITION FOR 1146

說明:兩種表示MySQL錯誤的方法名稱都為can_not_find，不同點隻是擷取的錯誤提示碼不同。第一種方法設定sqlstate_value值為42S02，第二種方法設定mysql_error_code值為1146。

在MySQL中可以使用DECLARE關鍵字來定義處理程式。其文法如下：

DECLARE handler_type HANDLER FOR condition_value，…… sp_statement

handler_type：

CONTINUE|EXIT|UNDO

SQLSTATE [VALUE] sqlstate_value   |   condition_name   |  SQLWARNING  |  NOT FOUND  | SQLEXCEPTION | mysql_error_code

這個語句指定每個可以處理一個或多個條件的處理程式。如果産生一個或多個條件，指定的語句被執行。

handler_type參數指明錯誤的處理方式，該參數有3個取值。它們分别是：

對于一個CONTINUE處理程式，目前子程式的執行在執行處理程式語句之後繼續。

對于EXIT處理程式，目前BEGIN……END複合語句的執行被終止。

UNDO表示遇到錯誤後撤回之前的操作，MySQL不支援該處理方式。

condition_value參數指定錯誤類型，該參數有6個取值。

sqlstate_value：表示MySQL的錯誤。

mysql_error_code：表示錯誤代碼。

condition_name：DECLARE定義的條件名稱。

SQL WARNING：表示所有以01開頭的sqlstate_value值。

NOT FOUND：表示所有以02開頭的sqlstate_value值。

SQLEXCEPITION：表示所有沒有被SQL WARNING或NOT FOUND捕獲的sqlstate_value值。