《MySQL數(shù)據(jù)庫應(yīng)用與管理項目化教程》課件-項目7-4 編程語法

項目七-4編程語法任務(wù)目標(biāo)任務(wù)一變量任務(wù)二判斷語句任務(wù)三 循環(huán)控制語句任務(wù)四 異常處理任務(wù)六游標(biāo)

任務(wù)一變量變量

主要內(nèi)容:變量類型變量賦值變量應(yīng)用重點難點:變量應(yīng)用變量變量用于存儲臨時數(shù)據(jù)，構(gòu)成表達(dá)式最基本的存儲元素；MySQL變量用戶變量：帶有前綴@，只能被定義它的用戶使用，作用于當(dāng)前用戶整個連接，當(dāng)前連接一旦斷開，所定義的用戶變量全部被釋放。用戶變量不用聲明定義，可直接使用。局部變量：沒有前綴，一般用于SQL語句塊的BEGING...END中，作用域僅僅局限于定義它的語句塊，在語句塊執(zhí)行完畢后，局部變量就會被釋放。局部變量需要先用DECLARE聲明定義，再使用。系統(tǒng)變量：帶有前綴@@，包含全局變量和會話變量。全部變量影響整個服務(wù)器，會話變量只影響個人客戶端連接。變量賦值查閱語法：官網(wǎng)文檔：/doc/refman/8.0/en/declare.html局部變量定義語法：DECLAREvar_name[,var_name]...type[DEFAULTvalue]作用：定義局部變量；為變量賦值語法一：SETvariable=expr[,variable=expr]...#局部變量SET@variable=expr[,variable=expr]...#用戶變量語法二：SELECT

col_nameINTOvariableFROMtable_name;SELECT

col_nameINTO@variableFROMtable_name;變量應(yīng)用舉例例1：用戶變量：不用聲明定義，可直接使用。mysql>SET@var1=1,@var2=2;mysql>SELECTCOUNT(*)INTO@var3FROMitemWHERE

goods_id=@var1andquantity>@var2;mysql>SELECT@var3;例2：系統(tǒng)變量：直接獲取值mysql>SELECT@@global.sort_buffer_size;#全局變量值mysql>SELECT@@log_error;#全局變量值mysql>SELECT@@session.sort_buffer_size，@@warning_coun，@@error_count;#會話變量值變量應(yīng)用舉例例3：局部變量：一般在存儲過程或函數(shù)中使用mysql>DELIMITER//mysql>CREATEPROCEDUREitem_n()

BEGIN

DECLAREvar1,var2,var3INT;

SETvar1=1,var2=2;

SELECTCOUNT(*)INTOvar3FROMgoodsWHEREgoods_id=var1and

quantity>var2;

SELECTvar3;

END//mysql>DELIMITER;mysql>CALLgoods_id;任務(wù)二判斷語句判斷語句

主要內(nèi)容:

IF語句應(yīng)用

