《MySQL数据库应用与管理项目化教程》课件-项目7-4 编程语法

项目七-4编程语法任务目标任务一变量任务二判断语句任务三 循环控制语句任务四 异常处理任务六游标

任务一变量变量

主要内容:变量类型变量赋值变量应用重点难点:变量应用变量变量用于存储临时数据，构成表达式最基本的存储元素；MySQL变量用户变量：带有前缀@，只能被定义它的用户使用，作用于当前用户整个连接，当前连接一旦断开，所定义的用户变量全部被释放。用户变量不用声明定义，可直接使用。局部变量：没有前缀，一般用于SQL语句块的BEGING...END中，作用域仅仅局限于定义它的语句块，在语句块执行完毕后，局部变量就会被释放。局部变量需要先用DECLARE声明定义，再使用。系统变量：带有前缀@@，包含全局变量和会话变量。全部变量影响整个服务器，会话变量只影响个人客户端连接。变量赋值查阅语法：官网文档：/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;变量应用举例例1：用户变量：不用声明定义，可直接使用。mysql>SET@var1=1,@var2=2;mysql>SELECTCOUNT(*)INTO@var3FROMitemWHERE

goods_id=@var1andquantity>@var2;mysql>SELECT@var3;例2：系统变量：直接获取值mysql>SELECT@@global.sort_buffer_size;#全局变量值mysql>SELECT@@log_error;#全局变量值mysql>SELECT@@session.sort_buffer_size，@@warning_coun，@@error_count;#会话变量值变量应用举例例3：局部变量：一般在存储过程或函数中使用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;任务二判断语句判断语句

主要内容:

IF语句应用

CASE语句应用重点难点:应用IF语句查阅语法：官网文档：/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为执行的语句块；当条件为true,则执行THEN后的语句块，后直接进入IF语句的下一个语句，否则，执行ELSE子句操作。IF语句：应用例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语句：嵌套应用例1：在db_shop中，编写一个存储过程，按员工编号查询，若有该编号员工显示其薪水的级别，否则显示“无人”。mysql>DELIMITER$$mysql>CREATEPROCEDUREp_stafer_if2(INsidINT)BEGINDECLAREsalary2decimal(8,2);SELECTsalaryINTOsalary2FROMstafferWHEREid=sid;IFsalary2ISNOTNULLTHENIFsalary2>8000THENSELECTsid,salary2,'高'AS级别;ELSESELECTsid,salary2,'继续加油！'AS级别;ENDIF;ELSESELECT'无此人！';ENDIF;END$$mysql>DELIMITER;mysql>CALLp_stafer_if2(20);mysql>CALLp_stafer_if2(4);IF语句：嵌套应用（函数）例2：mysql>showvariableslike'log_bin_trust_function_creators';#查看设置，可以在f配置文件中添加：log_bin_trust_function_creators=1mysql>setgloballog_bin_trust_function_creators=1;#如果出现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语句查阅语法：官网文档：/doc/refman/8.0/en/case.html语法一：CASEcase_valueWHENwhen_valueTHENstatement_list[WHENwhen_valueTHENstatement_list]...[ELSEstatement_list]ENDCASE；作用：对case_value和when_value进行等值判断，条件为true则执行相应语句，否则判断下一个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'员工要标记性别才有补贴';END;ENDCASE;END$$例如：例1：mysql>DELIMITER;mysql>CALLp_stafer_case(4);#调用存储过程CASE语句二语法二：CASEWHENsearch_conditionTHENstatement_list[WHENsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDCASE；作用：对search_condition进行判断，条件为true则执行相应语句，否则判断下一个WHEN条件。CASE语句二作用：对search_condition进行判断，条件为true则执行相应语句，否则判断下一个WHEN条件。例如：执行语句如下：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,'继续加油！'AS级别;ENDCASE;END$$mysql>DELIMITER;mysql>CALLp_stafer_case2(6);CASE语句：应用例1：mysql>DELIMITER$$mysql>CREATEPROCEDUREp()BEGINDECLAREvINTDEFAULT1;CASEvWHEN2THENSELECTv;WHEN3THENSELECT0;ELSE

BEGINSETV=V*-1;SELECTV;#-----局部变量必须在存储过程中显示值；END;ENDCASE;END$$mysql>DELIMITER;mysql>CALLp;#调用存储过程任务三循环语句循环语句

主要内容:WHILE语句应用REPEAT语句应用LOOP语句应用重点难点:应用循环语句：WHILE查阅语法：官网文档：/doc/refman/8.0/en/while.html语法：WHILEsearch_conditionDO statement_listENDWHILE;循环语句：WHILE应用例1：在db_shop数据库中，建立一个dowhile的存储过程，实现一个记数1000次的延时功能；mysql>delimiter//mysql>CREATEPROCEDUREdowhile()BEGINDECLAREv1INTDEFAULT1000;WHILEv1>0DOBEGIN

