项目10 事务与存储过程

事务与存储过程项目10【能力目标】

掌握事务的相关概‍念。

掌握事务的使用方‍法。

掌握存储过程的使‍用。

熟悉程序流程控制基本语‍句【素养目标】培养缜密的思维方式和较强的分析能力。。目标学习导航图10-1项目10所讲内容在数据库系统开发中的位置管理事务10.110.1.1了解事务的概念1．事务的基本概念事务可以理解为由多条SQL语句组成，用以完成一个业务功能的共同体。事务可以保证数据的一致性，事务处理是将多个操作或者命令一起执行，所有操作或命令全部执行成功才意味着该事务的成功，任何一个操作或命令失败都意味着该事务失败。只有多个操作全部成功，事务才能成功结束，并且会进行提交（COMMIT）；如果任何一个操作失败，则强制回滚（ROLLBACK）到初始状态。在设置事务之前，需要认真思考确保操作对象数据一致性的有效预防和保护措施，培养缜密的思维方式和较强的分析能‍力。素养小贴士10.1.1了解事务的概念①

MySQL中只有使用了InnoDB数据库引擎的数据库或数据表才支持事务，使用事务时可以设置自动提交功能是否开启。②事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执‍行。

③事务可以用来管理INSERT、UPDATE、DELETE语句。DROP、ALTER语句不能通过事务处理，会直接提‍交。10.1.1了解事务的概念2．事务分类事务一般分为两种：隐式事务和显式事务。在MySQL中，事务默认是自动提交的，所以说每条DML语句（INSERT、UPDATE、DELETE）实际上都是一次执行事务的过程。①隐式事务：没有开启和结束的标志，默认执行完SQL语句就自动提交。例如，我们经常使用的INSERT、UPDATE、DELETE语句就属于隐式事务。②显式事务：需要显式地开启、关闭，然后执行一系列操作，最后如果全部操作都成功执行，则提交事务；如果操作有异常，则回滚事务中的所有操作。10.1.1了解事务的概念3.事务的四大基本特性ACID

（1）原子性（Atomicity）

事务包含的所有操作要么全部成功，要么全部失败回滚，事务是一个不可分割的整体。

（2）一致性（Consistency）

事务开始前和结束后，数据库的完整性约束没有被破坏。

（3）隔离性（Isolation）

同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干‍扰。

（4）持久性（Durability）

持久性是指一个事务一旦被提交了，对数据库中数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操‍作。10.1.1了解事务的概念4．事务的并发问题当多个线程都开启事务操作数据库中的数据时，可能会产生如下几种问题。（1）脏读脏读又称无效数据的读出，是指在数据库访问过程中，事务（A）将某一值修改，然后事务（B）读取该值，此后事务（A）因为某种原因撤销对该值的修改，导致事务（B）所读取到的数据是无效的。（2）不可重复读取不可重复读取是指在某事务处理过程中对数据进行读取时，由于该事务的更新操作导致多次读取的数据发生了改变。（3）幻读幻读又称幻象读取是指在某事务处理数据过程中，由于该事务的插入或删除操作导致在多次读取过程中读取到不存在或者消失的数据。SETautocommit=1;10.1.2

提交事务1．事务提交状态查询查询当前自动提交功能的状态可以使用如下命令。也可以使用以下命令。要设置自动提交功能可以使用SELECT@@autocommit;SHOWVARIABLESLIKE'autocommit';图10-2自动提交状态查询10.1.2

提交事务2．使用COMMIT命令提交事务关闭事务的自动提交后，需通过COMMIT命令进行事务的提交。SHOWVARIABLESLIKE'autocommit';SETautocommit=0;STARTTRANSACTION;UPDATEaccountSETmoney=money+100WHEREname='A';UPDATEaccountSETmoney=money-100WHEREname='B';COMMIT;注意

如果事务被挂起没有提交，则有很多操作命令在后续执行时会自动提交被挂起的事务，即隐式地结束当前会话中活动的任何事务，就像在之前执行了提交一‍样。10.1.3