CASE語句應(yīng)用重點難點:應(yīng)用IF語句查閱語法：官網(wǎng)文檔：/doc/refman/8.0/en/flow-control-statements.html/doc/refman/8.0/en/if.html語法：IFsearch_conditionTHENstatement_list[ELSEIFsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDIF；作用：search_condition為判斷條件，statement_list為執(zhí)行的語句塊；當(dāng)條件為true,則執(zhí)行THEN后的語句塊，后直接進(jìn)入IF語句的下一個語句，否則，執(zhí)行ELSE子句操作。IF語句：應(yīng)用例1：在db_shop中，編寫一個存儲過程，按員工編號查詢判斷其薪水的級別。mysql>USEdb_shop;mysql>DELIMITER$$mysql>CREATEPROCEDUREp_stafer_if(INsidINT)

BEGINDECLAREsal_gradedecimal(8,2);SELECTsalaryintosal_gradeFROMstafferWHEREid=sid;IFsal_grade>=10000THENSELECTsal_grade,'高';ELSEIFsal_grade>=9000THENSELECTsal_grade,'良';ELSEIFsal_grade>=8000THENSELECTsal_grade,'中';ELSEIFsal_grade>=5000THENSELECTsal_grade,'基本';ELSESELECTsal_grade,'低';ENDIF;END$$mysql>DELIMITER;mysql>CALLp_stafer_if(4);IF語句：嵌套應(yīng)用例1：在db_shop中，編寫一個存儲過程，按員工編號查詢，若有該編號員工顯示其薪水的級別，否則顯示“無人”。mysql>DELIMITER$$mysql>CREATEPROCEDUREp_stafer_if2(INsidINT)BEGINDECLAREsalary2decimal(8,2);SELECTsalaryINTOsalary2FROMstafferWHEREid=sid;IFsalary2ISNOTNULLTHENIFsalary2>8000THENSELECTsid,salary2,'高'AS級別;ELSESELECTsid,salary2,'繼續(xù)加油！'AS級別;ENDIF;ELSESELECT'無此人！';ENDIF;END$$mysql>DELIMITER;mysql>CALLp_stafer_if2(20);mysql>CALLp_stafer_if2(4);IF語句：嵌套應(yīng)用（函數(shù)）例2：mysql>showvariableslike'log_bin_trust_function_creators';#查看設(shè)置，可以在f配置文件中添加：log_bin_trust_function_creators=1mysql>setgloballog_bin_trust_function_creators=1;#如果出現(xiàn)ThisfunctionhasnoneofDETERMINISTIC,NOSQL,orREADSSQLDATAin...

mysql>DELIMITER//mysql>CREATEFUNCTIONVerboseCompare(nINT,mINT)RETURNSVARCHAR(50)BEGINDECLAREsVARCHAR(50);IFn=mTHENSETs='equals';ELSEIFn>mTHENSETs='greater';ELSESETs='less';ENDIF;SETs=CONCAT('is',s,'than');ENDIF;SETs=CONCAT(n,'',s,'',m,'.');RETURNs;END//mysql>DELIMITER;mysql>SELECTVerboseCompare(2,3);mysql>SELECTVerboseCompare(4,3);CASE語句查閱語法：官網(wǎng)文檔：/doc/refman/8.0/en/case.html語法一：CASEcase_valueWHENwhen_valueTHENstatement_list[WHENwhen_valueTHENstatement_list]...[ELSEstatement_list]ENDCASE；作用：對case_value和when_value進(jìn)行等值判斷，條件為true則執(zhí)行相應(yīng)語句，否則判斷下一個WHEN值。CASE語句一例如：例1：mysql>USEdb_shop;mysql>DELIMITER$$mysql>CREATEPROCEDUREp_stafer_case(INsidINT)BEGINDECLAREsxenum('F','M');SELECTsexintosxFROMstafferWHEREid=sid;CASEsxWHEN'F'THENSELECT1000AS補貼;WHEN'M'THENSELECT800AS補貼; ELSEBEGIN

SELECT200AS補貼; SELECT'員工要標(biāo)記性別才有補貼';END;ENDCASE;END$$例如：例1：mysql>DELIMITER;mysql>CALLp_stafer_case(4);#調(diào)用存儲過程CASE語句二語法二：CASEWHENsearch_conditionTHENstatement_list[WHENsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDCASE；作用：對search_condition進(jìn)行判斷，條件為true則執(zhí)行相應(yīng)語句，否則判斷下一個WHEN條件。CASE語句二作用：對search_condition進(jìn)行判斷，條件為true則執(zhí)行相應(yīng)語句，否則判斷下一個WHEN條件。例如：執(zhí)行語句如下：mysql>USEdb_shop;mysql>DELIMITER$$mysql>CREATEPROCEDUREp_stafer_case2(INsidINT)BEGINDECLAREsalary2decimal(8,2);SELECTsalaryINTOsalary2FROMstafferWHEREid=sid;CASEWHENsalary2>10000THENSELECTsid,salary2,'高'AS級別;WHENsalary2>5000THENSELECTsid,salary2,'基本'AS級別;ELSESELECTsid,salary2,'繼續(xù)加油！'AS級別;ENDCASE;END$$mysql>DELIMITER;mysql>CALLp_stafer_case2(6);CASE語句：應(yīng)用例1：mysql>DELIMITER$$mysql>CREATEPROCEDUREp()BEGINDECLAREvINTDEFAULT1;CASEvWHEN2THENSELECTv;WHEN3THENSELECT0;ELSE

BEGINSETV=V*-1;SELECTV;#-----局部變量必須在存儲過程中顯示值；END;ENDCASE;END$$mysql>DELIMITER;mysql>CALLp;#調(diào)用存儲過程任務(wù)三循環(huán)語句循環(huán)語句

主要內(nèi)容:WHILE語句應(yīng)用REPEAT語句應(yīng)用LOOP語句應(yīng)用重點難點:應(yīng)用循環(huán)語句：WHILE查閱語法：官網(wǎng)文檔：/doc/refman/8.0/en/while.html語法：WHILEsearch_conditionDO statement_listENDWHILE;循環(huán)語句：WHILE應(yīng)用例1：在db_shop數(shù)據(jù)庫中，建立一個dowhile的存儲過程，實現(xiàn)一個記數(shù)1000次的延時功能；mysql>delimiter//mysql>CREATEPROCEDUREdowhile()BEGINDECLAREv1INTDEFAULT1000;WHILEv1>0DOBEGIN

SETv1=v1-1;END;#----------------------注意：END后面要加分號“;”ENDWHILE;SELECTV1;END//mysql>delimiter;mysql>CALLdowhile;循環(huán)語句：REPEAT查閱語法：官網(wǎng)文檔：/doc/refman/8.0/en/repeat.html語法：[begin_label:]REPEATstatement_listUNTILsearch_conditionENDREPEAT[end_label]循環(huán)語句：REPEAT應(yīng)用例1：在db_shop數(shù)據(jù)庫中，建立一個dorepeat的存儲過程，實現(xiàn)一個記數(shù)p1次的延時功能；mysql>delimiter//mysql>CREATEPROCEDUREdorepeat(p1INT)BEGINSET@x=0;REPEATSET@x=@x+1;UNTIL@x>p1ENDREPEAT;END//mysql>delimiter;mysql>CALLdorepeat(1000);mysql>SELECT@X;循環(huán)語句：LOOP查閱語法：官網(wǎng)文檔：/doc/refman/8.0/en/loop.html語法：

[Loop_label]:LOOPstatement_list IFexit_conditionTHEN LEAVELoop_label; ENDIF;ENDLOOP[Loop_label];循環(huán)語句：LOOP應(yīng)用例1：在db_shop數(shù)據(jù)庫中，建立一個doiterate的存儲過程，實現(xiàn)一個記數(shù)n次的延時功能；mysql>delimiter//mysql>CREATEPROCEDUREdoiterate(INnINT)BEGINSET@x=0;label1:LOOP

SET@x=@x+1;

IF@x<=nTHEN

ITERATElabel1;#再次循環(huán)，類似CONTINUE;

ENDIF;

LEAVElabel1;#退出循環(huán)；

ENDLOOPlabel1;END//mysql>DELIMITER;mysql>CALLdoiterate(1000);mysql>SELECT@x;#-----用戶變量必須在存儲過程外顯示值；任務(wù)四異常處理異常處理

主要內(nèi)容:異常處理作用

異常的處理異常處理的應(yīng)用拋出異常重點難點:異常處理的應(yīng)用異常處理作用了解MySQL出現(xiàn)的錯誤信息當(dāng)客戶端線程向MySQL服務(wù)器提交命令后，執(zhí)行是否成功，服務(wù)器端都會返回其相關(guān)判斷性數(shù)據(jù)。若是執(zhí)行過程中出錯了，MySQL會返回兩類信息：A.MySQL特殊的錯誤編碼（如1146），是一個整數(shù)類型的代碼，這些錯誤編碼并不適用于其他的數(shù)據(jù)庫產(chǎn)品；B.MySQL的SQL狀態(tài)值，由5個字符組成（如42S02），該值支持ANSISQL、ODBC或其他標(biāo)準(zhǔn)協(xié)議，并不是所有的MySQL返回的錯誤信息都有SQL狀態(tài)值，對于沒有SQL狀態(tài)值使用‘HY000’代替。異常處理方式當(dāng)存儲過程中發(fā)生錯誤時，通過捕捉異常和處理，防止出現(xiàn)系統(tǒng)錯誤。例如：繼續(xù)或退出當(dāng)前代碼塊的執(zhí)行，并發(fā)出有意義的錯誤消息。1、可以使用MYSQL系統(tǒng)定義的大類異常錯誤信息，較籠統(tǒng)地表達(dá)異常錯誤；2、也使用MYSQL系統(tǒng)定義或用戶定義的精準(zhǔn)異常錯誤信息，準(zhǔn)確記錄錯誤信息。3、可以將捕捉的異常信息存入到日志表中，以方便查詢跟蹤。異常處理查閱語法：官網(wǎng)文檔：https:///doc/refman/8.0/en/declare-handler.html語法：DECLAREhandler_actionHANDLERFORcondition_value[,condition_value]...statementhandler_action:{

CONTINUE|EXIT|UNDO}condition_value:{

mysql_error_code|SQLSTATE[VALUE]sqlstate_value|condition_name|SQLWARNING|NOTFOUND|SQLEXCEPTION}異常處理statement：異常處理時，執(zhí)行的SQL語句或語句塊；handler_action:CONTINUE|EXIT|UNDO對指定的異常錯誤情況進(jìn)行的處理。當(dāng)執(zhí)行完上面的異常處理的statementSQL語句后，對出現(xiàn)的錯誤希望執(zhí)行動作:CONTINUE：表示繼續(xù)，是SQLWARNING和NOTFOUND的默認(rèn)處理方法；EXIT：退出，SQLEXCEPTION的默認(rèn)處理方法。UNDO：撤銷異常處理condition_value[,condition_value]：針對一個或多個異常