SETv1=v1-1;END;#----------------------注意：END后面要加分号“;”ENDWHILE;SELECTV1;END//mysql>delimiter;mysql>CALLdowhile;循环语句：REPEAT查阅语法：官网文档：/doc/refman/8.0/en/repeat.html语法：[begin_label:]REPEATstatement_listUNTILsearch_conditionENDREPEAT[end_label]循环语句：REPEAT应用例1：在db_shop数据库中，建立一个dorepeat的存储过程，实现一个记数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;循环语句：LOOP查阅语法：官网文档：/doc/refman/8.0/en/loop.html语法：

[Loop_label]:LOOPstatement_list IFexit_conditionTHEN LEAVELoop_label; ENDIF;ENDLOOP[Loop_label];循环语句：LOOP应用例1：在db_shop数据库中，建立一个doiterate的存储过程，实现一个记数n次的延时功能；mysql>delimiter//mysql>CREATEPROCEDUREdoiterate(INnINT)BEGINSET@x=0;label1:LOOP

SET@x=@x+1;

IF@x<=nTHEN

ITERATElabel1;#再次循环，类似CONTINUE;

ENDIF;

LEAVElabel1;#退出循环；

ENDLOOPlabel1;END//mysql>DELIMITER;mysql>CALLdoiterate(1000);mysql>SELECT@x;#-----用户变量必须在存储过程外显示值；任务四异常处理异常处理

主要内容:异常处理作用

异常的处理异常处理的应用抛出异常重点难点:异常处理的应用异常处理作用了解MySQL出现的错误信息当客户端线程向MySQL服务器提交命令后，执行是否成功，服务器端都会返回其相关判断性数据。若是执行过程中出错了，MySQL会返回两类信息：A.MySQL特殊的错误编码（如1146），是一个整数类型的代码，这些错误编码并不适用于其他的数据库产品；B.MySQL的SQL状态值，由5个字符组成（如42S02），该值支持ANSISQL、ODBC或其他标准协议，并不是所有的MySQL返回的错误信息都有SQL状态值，对于没有SQL状态值使用‘HY000’代替。异常处理方式当存储过程中发生错误时，通过捕捉异常和处理，防止出现系统错误。例如：继续或退出当前代码块的执行，并发出有意义的错误消息。1、可以使用MYSQL系统定义的大类异常错误信息，较笼统地表达异常错误；2、也使用MYSQL系统定义或用户定义的精准异常错误信息，准确记录错误信息。3、可以将捕捉的异常信息存入到日志表中，以方便查询跟踪。异常处理查阅语法：官网文档：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：异常处理时，执行的SQL语句或语句块；handler_action:CONTINUE|EXIT|UNDO对指定的异常错误情况进行的处理。当执行完上面的异常处理的statementSQL语句后，对出现的错误希望执行动作:CONTINUE：表示继续，是SQLWARNING和NOTFOUND的默认处理方法；EXIT：退出，SQLEXCEPTION的默认处理方法。UNDO：撤销异常处理condition_value[,condition_value]：针对一个或多个异常

1、mysql_error_code，这个表示mysql的错误代码，错误代码是一个数字，完成是由mysql自己定义的，这个值可以参考mysql数据库错误代码及信息。

2、SQLSTATE[VALUE]sqlstate_value，这个同错误代码类似形成一一对应的关系，它是一个5个字符组成的字符串，关键的地方是它从ANSISQL和ODBC这些标准中引用过来的，因此更加标准化，而不像上面的error_code完全是mysql自己定义给自己用的，这个和第一个类似也可以参考mysql数据库错误代码及信息。

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其实表示的是成功执行而不是错误。说明：这里第1、2、3异常处理是精准信息的异常处理；第4、5、6异常处理是对应类型的异常处理，给出是比较笼统的信息。异常处理例1：观察异常处理的作用:对比下面在没有异常处理语句、CONTINUE、EXIT的区别；异常代码：SQLSTATE

'23000'与对应mysql错误号1062的用法。mysql>CREATETABLEt_handler(s1INT,PRIMARYKEY(s1));mysql>DELIMITER//mysql>CREATEPROCEDUREhandlerdemo()BEGINDECLARECONTINUEHANDLERFORSQLSTATE'23000'SET@info='重复key继续执行';

#DECLARECONTINUEHANDLERFOR1062SET@info='重复key继续执行';

#DECLAREEXITHANDLERFORSQLSTATE'23000'SET@info='重复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#异常处理的内容可以是SQL语句块