回滚事务事务回滚后，数据库中的数据不会发生任何变化。回滚事务使用ROLLBACK命令实现。此处以表10-1所示数据为例讲解ROLLBACK命‍令。（1）首先查看自动提交状态，命令如‍下。

可以看到@@autocommit的值为1，表示自动提交功能开启，这里将自动提交功能关‍闭。idnamemoney1tom10002peter1000表10-1表mytest.test数据SELECT@@autocommit;SETautocommit=0;10.1.3

回滚事务

（2）创建一个实验数据库mytest，在数据库中创建数据表test，在表中插入2条记录，相关代码如‍下。CREATEDATABASEmytest;USEmytestCREATETABLEtest(nameCHAR(8)NOTNULL,moneyINT(10)NOTNULL);INSERTINTOtestVALUES('tom',1000);INSERTINTOtestVALUES('peter',1000);10.1.3

回滚事务

（3）执行数据修改事‍务。

查看目前临时执行结果，命令如‍下。STARTTRANSACTION;UPDATEtestSETmoney=money-100WHEREname='tom';UPDATEtestSETmoney=money+100WHEREname='peter';SELECT*FROMtest;图10-4

事务操作的临时结果10.1.3

回滚事务

（4）使用ROLLBACK命令回滚事‍务。

执行回滚命令后再次查看表数据，发现表数据并没有发生变‍化。ROLLBACK;10.1.4了解事务的隔离级别

事务隔离是数据库处理的基础之一。隔离是事务四大基本特性缩写ACID中的I。数据库事务的隔离级别有4种，由低到高分别为READUNCOMMITTED、READCOMMITTED、REPEATABLEREAD、SERIALIZABLE。①READUNCOMMITTED：所有事务都可以看到其他未提交事务的执行结果，即另一个未提交事务的数据。②READCOMMITTED：一个事务要等另一个事务提交后才能读取数据。因为同一事务的其他实例在该实例处理期间可能会有新的COMMIT，所以同一SELECT可能返回不同结果。③REPEATABLEREAD：这是MySQL默认的事务隔离级别，就是在开始读取数据（事务开启）时，不再允许修改操作。它确保同一事务的多个实例在并发读取数据时会看到同样的数据行，但可能导致幻读。④SERIALIZABLE：最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别可能导致大量的超时现象和锁竞争，效率低下，比较消耗数据库性能。10.1.4了解事务的隔离级别隔离级别脏读不可重复读幻读READUNCOMMITTED可能可能可能READCOMMITTED不可能可能可能REPEATABLEREAD不可能不可能可能SERIALIZABLE不可能不可能不可能

不同隔离级别在并发时可能出现的问题如表所‍示。表10-2不同隔离级别在并发时可能出现的问题10.1.4了解事务的隔离级别

查看当前的事务隔离级别，命令如‍下。也可以使用如下命令。图10-5

查看当前事务级别SELECT@@transaction_isolation;SHOWVARIABLESLIKE'transaction_isolation';10.1.4了解事务的隔离级别

如果需要更改当前的事务隔离级别，则可以使用SETTRANSACTION语句更改单个会话或所有后续连接的隔离级别，其语法格式如‍下。SET[GLOBAL|SESSION]TRANSACTION

TRANSACTION_CHARACTERISTIC[,TRANSACTION_CHARACTERISTIC]...其中：TRANSACTION_CHARACTERISTIC:{ISOLATIONLEVELLevel}Level:{REPEATABLEREAD|READCOMMITTED|READUNCOMMITTED|SERIALIZABLE}注意

不允许在同一SETTRANSACTION语句中指定多个隔离级别子句。在事务开始后也不能更改事务隔离级别，否则会出‍错。10.1.4了解事务的隔离级别

在事务开始后更改事务级别的代码如‍下。图10-6事务开始后更改事务级别的执行结果STARTTRANSACTION;SETTRANSACTIONISOLATIONLEVELSERIALIZABLE;10.1.4了解事务的隔离级别