1、mysql_error_code，這個表示mysql的錯誤代碼，錯誤代碼是一個數(shù)字，完成是由mysql自己定義的，這個值可以參考mysql數(shù)據(jù)庫錯誤代碼及信息。

2、SQLSTATE[VALUE]sqlstate_value，這個同錯誤代碼類似形成一一對應(yīng)的關(guān)系，它是一個5個字符組成的字符串，關(guān)鍵的地方是它從ANSISQL和ODBC這些標(biāo)準(zhǔn)中引用過來的，因此更加標(biāo)準(zhǔn)化，而不像上面的error_code完全是mysql自己定義給自己用的，這個和第一個類似也可以參考mysql數(shù)據(jù)庫錯誤代碼及信息。

3、condtion_name，這個是條件名稱，它使用DECLARE...CONDITION語句來定義，就是自定義異常名稱。

4、SQLWARNING，表示SQLTATE中的字符串以‘01’起始的那些錯誤，比如Error:1311SQLSTATE:01000(ER_SP_UNINIT_VAR)

5、NOTFOUND，表示SQLTATE中的字符串以‘02’起始的那些錯誤，比如Error:1329SQLSTATE:02000(ER_SP_FETCH_NO_DATA)

6、SQLEXCEPTION，表示SQLSTATE中的字符串不是以'00'、'01'、'02'起始的那些錯誤，這里'00'起始的SQLSTATE其實表示的是成功執(zhí)行而不是錯誤。說明：這里第1、2、3異常處理是精準(zhǔn)信息的異常處理；第4、5、6異常處理是對應(yīng)類型的異常處理，給出是比較籠統(tǒng)的信息。異常處理例1：觀察異常處理的作用:對比下面在沒有異常處理語句、CONTINUE、EXIT的區(qū)別；異常代碼：SQLSTATE