BEGIN

INSERTINTOtb_err_info(exception_info,create_time)VALUES(concat(@@error_count,'重复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;自定义异常名称查阅语法：官网文档：/doc/refman/8.0/en/declare-condition.html定义异常名称语法：DECLAREcondition_nameCONDITIONFORcondition_valuecondition_value:{mysql_error_code|SQLSTATE[VALUE]sqlstate_value}调用异常名称进行异常处理：DECLAREhandler_actionHANDLERFORcondition_namestatement自定义异常调用例3：捕捉异常信息存入日志表，使用自定义异常（不如直接使用异常处理简洁）mysql>DELIMITER//mysql>CREATEPROCEDUREhandlerdemo_ins()BEGIN先定义一个异常名称，再调用进行异常处理：DECLAREDUP_keyCONDITIONFOR1062;DECLAREEXITHANDLERFORDUP_key#DECLAREEXITHANDLERFOR1062

BEGININSERTINTOtb_err_info(exception_info,create_time)VALUES(concat(@@error_count,'重复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;抛出异常查阅语法：官网文档：/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数据库中，建立一个存储过程，用于在添加订单号时检查详细订单表是否已经存在该订单号，存在则抛出异常。执行语句如下：mysql>USEdb_shop;mysql>DELIMITER$$mysql>CREATEPROCEDUREAddOrderItem(inordersNoint)BEGINDECLAREnINT;SELECTCOUNT(*)INTOnFROMitemWHEREorder_id=ordersNo;--检查是否存在订单的详细商品IF(n<>0)THENSIGNALSQLSTATE'45000'SETMESSAGE_TEXT='item表没有这样的订单编号';ENDIF;SELECTn;--显示订单的条目数量。END$$mysql>DELIMITER;mysql>CALLAddOrderItem(12);mysql>CALLAddOrderItem(1);游标在存储过程和函数中，当查询语句可能返回多条记录，使用游标可以实现逐条读取结果集中的记录。游标的使用声明光标打开光标使用光标关闭光标游标查阅语法：官网文档：/doc/refman/8.0/en/cursors.html声明游标语法：DECLAREcursor_nameCURSORFORselect_statement打开光标语法：OPENcursor_name;游标使用游标：FETCH读取数据语法：FETCH[[NEXT]FROM]cursor_nameINTOvar_name[,var_name]...关闭游标语法：CLOSEcursor_name;mysql>DELIMITER//mysql>DROPPROCEDUREIFEXISTSstaff_cursor//#建立存储过程createCREATEPROCEDUREstaff_cursor()BEGIN#局部变量的定义declareDECLAREtmpNameVARCHAR(20)DEFAULT'';DECLAREtmpidVARCHAR(20)DEFAULT'';DECLAREallNameVARCHAR(255)DEFAULT'';DECLAREcur1CURSORFORSELECTid,staff_nameFROMstaffer;#MySQL游标异常后捕捉#并设置循环使用变量tmpname为null跳出循环。declareCONTINUEHANDLERFORSQLSTATE'02000'SETtmpname=null;#打开游标OPENcur1;#游标向下走一步FETCHNEXTFROMcur1INTOtmpid,tmpName;SELECTtmpid,tmpName;WHILEtmpnameISNOTNULLDO SELECTtmpid,tmpName; FETCHNEXTFROMcur1INTOtmpid,tmpName;#或：FETCHcur1INTOtmpid,tmpName;ENDWHILE;#关闭游标CLOSEcur1;END//mysql>DELIMITER;mysql>CALLstaff_cursor();游标：应用例如：DELIMITER//mysql>dropprocedureifexistsstaff_cursor//#建立存储过程createmysql>CREATEPROCEDUREstaff_cursor()BEGIN#局部变量的定义declaredeclaretmpNamevarchar(20)default'';declaretmpidvarchar(20)default'';declareallNamevarchar(255)default'';declarecur1CURSORFORSELECTid,staff_nameFROMstaffer;#MySQL游标异常后捕捉#并设置循环使用变量tmpname为null跳出循环。declareCONTINUEHANDLERFORSQLSTATE'02000'SETtmpname=null;#开游标OPENcur1;#游标向下走一步FETCHcur1INTOtmpid,tmpName;#或：FETCHnextFROMcur1INTOtmpid,tmpName;#循环体，将查询出的name都加起并用;号隔开WHILE(tmpnameisnotnull)DOsettmpid=CONCAT(tmpid,"|");settmpName=CONCAT(tmpName,";");setallName=CONCAT(allName,tmpid);setallName=CONCAT(allName,tmpName);#游标向下读取下一条