在语句中使用关键字GLOBAL，表示此语句应用于之后的所有SESSION，而当前已经存在的SESSION不受影响，也就是此语句将应用于当前SESSION内之后的所有事务。如果没有使用GLOBAL，那么此语句将应用于当前SESSION内的下一个还未开始的事‍务。

使用示例如‍下。

要查看更改后的事务隔离级别，可以重新登录后查‍看。SETGLOBALTRANSACTIONISOLATIONLEVELREPEATABLEREAD;存储过程10.210.2.1创建存储过程在MySQL中创建存储过程的语法格式如下。其中，各参数的含义如下。①DEFINER：默认为当前用户②SP_NAME：自定义存储过程名③PROC_PARAMETER：[IN|OUT|INOUT]PARAM_NAMETYPE。默认情况下参数是IN。IN参数表示将值传入过程，过程可能会修改该值。OUT参数将一个值从过程传出给调用方，它的初始值在过程中为空。INOUT参数由调用者初始化，可以由过程修改传出。④ROUTINE_BODY：过程体，由有效的SQL语句组成。⑤CHARACTERISTIC：存储过程的一些特性。CREATE[DEFINER=USER]PROCEDURESP_NAME([PROC_PARAMETER[,...]])[CHARACTERISTIC...]ROUTINE_BODY10.2.1创建存储过程【例10-1】创建存储过程，并使用存储过程统计数据库ssms的表student中的学生人数。USEssms;DELIMITER$$CREATEPROCEDUREstu_num(OUTn1int)BEGINSELECTcount(*)INTOn1FROMstudent;END$$DELIMITER;CALLstu_num(@a);SELECT@a;图10-7使用存储过程统计学生人数10.2.1创建存储过程注意在存储过程的定义中，如果包含多条语句需要使用BEGIN…END结构，则只有单行语句才可以省略。在存储过程中使用多行语句及分号时，必须使用DELIMITER临时重新定义分隔符，以便能够正常完成定义过程。分隔符可以由单个或多个字符组成。应该避免使用反斜杠（\）字符，因为这是MySQL的转义字‍符。10.2.1创建存储过程【例10-2】创建存储过程，并使用存储过程统计数据库ssms表student中总学分大于45的学生人‍数。DELIMITER$$CREATEPROCEDUREstu_num_tc(INtc1int,OUTn1int)BEGINSELECTcount(*)FROMstudentWHERETotal_Credit>tc1;END$$DELIMITER;

CALLstu_num_tc(45,@a);图10-8使用存储过程统计总学分大于45的学生人数10.2.2

使用变量

在存储过程中可以定义和使用变量，这些变量为局部变量，只在BEGIN…END代码块中有效，执行完该代码块，变量就消失了。可以使用DECLARE语句定义局部变量，用DEFAULT语句指明默认值。定义变量的语法格式如‍下。定义变量的语法格式如下。其中VAR_NAME为所定义的变量名称，TYPE为变量类型，DEFAULT子句指明变量的默认值为VALUE，如果省略DEFAULT子句，则变量初始值为NULL。DECLAREVAR_NAME[,VAR_NAME]...TYPE[DEFAULTvalue]10.2.2

使用变量【例10-3】定义变量a和b，并赋予默认值为0。变量的赋值可以使用SET或SELECT…INTO语句来完成。【例10-4】将学生人数赋给变量stu_num。或者注意

DECLARE命令不能单独使用，需要放入存储过程中才能正常使‍用。DECLAREa,bintDEFAULT0;DECLAREstu_numintDEFAULT0SETstu_num=21;SELECTcount(*)INTOstu_numFROMstudent;10.2.3定义条件和处理程序1．定义条件定义条件的语法格式如下。

其中，各参数的含义如‍下。①

CONDITION_NAME：条件的名‍称。②