'23000'與對應(yīng)mysql錯誤號1062的用法。mysql>CREATETABLEt_handler(s1INT,PRIMARYKEY(s1));mysql>DELIMITER//mysql>CREATEPROCEDUREhandlerdemo()BEGINDECLARECONTINUEHANDLERFORSQLSTATE'23000'SET@info='重復(fù)key繼續(xù)執(zhí)行';

#DECLARECONTINUEHANDLERFOR1062SET@info='重復(fù)key繼續(xù)執(zhí)行';

#DECLAREEXITHANDLERFORSQLSTATE'23000'SET@info='重復(fù)key退出';#------------------對比前一句SET@X=1;INSERTINTOt_handlerVALUES(1);SET@X=2;INSERTINTOt_handlerVALUES(1);SET@X=3;END//mysql>DELIMITER;mysql>CALLhandlerdemo();mysql>SELECT@X,@info;異常處理例2：捕捉異常信息存入日志表mysql>CREATETABLEtb_err_info(idINTAUTO_INCREMENTPRIMARYKEY,exception_infoVARCHAR(50),create_timedatetime);mysql>DELIMITER//mysql>CREATEPROCEDUREhandlerdemo_ins()BEGIN

DECLAREEXITHANDLERFOR1062#異常處理的內(nèi)容可以是SQL語句塊

