MySQL心得7-1-存儲過程
1. 使用存儲過程的優點有：
（1）存儲過程在服務器端運行，執行速度快。
（2）存儲過程執行一次後，其執行規劃就駐留在高速緩沖存儲器，在以後的操作中，只需從高速緩沖存儲器中調用已編譯好的二進制代碼執行，提高了系統性能。
（3）確保數據庫的安全。使用存儲過程可以完成所有數據庫操作，並可通過編程方式控制上述操作對數據庫信息訪問的權限。 www.2cto.com
2.創建存儲過程可以使用create procedure語句。
要在MySQL 5.1中創建存儲過程，必須具有CREATE routine權限。要想查看數據庫中有哪些存儲過程，可以使用SHOW PROCEDURE STATUS命令。要查看某個存儲過程的具體信息，可使用SHOWCREATE PROCEDURE sp_name命令，其中sp_name是存儲過程的名稱。
CREATE PROCEDURE的語法格式：
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
其中，proc_parameter的參數如下：
[ IN | OUT | INOUT ] param_name type
characteristic特征如下：
language SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
說明：
● sp_name：存儲過程的名稱，默認在當前數據庫中創建。需要在特定數據庫中創建存儲過程時，則要在名稱前面加上數據庫的名稱，格式為：db_name.sp_name。值得注意的是，這個名稱應當盡量避免取與MySQL的內置函數相同的名稱，否則會發生錯誤。
● proc_parameter：存儲過程的參數，param_name為參數名，type為參數的類型，當有多個參數的時候中間用逗號隔開。存儲過程可以有0個、1個或多個參數。MySQL存儲過程支持三種類型的參數：輸入參數、輸出參數和輸入/輸出參數，關鍵字分別是IN、OUT和INOUT。輸入參數使數據可以傳遞給一個存儲過程。當需要返回一個答案或結果的時候，存儲過程使用輸出參數。輸入/輸出參數既可以充當輸入參數也可以充當輸出參數。存儲過程也可以不加參數，但是名稱後面的括號是不可省略的。
注意：參數的名字不要等於列的名字，否則雖然不會返回出錯消息，但是存儲過程中的SQL語句會將參數名看做列名，從而引發不可預知的結果。
characteristic：存儲過程的某些特征設定，下面一一介紹:
language sql：表明編寫這個存儲過程的語言為SQL語言，目前來講，MySQL存儲過程還不能用外部編程語言來編寫，也就是說，這個選項可以不指定。將來將會對其擴展，最有可能第一個被支持的語言是PHP。 www.2cto.com
deterministic：設置為DETERMINISTIC表示存儲過程對同樣的輸入參數產生相同的結果，設置為NOT DETERMINISTIC則表示會產生不確定的結果。默認為NOTDETERMINISTIC。
contains SQL：表示存儲過程不包含讀或寫數據的語句。NO SQL表示存儲過程不包含SQL語句。reads SQL DATA表示存儲過程包含讀數據的語句，但不包含寫數據的語句。modifies SQL DATA表示存儲過程包含寫數據的語句。如果這些特征沒有明確給定，默認的是CONTAINS SQL。
SQL SECURITY：SQL SECURITY特征可以用來指定存儲過程使用創建該存儲過程的用戶（DEFINER）的許可來執行，還是使用調用者（INVOKER）的許可來執行。默認值是DEFINER。
COMMENT 'string'：對存儲過程的描述，string為描述內容。這個信息可以用SHOWCREATE PROCEDURE語句來顯示。
● routine_body：這是存儲過程的主體部分，也叫做存儲過程體。裡面包含了在過程調用的時候必須執行的語句，這個部分總是以begin開始，以end結束。當然，當存儲過程體中只有一個SQL語句時可以省略BEGIN-END標志。
3. 在開始創建存儲過程之前，先介紹一個很實用的命令，即delimiter命令。在MySQL中，服務器處理語句的時候是以分號為結束標志的。但是在創建存儲過程的時候，存儲過程體中可能包含多個SQL語句，每個SQL語句都是以分號為結尾的，這時服務器處理程序的時候遇到第一個分號就會認為程序結束，這肯定是不行的。所以這裡使用DELIMITER命令將MySQL語句的結束標志修改為其他符號。
DELIMITER語法格式為：DELIMITER $$
說明：$$是用戶定義的結束符，通常這個符號可以是一些特殊的符號，如兩個“#”，一個“￥”、數字、字母等都可以。當使用DELIMITER命令時，應該避免使用反斜槓（“\”）字符，因為那是MySQL的轉義字符。
例：創建存儲過程，實現的功能是刪除一個特定學生的信息。
DELIMITER $$
CREATE PROCEDURE DELETE_STUDENT(IN XH CHAR(6))
BEGIN
DELETE FROM XS WHERE 學號=XH;
END $$
DELIMITER ;
說明：當調用這個存儲過程時，MySQL根據提供的參數XH的值，刪除對應在XS表中的數據。
在關鍵字BEGIN和END之間指定了存儲過程體，當然，BEGIN-END復合語句還可以嵌套使用。
4. 局部變量
在存儲過程中可以聲明局部變量，它們可以用來存儲臨時結果。要聲明局部變量必須使用declare語句。在聲明局部變量的同時也可以對其賦一個初始值。
DECLARE語法格式：DECLARE var_name[,...] type [DEFAULT value]
說明：var_name為變量名；type為變量類型；default子句給變量指定一個默認值，如果不指定默認為NULL的話。可以同時聲明多個類型相同的局部變量，中間用逗號隔開。
例： 聲明一個整型變量和兩個字符變量。
DECLARE num INT(4);
DECLARE str1, str2 VARCHAR(6);
declare n char(10) default ‘abcdefg’;
說明：局部變量只能在BEGIN…END語句塊中聲明。
局部變量必須在存儲過程的開頭就聲明，聲明完後，可以在聲明它的BEGIN…END語句塊中使用該變量，其他語句塊中不可以使用它。 www.2cto.com
在存儲過程中也可以聲明用戶變量，不過千萬不要將這兩個混淆。局部變量和用戶變量的區別在於：局部變量前面沒有使用@符號，局部變量在其所在的BEGIN…END語句塊處理完後就消失了，而用戶變量存在於整個會話當中。
5. 使用SET語句賦值
要給局部變量賦值可以使用SET語句，SET語句也是SQL本身的一部分。語法格式為：SET var_name = expr [,var_name = expr] ...
例: 在存儲過程中給局部變量賦值。
SET num=1, str1= 'hello';
說明：與聲明用戶變量時不同，這裡的變量名前面沒有@符號。注意，例中的這條語句無法單獨執行，只能在存儲過程和存儲函數中使用。
6. SELECT...INTO語句（重點）
使用這個SELECT…INTO語法可以把選定的列值直接存儲到變量中。因此，返回的結果只能有一行。語法格式為：
SELECT col_name[,...] INTO var_name[,...] table_expr
說明：col_name是列名，var_name是要賦值的變量名。table_expr是SELECT語句中的FROM子句及後面的部分，這裡不再敘述。
例: 在存儲過程體中將XS表中的學號為081101的學生姓名和專業名的值分別賦給變量name和project。
SELECT 姓名,專業名 INTO name, project
FROMXS; WHERE 學號= '081101';
7. 流程控制語句
在MySQL中，常見的過程式SQL語句可以用在一個存儲過程體中。例如：IF語句、CASE語句、LOOP語句、WHILE語句、iterate語句和LEAVE語句。
（1）IF語句
IF-THEN-ELSE語句可根據不同的條件執行不同的操作。
語法格式為：
IF 判斷的條件THEN 一個或多個SQL語句
[ELSEIF判斷的條件THEN一個或多個SQL語句] ...
[ELSE一個或多個SQL語句]
END IF
說明：當判斷條件為真時，就執行相應的SQL語句。
IF語句不同於系統的內置函數IF()函數，IF()函數只能判斷兩種情況，所以請不要混淆。
例： 創建XSCJ數據庫的存儲過程，判斷兩個輸入的參數哪一個更大。
DELIMITER $$ www.2cto.com
CREATE PROCEDURE XSCJ.COMPAR
(IN K1INTEGER, IN K2 INTEGER, OUT K3 CHAR(6) )
BEGIN
IFK1>K2 THEN
SET K3= '大於';
ELSEIFK1=K2 THEN
SET K3= '等於';
ELSE
SET K3= '小於';
ENDIF;
END$$
DELIMITER ;
說明：存儲過程中K1和K2是輸入參數，K3是輸出參數。
（2）CASE語句
前面已經介紹過了，這裡介紹CASE語句在存儲過程中的用法，與之前略有不同。語法格式為：
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
或者：
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list] www.2cto.com
END CASE
說明：一個CASE語句經常可以充當一個IF-THEN-ELSE語句。
第一種格式中case_value是要被判斷的值或表達式，接下來是一系列的WHEN-THEN塊，每一塊的when_value參數指定要與case_value比較的值，如果為真，就執行statement_list中的SQL語句。如果前面的每一個塊都不匹配就會執行ELSE塊指定的語句。CASE語句最後以END CASE結束。
第二種格式中CASE關鍵字後面沒有參數，在WHEN-THEN塊中，search_condition指定了一個比較表達式，表達式為真時執行THEN後面的語句。與第一種格式相比，這種格式能夠實現更為復雜的條件判斷，使用起來更方便。
例： 創建一個存儲過程，針對參數的不同，返回不同的結果。
DELIMITER $$
CREATE PROCEDURE XSCJ.RESULT
(IN str VARCHAR(4), OUT sex VARCHAR(4) )
BEGIN
CASE str
WHEN'M' THEN SET sex='男';
WHEN'F' THEN SET sex='女';
ELSE SET sex='無';
ENDCASE;
END$$
DELIMITER ;
例： 用第二種格式的CASE語句創建以上存儲過程。程序片段如下：
CASE
WHENstr='M' THEN SET sex='男';
WHENstr='F' THEN SET sex='女';
ELSE SET sex='無';
END CASE;
（3）循環語句
MySQL支持3條用來創建循環的語句：while、repeat和loop語句。在存儲過程中可以定義0個、1個或多個循環語句。
● WHILE語句語法格式為：
[begin_label:] WHILE search_condition DO
statement_list www.2cto.com
END WHILE [end_label]
說明：語句首先判斷search_condition是否為真，不為真則執行statement_list中的語句，然後再次進行判斷，為真則繼續循環，不為真則結束循環。begin_label和end_label是WHILE語句的標注。除非begin_label存在，否則end_label不能被給出，並且如果兩者都出現，它們的名字必須是相同的。
例: 創建一個帶WHILE循環的存儲過程。
DELIMITER $$
CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT5;
WHILE v1 > 0 DO
SET v1 = v1-1;
END WHILE;
END $$
DELIMITER ;
● repeat語句格式如下：
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
說明：REPEAT語句首先執行statement_list中的語句，然後判斷search_condition是否為真，為真則停止循環，不為真則繼續循環。REPEAT也可以被標注。
例: 用REPEAT語句創建一個如例7.9的存儲過程。程序片段如下：
REPEAT
v1=v1-1;
UNTIL v1<1;
END REPEAT;
說明：REPEAT語句和WHILE語句的區別在於：REPEAT語句先執行語句，後進行判斷；而WHILE語句是先判斷，條件為真時才執行語句。
● LOOP語句語法格式如下：
[begin_label:] LOOP
www.2cto.com
statement_list
END LOOP [end_label]
說明：LOOP允許某特定語句或語句群的重復執行，實現一個簡單的循環構造，statement_list是需要重復執行的語句。在循環內的語句一直重復至循環被退出，退出時通常伴隨著一個LEAVE 語句。
LEAVE語句經常和BEGIN...END或循環一起使用。結構如下：
LEAVE label ; label是語句中標注的名字，這個名字是自定義的。加上LEAVE關鍵字就可以用來退出被標注的循環語句。
例: 創建一個帶LOOP語句的存儲過程。
DELIMITER $$
CREATE PROCEDURE doloop()
BEGIN
SET @a=10;
Label: LOOP
SET @a=@a-1;
IF @a<0 THEN
LEAVELabel;
END IF;
END LOOPLabel;
END$$
DELIMITER ;
循環語句中還有一個iterate語句，它只可以出現在LOOP、REPEAT和WHILE語句內，意為“再次循環”。它的格式為：ITERATE label
說明：該語句格式與LEAVE差不多，區別在於：LEAVE語句是離開一個循環，而ITERATE語句是重新開始一個循環。
8.我們調用此存儲過程來查看最後結果。調用該存儲過程使用如下命令：CALL doloop();
接著，查看用戶變量的值： SELECT@a;
語法格式：CALL sp_name([parameter[,...]])
說明：sp_name為存儲過程的名稱，如果要調用某個特定數據庫的存儲過程，則需要在前面加上該數據庫的名稱。parameter為調用該存儲過程使用的參數，這條語句中的參數個數必須總是等於存儲過程的參數個數。 www.2cto.com
例：創建一個存儲過程，有兩個輸入參數：XH和KCM，要求當某學生某門課程的成績小於60分時將其學分修改為零，大於等於60分時將學分修改為此課程的學分。
DELIMITER $$
CREATE PROCEDURE XSCJ.DO_UPDATE(IN XHCHAR(6), IN KCM CHAR(16))
BEGIN
DECLARE KCH CHAR(3);
DECLARE XF TINYINT;
DECLARE CJ TINYINT;
SELECT課程號, 學分 INTO KCH, XFFROM KC WHERE 課程名=KCM;
SELECT成績 INTO CJ FROM XS_KC WHERE 學號=XH AND 課程號=KCH;
IF CJ<60 THEN
UPDATE XS_KC SET 學分=0 WHERE 學號=XH AND 課程號=KCH;
ELSE
UPDATE XS_KC SET 學分=XF WHERE 學號=XH AND 課程號=KCH;
END IF;
END$$
DELIMITER ;
9. 存儲過程創建後需要刪除時使用DROP PROCEDURE語句。
在此之前，必須確認該存儲過程沒有任何依賴關系，否則會導致其他與之關聯的存儲過程無法運行。
語法格式為： DROPPROCEDURE [IF EXISTS] sp_name
說明：sp_name是要刪除的存儲過程的名稱。IF EXISTS子句是MySQL的擴展，如果程序或函數不存在，它防止發生錯誤。
例： 刪除存儲過程dowhile：DROP PROCEDURE IF EXISTS dowhile;
10. 使用ALTER PROCEDURE語句可以修改存儲過程的某些特征。
語法格式為：ALTER PROCEDURE sp_name [characteristic ...]
其中，characteristic為：
www.2cto.com
{ CONTAINS SQL | NO SQL | READS SQLDATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
說明：characteristic是存儲過程創建時的特征，在CREATE PROCEDURE語句中已經介紹過。只要設定了其中的值，存儲過程的特征就隨之變化。
如果要修改存儲過程的內容，可以使用先刪除再重新定義存儲過程的方法。
例： 使用先刪除後修改的方法修改例7.12中的存儲過程。
DELIMITER $$
DROP PROCEDURE IF EXISTS DO_QUERY;
CREATE PROCEDURE DO_QUERY()
BEGIN
SELECT * FROM XS;
END$$
DELIMITER ;
***11 往後為選看內容。。非重點！！
11. SQL語句中的錯誤提示
在存儲過程中處理SQL語句可能導致一條錯誤消息。例如，向一個表中插入新的行而主鍵值已經存在，這條INSERT語句會導致一個出錯消息，並且MySQL立即停止對存儲過程的處理。每一個錯誤消息都有一個唯一代碼和一個SQLSTATE代碼。例如，SQLSTATE 23000屬於如下的出錯代碼：
Error 1022, "Can't write;duplicate（重復） key intable"
Error 1048, "Column cannot benull"
Error 1052, "Column is ambiguous（歧義）"
Error 1062, "Duplicate entry forkey"
MySQL手冊的“錯誤消息和代碼”一章中列出了所有的出錯消息及它們各自的代碼。
為了防止MySQL在一條錯誤消息產生時就停止處理，需要使用到DECLAREhandler語句。該語句語句為錯誤代碼聲明了一個所謂的處理程序，它指明：對一條SQL語句的處理如果導致一條錯誤消息，將會發生什麼。
DECLARE HANDLER語法格式為：
DECLARE handler_type HANDLER FOR condition_value[,...]sp_statement
其中，handler_type為：
Continue
| EXIT
| UNDO
condition_value為：
SQLstate [VALUE] sqlstate_value
www.2cto.com
| condition_name
| SQLwarning
| NOT FOUND
| SQLexception
| mysql_error_code
說明：
● handler_type：處理程序的類型，主要有三種：CONTINUE、EXIT和UNDO。對CONTINUE處理程序，MySQL不中斷存儲過程的處理。對於EXIT處理程序，當前 BEGIN...END復合語句的執行被終止。UNDO處理程序類型語句暫時還不被支持。
● condition_value：給出SQLSTATE的代碼表示。
condition_name是處理條件的名稱，接下來會講到。
SQLWARNING是對所有以01開頭的SQLSTATE代碼的速記。NOT FOUND是對所有以02開頭的SQLSTATE代碼的速記。SQLEXCEPTION是對所有沒有被SQLWARNING或NOT FOUND捕獲的SQLSTATE代碼的速記。當用戶不想為每個可能的出錯消息都定義一個處理程序時可以使用以上三種形式。
mysql_error_code是具體的SQLSTATE代碼。除了SQLSTATE值，MySQL錯誤代碼也被支持，表示的形式為：ERROR= 'xxxx'。
● sp_statement：處理程序激活時將要執行的動作。
例： 創建一個存儲過程，向XS表插入一行數據（'081101', '王民', '計算機', 1, '1990-02-10',50 , NULL, NULL），已知學號081101在XS表中已存在。如果出現錯誤，程序繼續進行。
USE XSCJ;
DELIMITER $$
CREATE PROCEDURE MY_INSERT ()
BEGIN
DECLARECONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;
SET@x=2;
INSERTINTO XS VALUES('081101', '王民', '計算機', 1, '1990-02-10', 50 , NULL, NULL);
SET@x=3; www.2cto.com
END$$
DELIMITER ;
說明：在調用存儲過程後，未遇到錯誤消息時處理程序未被激活，當執行INSERT語句出現出錯消息時，MySQL檢查是否為這個錯誤代碼定義了處理程序。如果有，則激活該處理程序，本例中，INSERT語句導致的錯誤消息剛好是SQLSTATE代碼中的一條。接下來執行處理程序的附加語句（SET @x2=1）。此後，MySQL檢查處理程序的類型，這裡的類型為CONTINUE，因此存儲過程繼續處理，將用戶變量x賦值為3。如果這裡的INSERT語句能夠執行，處理程序將不被激活，用戶變量x2將不被賦值。
注意：不能為同一個出錯消息在同一個BEGIN-END語句塊中定義兩個或更多的處理程序。
為了提高可讀性，可以使用DECLARE CONDITION語句為一個SQLSTATE或出錯代碼定義一個名字，並且可以在處理程序中使用這個名字。
DECLARE CONDITION語法格式為：
DECLARE condition_name CONDITION FORcondition_value
其中，condition_value：
SQLSTATE [VALUE] sqlstate_value
| mysql_error_code
說明：condition_name是處理條件的名稱，condition_value為要定義別名的SQLSTATE或出錯代碼。
例： 修改上例中的存儲過程，將SQLSTATE '23000' 定義成NON_UNIQUE，並在處理程序中使用這個名稱。程序片段為：
BEGIN
DECLARE NON_UNIQUE CONDITION FOR SQLSTATE '23000';
DECLARE CONTINUE HANDLER FOR NON_UNIQUE SET @x2=1;
SET @x=2;
INSERT INTO XS VALUES('081101', '王民', '計算機', 1, '1990-02-10', 50 , NULL, NULL);
SET @x=3; www.2cto.com
END;
12. 游標
一條SELECT...INTO語句返回的是帶有值的一行，這樣可以把數據讀取到存儲過程中。但是常規的SELECT語句返回的是多行數據，如果要處理它需要引入游標這一概念。MySQL支持簡單的游標。在MySQL中，游標一定要在存儲過程或函數中使用，不能單獨在查詢中使用。
使用一個游標需要用到4條特殊的語句：DECLARE CURSOR（聲明游標）、OPEN CURSOR（打開游標）、FETCH CURSOR（讀取游標）和CLOSE CURSOR（關閉游標）。
如果使用了DECLARE CURSOR語句聲明了一個游標，這樣就把它連接到了一個由SELECT語句返回的結果集中。使用OPEN CORSOR語句打開這個游標。接著，可以用FETCH CURSOR語句把產生的結果一行一行地讀取到存儲過程或存儲函數中去。游標相當於一個指針，它指向當前的一行數據，使用FETCH CORSOR語句可以把游標移動到下一行。當處理完所有的行時，使用CLOSECURSOR語句關閉這個游標。
（1）聲明游標
語法格式：DECLAREcursor_name cursor for select_statement
說明：cursor_name是游標的名稱，游標名稱使用與表名同樣的規則。select_statement是一個SELECT語句，返回的是一行或多行的數據。這個語句聲明一個游標，也可以在存儲過程中定義多個游標，但是一個塊中的每一個游標必須有唯一的名字。
注意：這裡的SELECT子句不能有INTO子句。
下面的定義符合一個游標聲明：
DECLARE XS_CUR1 CURSOR FOR
SELECT 學號,姓名,性別,出生日期,總學分
FROM XS
WHERE 專業名 = '計算機';
注意：游標只能在存儲過程或存儲函數中使用，例中語句無法單獨運行。
（2）打開游標
聲明游標後，要使用游標從中提取數據，就必須先打開游標。在MySQL中，使用OPEN語句打開游標，其格式為：OPEN cursor_name
在程序中，一個游標可以打開多次，由於其他的用戶或程序本身已經更新了表，所以每次打開結果可能不同。 www.2cto.com
（3）讀取數據
游標打開後，就可以使用fetch…into語句從中讀取數據。
語法格式：FETCH cursor_nameINTO var_name [, var_name] ...
說明：FETCH ...INTO語句與SELECT...INTO語句具有相同的意義，FETCH語句是將游標指向的一行數據賦給一些變量，子句中變量的數目必須等於聲明游標時SELECT子句中列的數目。var_name是存放數據的變量名。
（4）關閉游標
游標使用完以後，要及時關閉。關閉游標使用CLOSE語句，格式為：
CLOSE cursor_name語句參數的含義與OPEN語句中相同。
例如： CLOSE XS_CUR2 將關閉游標XS_CUR2。
例： 創建一個存儲過程，計算XS表中行的數目。
DELIMITER $$
CREATE PROCEDURE compute (OUT NUMBERINTEGER)
BEGIN
DECLAREXH CHAR(6);
DECLAREFOUND BOOLEAN DEFAULT TRUE;
DECLARENUMBER_XS CURSOR FOR
SELECT學號 FROM XS;
DECLARECONTINUE HANDLER FOR NOT FOUND
SETFOUND=FALSE;
SETNUMBER=0;
OPENNUMBER_XS;
FETCHNUMBER_XS INTO XH;
www.2cto.com
WHILEFOUND DO
SETNUMBER=NUMBER+1;
FETCHNUMBER_XS INTO XH;
ENDWHILE;
CLOSENUMBER_XS;
END$$
DELIMITER ;
作者 tianyazaiheruan