CONDITION_VALUE：条件的类型，其格式如下。SQLSTATE[VALUE]SQLSTATE_VALUE|MYSQL_ERROR_CODESQLSTATE_VALUE参数和MYSQL_ERROR_CODE参数都可以表示MySQL的错误。常见的ERROR1146（42s02）错误如‍下。SQLSTATE_VALUE值是42s02，MYSQL_ERROR_CODE值是1146，具体内容是数据库ssms中不存在数据表st_table。DECLARECONDITION_NAMECONDITIONFORCONDITION_VALUESQLSTATE[VALUE]SQLSTATE_VALUE

|MYSQL_ERROR_CODEerror1146(42s02):table'ssms.st_table'doesn'texist10.2.3定义条件和处理程序

为错误“ERROR1146（42s02）”定义条件“no_such_table”可以使用以下命‍令。

或者DECLAREno_such_tableconditionFOR1146;DECLAREno_such_tableconditionFORsqlstate'42s02';10.2.3定义条件和处理程序2．定义处理程序定义处理程序的语法格式如下。其中，各参数的含义如下。①HANDLER_ACTION：可以取CONTINUE或EXIT，其中CONTINUE表示继续执行当前程序，EXIT表示执行终止。②CONDITION_VALUE：表示激活处理程序的条件。其值可以为MYSQL_ERROR_CODE、SQLSTATE_VALUE、CONDITION_NAME、SQLWARNING、NOTFOUND、SQLEXCEPTION，不同激活条件值的含义如表10-3所‍示。③STATEMENT：表示一些存储过程或执行语句。DECLAREHANDLER_ACTIONHANDLER

FORCONDITION_VALUE[,CONDITION_VALUE]...

STATEMENT10.2.3定义条件和处理程序激活条件值含义MYSQL_ERROR_CODE错误代码数字SQLSTATE_VALUE包含5个字符的SQLSTATE值CONDITION_NAMEDECLARE定义的条件名称SQLWARNING以“01”开头的SQLSTATE值NOTFOUND以“02”开头的SQLSTATE值SQLEXCEPTION不以“00”、“01”或“02”开头的SQLSTATE值表10-3不同激活条件值的含义10.2.3定义条件和处理程序【例10-5】为调用的表不存在错误时定义一个处理程序。DELIMITER$$CREATEPROCEDUREstu_num_condition(OUTn1int)BEGINDECLAREno_such_tableconditionFOR1146;DECLAREEXITHANDLERFORno_such_tableSET@str='tablenameerror';SELECTcount(*)INTOn1FROMsss;END$$DELIMITER;CALLstu_num_condition(@a);SELECT@str;图10-9表不存在处理结果10.2.4光标（游标）的使用过程光标的使用过程包括声明光标、打开光标、使用光标和关闭光标。1．声明光标声明光标的语法格式如下。

声明一个名为cur_student的光标的语句如下。2．打开光标使用关键字OPEN打开光标，语法格式如下。打开cur_student光标的语句如下。DECLARECURSOR_NAMECURSORFORSELECT_STATEMENTDECLAREcur_studentCURSORFORSELECTname,majorFROMstudent;OPENCURSOR_NAMEOPENcur_student;10.2.4光标（游标）的使用过程3．使用光标使用关键字FETCH来使用光标，其语法格式如下。FETCH语句获取与指定光标（已打开）关联的SELECT语句的下一行，如果数据行存在，则将获取的字段值存储在变量中。SELECT语句检索的列数必须与FETCH语句中指定的输出变量数匹配。如果没有更多的行可用，则会出现SQLSTATE值为“02000”的“无数据”错误。

将光标cur_student查询的一条数据存入变量a和b。FETCH[[NEXT]FROM]CURSOR_NAMEINTOVAR_NAME[,VAR_NAME]...FETCHcur_studentINTOa,b;10.2.4光标（游标）的使用过程4．关闭光标使用关键字CLOSE关闭光标，语法格式如下。关闭cur_student光标使用如下语句。此语句用于关闭以前打开的光标，如果光标未打开，则会发生错误。如果未显式关闭，则光标将在声明它的BEGIN…END语句块结束时关闭。CLOSECURSOR_NAMECLOSEcur_student;10.2.4光标（游标）的使用过程【例10-6】使用光标统计数据表course中所有课程的总学分。DELIMITER$$CREATEPROCEDUREcredits()BEGINDECLAREtotalintDEFAULT0;

