MySQL心得7-1-存儲(chǔ)過程_MySQL

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

作者 tianyazaiheruan bitsCN.com

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com