BEGIN

INSERTINTOtb_err_info(exception_info,create_time)VALUES(concat(@@error_count,'重復(fù)key退出'),NOW());

END;DECLAREEXITHANDLERFORSQLEXCEPTIONINSERTINTOtb_err_info(exception_info,create_time)VALUES(concat(@@error_count,'errors'),NOW());DECLARECONTINUEHANDLERFORSQLWARNINGINSERTINTOtb_err_info(exception_info,create_time)VALUES(concat(@@warning_count,'warnings'),NOW());SET@X=1;INSERTINTOt_handlerVALUES(1);SET@X=2;INSERTINTOt_handlerVALUES(1);SET@X=3;END//mysql>DELIMITER;mysql>CALLhandlerdemo_ins();mysql>SELECT@X；mysql>SELECT*FROMtb_err_info;自定義異常名稱查閱語法：官網(wǎng)文檔：/doc/refman/8.0/en/declare-condition.html定義異常名稱語法：DECLAREcondition_nameCONDITIONFORcondition_valuecondition_value:{mysql_error_code|SQLSTATE[VALUE]sqlstate_value}調(diào)用異常名稱進(jìn)行異常處理：DECLAREhandler_actionHANDLERFORcondition_namestatement自定義異常調(diào)用例3：捕捉異常信息存入日志表，使用自定義異常（不如直接使用異常處理簡潔）mysql>DELIMITER//mysql>CREATEPROCEDUREhandlerdemo_ins()BEGIN先定義一個異常名稱，再調(diào)用進(jìn)行異常處理：DECLAREDUP_keyCONDITIONFOR1062;DECLAREEXITHANDLERFORDUP_key#DECLAREEXITHANDLERFOR1062