DECLAREcreditsCURSORFORSELECTsum(Credit)FROMcourse;OPENcredits;FETCHcreditsINTOtotal;CLOSEcredits;SELECTtotal;END$$DELIMITER;CALLcredits();10.2.5使用流程控制MySQL中的流程控制语句有IF语句、CASE语句、LOOP语句、REPEAT语句、WHILE语句、LEAVE语句和ITERATE语句。1．IF语句IF语句用来进行条件判断，其语法格式如下。IF语句可以有THEN、ELSE和ELSEIF子句，并以ENDIF结尾。IFSEARCH_CONDITIONTHENSTATEMENT_LIST[ELSEIFSEARCH_CONDITIONTHENSTATEMENT_LIST]...[ELSESTATEMENT_LIST]ENDIF10.2.5使用流程控制【例10-7】判断数字正、负和零，分别返回1、-1和0。DELIMITER$$CREATEPROCEDUREpm(nINT)BEGINDECLAREsint;IFn>0THENSETs=1;ELSEIFn=0THENSETs=0;ELSESETs=-1;ENDIF;SELECTs;END$$DELIMITER;CALLpm(-6);10.2.5使用流程控制2.CASE语句CASE语句也是用来进行条件判断的，其语法结构如下。CASE语句还有另外一种语法结构。CASECASE_VALUEWHENWHEN_VALUETHENSTATEMENT_LIST[WHENWHEN_VALUETHENSTATEMENT_LIST]...[ELSESTATEMENT_LIST]ENDCASECASEWHENSEARCH_CONDITIONTHENSTATEMENT_LIST[WHENSEARCH_CONDITIONTHENSTATEMENT_LIST]...[ELSESTATEMENT_LIST]ENDCASE10.2.5使用流程控制【例10-8】创建存储过程，使用存储过程按照参数值不同输出其结果字符串。DELIMITER$$CREATEPROCEDUREpm1(innint)BEGINCASEnWHEN0THENSELECT'zero';WHEN1THENSELECT'one';ELSESELECT'no';ENDCASE;END$$DELIMITER;CALLpm1(0);10.2.5使用流程控制3．LOOP语句LOOP语句可以实现一个简单的循环构造，通常使用LEAVE语句来退出循环，否则会导致死循环。LOOP语句的语法格式如下。[BEGIN_LABEL:]LOOP

STATEMENT_LISTENDLOOP[END_LABEL]DELIMITER$$CREATEPROCEDUREsum100()BEGINDECLAREsumintDEFAULT0;DECLAREnintDEFAULT0;LABEL1:LOOPSETn=n+1;IFn>100THENLEAVELABEL1;ENDIF;SETsum=sum+n;ENDLOOPLABEL1;SELECTsum;END$$DELIMITER;CALLsum100;10.2.5使用流程控制【例10-9】使用LOOP语句求100以内整数的和。10.2.5使用流程控制4．REPEAT语句REPEAT语句为条件循环语句，执行循环直到条件表达式为TRUE。因此REPEAT语句至少会进入一次循环。REPEAT语句的语法格式如下。[BEGIN_LABEL:]REPEATSTATEMENT_LISTUNTILSEARCH_CONDITIONENDREPEAT[END_LABEL]DELIMITER$$CREATEPROCEDUREdorepeat()BEGINSET@x=1;REPEATSET@x=@x*2;UNTIL@x>100ENDREPEAT;END$$DELIMITER;CALLdorepeat();SELECT@x;10.2.5使用流程控制【例10-10】变量的初始值为1，使用REPEAT语句循环乘2直到积大于100。10.2.5使用流程控制5．WHILE语句WHILE语句是有条件的循环语句，其语法格式如下。WHILE循环判断的条件在前，只要循环条件表达式的值为TRUE，WHILE语句中的语句列表STATEMENT_LIST就会重复执行。[BEGIN_LABEL:]WHILESEARCH_CONDITIONDOSTATEMENT_LISTENDWHILE[END_LABEL]10.2.5使用流程控制6．LEAVE语句LEAVE语句用于跳出当前存储过程，不执行存储过程中剩余的代码。其基本语法格式如下。7．ITERATE语句ITERATE语句在循环语句中使用，用于跳出本次循环，进入下一次循环。其基本语法格式如下。LEAVE语句和ITERATE语句都用于跳出循环，LEAVE语句是跳出整个循环，执行循环之后的程序；而ITERATE语句则是仅跳出本次循环，然后进入下一次循环。IEAVELABELITERATELABEL使用存储过程10.310.3.1调用存储过程在MySQL中使用CALL语句调用存储过程，其语法格式如下。

通过语法格式可以看出，可以调用不带参数的存储过程。其中，SP_NAME为存储过程名，PARAMETER为存储过程的参‍数。CALLSP_NAME([PARAMETER[,...]])CALLSP_NAME[()]10.3.1调用存储过程【例10-11】存储过程的调用示例。DELIMITER$$CREATEPROCEDUREp(OUTver_pvarchar(25),INOUTincr_pint)BEGINSELECTversion()INTOver_p;SETincr_p=incr_p+1;END$$DELIMITER;SET@n=10;CALLp(@version,@n);SELECT@version,@n;10.3.2查看存储过程1．查看存储过程的创建语句

要查看存储过程的创建语句，可以使用SHOW命令，其语法格式如‍下。【例10-12】查看存储过程p的创建信息。SHOWCREATEPROCEDUREPROC_NAMESHOWCREATEPROCEDUREp\G;10.3.2查看存储过程2.查看存储过程状态

要查看存储过程的状态，同样可以使用SHOW命令，其语法格式如‍下。【例10-13】查看存储过程p的状态。SHOWPROCEDURESTATUS[LIKE'PATTERN'|WHEREEXPR]SHOWPROCEDURESTATUSLIKE'p'\G;10.3.2查看存储过程【例10-14】查看数据库ssms中所有存储过程的信‍息。SHOWPROCEDURESTATUSWHEREdb='ssms'\G10.3.3修改存储过程修改存储过程使用ALTERPROCEDURE语句，其语法格式如下。其中CHARACTERISTIC的语法格式如下。

ALTERPROCEDUREPROC_NAME[CHARACTERISTIC...]COMMENT'STRING'|LANGUAGESQL|{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}10.3.3修改存储过程【例10-15】修改存储过程p，将描述信息COMMENT修改为“myprocedure”。ALTERPROCEDUREpCOMMENT'myprocedure';SHOWPROCEDURESTATUSLIKE'p'\G;

10.3.4删除存储过程删除存储过程可以通过DROP语句实现，其语法结构如下。【例10-16】删除存储过程p。DROPPROCEDURE[IFEXISTS]SP_NAMEDROPPROCEDUREp;DROPPROCEDUREIFEXISTSp;SHOWWARNINGS;【知识拓展】1．如何进行函数的创建和使用？

函数与存储过程的定义、使用方法类似，函数与存储过程最大的区别就是函数调用有返回值，调用存储过程用CALL语句，而调用函数直接引用函数名和参数即可。IN、OUT、INOUT3个参数前的关键词只适用于存储过‍程。

定义函数的语法格式如‍下。

其中，各参数的含义如‍下。SP_NAME表示函数的名称，RETURNS子句中的TYPE表示函数返回值的类型。程序体中使用RETURN子句指明返回值。其他参数和存储过程相同，在此不再重复说‍明。CREATE[DEFINER=USER]FUNCTIONSP_NAME([FUNC_PARAMETER[,...]])RETURNSTYPE[CHARACTERISTIC...]ROUTINE_BODY【知识拓展】【例10-17】定义函数hello()，输出连接后的字符串。CREATEFUNCTIONhello(sCHAR(20))RETURNSchar(50)DETER