BEGININSERTINTOtb_err_info(exception_info,create_time)VALUES(concat(@@error_count,'重復(fù)key退出'),NOW());END;DECLAREEXITHANDLERFORSQLEXCEPTIONINSERTINTOtb_err_info(exception_info,create_time)VALUES(concat(@@error_count,'errors'),NOW());DECLARECONTINUEHANDLERFORSQLWARNINGINSERTINTOtb_err_info(exception_info,create_time)VALUES(concat(@@warning_count,'warnings'),NOW());SET@X=1;INSERTINTOt_handlerVALUES(1);SET@X=2;INSERTINTOt_handlerVALUES(1);SET@X=3;END//mysql>DELIMITER;mysql>CALLhandlerdemo_ins();mysql>SELECT@Xmysql>SELECT*FROMtb_err_info;拋出異常查閱語法：官網(wǎng)文檔：/doc/refman/8.0/en/signal.html#signal-condition-information-items語法：SIGNALcondition_value[SETsignal_information_item[,signal_information_item]...]condition_value:{SQLSTATE[VALUE]sqlstate_value|condition_name}signal_information_item:condition_information_item_name=simple_value_specification拋出異常例：在db_shop數(shù)據(jù)庫中，建立一個存儲過程，用于在添加訂單號時檢查詳細(xì)訂單表是否已經(jīng)存在該訂單號，存在則拋出異常。執(zhí)行語句如下：mysql>USEdb_shop;mysql>DELIMITER$$mysql>CREATEPROCEDUREAddOrderItem(inordersNoint)BEGINDECLAREnINT;SELECTCOUNT(*)INTOnFROMitemWHEREorder_id=ordersNo;--檢查是否存在訂單的詳細(xì)商品IF(n<>0)THENSIGNALSQLSTATE'45000'SETMESSAGE_TEXT='item表沒有這樣的訂單編號';ENDIF;SELECTn;--顯示訂單的條目數(shù)量。END$$mysql>DELIMITER;mysql>CALLAddOrderItem(12);mysql>CALLAddOrderItem(1);游標(biāo)在存儲過程和函數(shù)中，當(dāng)查詢語句可能返回多條記錄，使用游標(biāo)可以實現(xiàn)逐條讀取結(jié)果集中的記錄。游標(biāo)的使用聲明光標(biāo)打開光標(biāo)使用光標(biāo)關(guān)閉光標(biāo)游標(biāo)查閱語法：官網(wǎng)文檔：/doc/refman/8.0/en/cursors.html聲明游標(biāo)語法：DECLAREcursor_nameCURSORFORselect_statement打開光標(biāo)語法：OPENcursor_name;游標(biāo)使用游標(biāo)：FETCH讀取數(shù)據(jù)語法：FETCH[[NEXT]FROM]cursor_nameINTOvar_name[,var_name]...關(guān)閉游標(biāo)語法：CLOSEcursor_name;mysql>DELIMITER//mysql>DROPPROCEDUREIFEXISTSstaff_cursor//#建立存儲過程createCREATEPROCEDUREstaff_cursor()BEGIN#局部變量的定義declareDECLAREtmpNameVARCHAR(20)DEFAULT'';DECLAREtmpidVARCHAR(20)DEFAULT'';DECLAREallNameVARCHAR(255)DEFAULT'';DECLAREcur1CURSORFORSELECTid,staff_nameFROMstaffer;#MySQL游標(biāo)異常后捕捉#并設(shè)置循環(huán)使用變量tmpname為null跳出循環(huán)。declareCONTINUEHANDLERFORSQLSTATE'02000'SETtmpname=null;#打開游標(biāo)OPENcur1;#游標(biāo)向下走一步FETCHNEXTFROMcur1INTOtmpid,tmpName;SELECTtmpid,tmpName;WHILEtmpnameISNOTNULLDO SELECTtmpid,tmpName; FETCHNEXTFROMcur1INTOtmpid,tmpName;#或：FETCHcur1INTOtmpid,tmpName;ENDWHILE;#關(guān)閉游標(biāo)CLOSEcur1;END//mysql>DELIMITER;mysql>CALLstaff_cursor();游標(biāo)：應(yīng)用例如：DELIMITER//mysql>dropprocedureifexistsstaff_cursor//#建立存儲過程createmysql>CREATEPROCEDUREstaff_cursor()BEGIN#局部變量的定義declaredeclaretmpNamevarchar(20)default'';declaretmpidvarchar(20)default'';declareallNamevarchar(255)default'';declarecur1CURSORFORSELECTid,staff_nameFROMstaffer;#MySQL游標(biāo)異常后捕捉#并設(shè)置循環(huán)使用變量tmpname為null跳出循環(huán)。declareCONTINUEHANDLERFORSQLSTATE'02000'SETtmpname=null;#開游標(biāo)OPENcur1;#游標(biāo)向下走一步FETCHcur1INTOtmpid,tmpName;#或：FETCHnextFROMcur1INTOtmpid,tmpName;#循環(huán)體，將查詢出的name都加起并用;號隔開WHILE(tmpnameisnotnull)DOsettmpid=CONCAT(tmpid,"|");settmpName=CONCAT(tmpName,";");setallName=CONCAT(allName,tmpid);setallName=CONCAT(allName,tmpName);#游標(biāo)向下讀取下一條