MySQL实用教程(新体系.综合应用实例视频)第4版第9章 过程式对象程序设计

第9章

过程式对象程序设计——过程体MySQL+实用教程（第4版）在MySQL8中，有一些过程式对象，包括存储过程、存储函数、触发器和事件，它们都包含过程体代码，作为一个相对独立的单元来执行某个特定的功能。例如，一个存储过程的代码结构如下：CREATEPROCEDURE过程名()BEGIN

过程体END;说明：（1）过程体：具体内容就是SQL语句，可以包含空、一条或多条普通的SQL语句，也可以是由BEGIN...END括起的复合语句，其中由空、一个或多个语句通过流程控制语句组织起来，这就构成了MySQL程序。（2）要执行这段MySQL程序，通过调用CALL语句：CALL过程名();01局部变量定义局部变量定义在过程体中可以使用DECLARE语句声明局部变量，用来存储临时的结果。它仅允许出现在BEGIN...END语句内部，且必须在所有其他语句之前。DECLARE

变量名,...类型[DEFAULT值]说明：（1）变量名不区分大小写，允许的字符和引用规则与其他标识符相同。可包含DEFAULT子句提供变量初始的默认值，否则初始值为NULL。值可以指定为表达式，不一定是常数。（2）不同类型的局部变量，必须用不同的DECLARE语句分别声明。（3）局部变量的作用范围是其所在的BEGIN...END块，也可以在声明块内嵌套的块中引用该变量，但那些声明了同名变量的块除外。（4）加@@打头是系统变量，其作用范围为服务器的所有会话。用户变量名前需要加@，用户变量作用范围为用户的整个会话当中，过程体可以使用用户变量。（5）局部变量可以通过SET语句赋值和SELECT语句显示。SET变量名=值,...SELECT变量名,...局部变量定义【例9.1】在一个存储过程proc_circle中声明局部变量，分别计算显示圆的周长和面积。1）定义存储过程USEmydb;DROPPROCEDUREIFEXISTSproc_circle;CREATEPROCEDUREproc_circle()BEGIN

DECLARElen,areafloat(6.2)DEFAULT0.00;

SETlen=2*PI()*@r; #（a）

SETarea=PI()*@r*@r;

SELECT@rAS'半径',FORMAT(len,2)AS'周长',FORMAT(area,2)AS'面积'; #（b）END;说明：（a）@r是用户定义变量，存放圆的半径带入存储过程中，但在存储过程中定义的局部变量len和area则不能带出proc_circle存储过程。（b）PI()=3.14159…，FORMAT(len,2)和FORMAT(area,2)表示显示为2位小数。局部变量定义存储过程创建后，在Navicat中树状视图对应数据库下的“函数”节点下就出现了“proc_circle”项，如图9.1所示。

2）执行存储过程，结果如图9.2所示。SET@r=6;CALLproc_circle();02条件分支1.根据条件控制：IF语句2.根据表达式值或者条件控制：CASE语句条件分支1.根据条件控制：IF语句根据不同的条件执行不同的操作：IF条件1THEN

语句序列1[ELSEIF条件2THEN

语句序列2]...[ELSE

语句序列0]ENDIF说明：（1）当某条件成立时，执行对应THEN后的语句序列，如果没有匹配的条件则执行最后ELSE后的语句序列0。每个语句序列中又可以包含一个或多个SQL语句。（2）IF语句可以嵌套。条件分支【例9.2】采用IF根据分数判断对应等级。DROPPROCEDUREIFEXISTSproc_grade;CREATEPROCEDUREproc_grade()BEGIN DECLAREcscorevarchar(3);

IF@nscore>=90THEN SETcscore='优秀';

ELSEIF@nscore>=80THEN SETcscore='良好';

ELSEIF@nscore>=70THEN SETcscore='中等';

ELSEIF@nscore>=60THEN SETcscore='及格';

ELSE SETcscore='不及格';

ENDIF; SELECT@nscore,cscore;END;SET@nscore=85;CALLproc_grade();运行结果如图9.3所示。条件分支2.根据表达式值或者条件控制：CASE语句SELECT语句选择输出项用到了CASE函数，这里介绍CASE语句，前者根据表达式值控制返回不同的表达式进行计算，这里根据表达式值控制执行不同的语句序列。CASE语句有两种格式。格式一：CASE表达式 WHEN值1THEN语句序列1 [WHEN值2THEN语句序列2] ... [ELSE语句序列0]ENDCASE格式二：CASE WHEN条件1THEN语句序列1 [WHEN条件2THEN语句序列2] ... [ELSE语句序列0]ENDCASE条件分支说明：（1）一个CASE语句的作用等效于一个多分支的IF语句，但在判断条件情况很多时比后者在结构上更加清晰。（2）第一种格式中，根据表达式的值匹配一系列的WHEN-THEN块，匹配成功时，就执行对应THEN后的语句序列。如果没有匹配的块，则执行ELSE块指定的语句序列0。（3）第二种格式中，CASE关键字后面没有参数，程序流程直接依次进入各个WHEN-THEN块，一旦WHEN后的条件成立，就执行THEN后面的语句序列。与第一种格式相比，这种格式能够实现更为复杂的条件判断，使用起来更为灵活。（4）CASE语句之间、CASE语句和IF语句之间可以嵌套。条件分支【例9.2续】采用CASE根据分数判断对应等级。DROPPROCEDUREIFEXISTSproc_grade;CREATEPROCEDUREproc_grade()BEGIN DECLAREcscorevarchar(3);

CASEFLOOR(@nscore/10)

WHEN10THEN SETcscore='优秀';

WHEN9THEN SETcscore='优秀';

WHEN8THEN SETcscore='良好';

WHEN7THEN SETcscore='中等';

WHEN6THEN SETcscore='及格';

ELSE SETcscore='不及格';

ENDCASE; SELECT@nscore,cscore;END;

SET@nscore=85;CALLproc_grade();条件分支若采用第二种格式，程序修改如下：BEGIN DECLAREcscorevarchar(3); DECLAREnint(2); SETn=FLOOR(@nscore/10); CASE WHENn=10THEN SETcscore='优秀'; ... ENDCASE; SELECT@nscore,cscore;END;03循环执行1.先判断条件再执行语句序列：WHILE语句2.先执行语句序列再判断条件：REPEAT语句3.通过语句体控制循环结束：LOOP和LEAVE语句4.跳转到循环开始：LOOP和ITERATE语句循环执行1.先判断条件再执行语句序列：WHILE语句WHILE条件DO

语句序列ENDWHILE说明：只要条件为真，则执行语句序列中的语句，直到条件为假则结束循环。【例9.3】采用WHILE计算1+2+3+…+n。DROPPROCEDUREIFEXISTSproc_nsum;CREATEPROCEDUREproc_nsum()BEGIN DECLAREn,sintDEFAULT0; WHILEn<=@nDO SETs=s+n; SETn=n+1; ENDWHILE; SELECT'1+2+3+…+',@n,'=',s;END;SET@n=50;CALLproc_nsum();其中，n是局部变量，@n是用户定义变量。运行结果如图9.4所示。循环执行2.先执行语句序列再判断条件：REPEAT语句REPEAT

语句序列UNTIL条件ENDREPEAT说明：先执行语句序列中的语句，然后判断条件，如果条件为真则停止循环，为假则继续循环。【例9.3续】采用REPEAT语句计算1+2+3+…+n。将本例的BEGIN…END替换为：BEGIN DECLAREn,sintDEFAULT0;

REPEAT SETn=n+1; SETs=s+n;

UNTILn>=@nENDREPEAT; SELECT'1+2+3+…+',@n,'=',s;END;运行结果同图9.4。循环执行3.通过语句体控制循环结束：LOOP和LEAVE语句[标签:]LOOP

语句序列 LEAVE标签 ...ENDLOOP[标签]说明：语句序列是需要重复执行的语句。执行到“LEAVE标签”语句终止循环，跳转到ENDLOOP[标签]语句的下一个语句。【例9.3续】采用LOOP语句计算1+2+3+…+n。将本例的BEGIN…END替换为：BEGIN DECLAREn,sintDEFAULT0; SETn=0;

mylabel:LOOP SETs=s+n; IFn>=@nTHEN

LEAVE

mylabel; ENDIF; SETn=n+1;

ENDLOOPmylabel; SELECT'1+2+3+…+',@n,'=',s;END;循环执行4.跳转到循环开始：LOOP和ITERATE语句在WHILE、REPEAT或LOOP循环体内执行到ITERATE语句，就跳转到循环开始继续执行。ITERATE标签说明：ITERATE语句与LEAVE区别在于，LEAVE是离开一个循环，而ITERATE语句则是重新开始一个循环。【例9.3续】采用ITERATE语句计算1+2+3+…+n。将本例的BEGIN…END替换为：BEGIN DECLAREn,sintDEFAULT0; SETn=0;

mylabel:LOOP SETs=s+n; SETn=n+1; IFn<=@nTHEN

ITERATE

mylabel; ENDIF; LEAVEmylabel; ENDLOOPmylabel; SELECT'1+2+3+…+',@n,'=',s;END;第9章

过程式对象程序设计——出错处理及实例01根据错误自动处理根据错误自动处理每一个错误消息都有一个唯一错误代码和一个SQLSTATE代码，例如，SQLSTATE23000属于如下的出错代码：Error1022,"Can'twrite;duplicatekeyintable"Error1048,"Columncannotbenull"Error1052,"Columnisambiguous"Error1062,"Duplicateentryforkey"MySQL手册的“错误消息和代码”中列出了所有可能的出错消息及它们各自的代码。错误处理定义包含条件名称定义和条件处理定义。1）条件名称定义DECLARE条件名称CONDITIONFOR条件值其中：条件值：ERROR=MySQL错误代码|SQLSTATE[VALUE]sql状态值条件名称定义不是必须的。根据错误自动处理2）条件处理定义DECLARE处理动作HANDLERFOR条件值,...

处理语句过程体处理动作：CONTINUE|EXIT|UNDOCONTINUE不中断程序的处理；EXIT对当前BEGIN...END复合语句的执行被终止；UNDO撤销之前的操作，但目前MySQL暂不支持撤销操作。条件值：错误代码|SQLSTATE[VALUE]sql状态值|条件名称|SQLWARNING|NOTFOUND|SQLEXCEPTION根据错误自动处理说明：（1）错误代码：是MySQL错误代码，一般为4位数字，例如，1146表示数据库表不存在。（2）sql状态值：包含5个字符的字符串错误值，例如，'42S02'表示数据库表不存在。（3）条件名称：如果此前定义了条件名称，这里就可以引用。定义的条件值就是条件名称定义内容。（4）SQLWARNING：所有以01开头的SQLSTATE代码值。（5）NOTFOUND：所有以02开头的SQLSTATE代码值。（6）SQLEXCEPTION：所有没有被SQLWARNING或NOTFOUND捕获的SQLSTATE代码值。【例9.4】数据库中不存在的表错误处理程序测试。（1）在MySQL命令行窗口操作，系统会显示出错信息，如图9.5所示。根据错误自动处理（2）在Navicat创建存储过程执行查询操作，不加控制时显示出错信息如图9.6所示。USEmydb;DROPPROCEDUREIFEXISTSproc_test;CREATEPROCEDUREPROC_test()BEGIN DESCmytab2;END;

SET@ERR=0;CALLproc_test();SELECT@ERRAS'CALL后';根据错误自动处理（3）若在存储过程中控制该出错信息，仅仅用一个语句对其进行处理，显示结果如图9.7所示。USEmydb;DROPPROCEDUREIFEXISTSproc_test;CREATEPROCEDUREPROC_test()BEGIN DECLARECONTINUEHANDLERFOR1146 SET@ERR=1; DESCmytab2;END;

SET@ERR=0;CALLproc_test();SELECT@ERRAS'CALL后';根据错误自动处理（4）若在存储过程中控制该出错信息，使用多个语句进行处理，显示结果如图9.8所示。USEmydb;DROPPROCEDUREIFEXISTSproc_test;CREATEPROCEDUREPROC_test()BEGIN DECLARECONTINUEHANDLERFORSQLSTATE'42S02' BEGIN SET@ERR=1; SELECT'文件不存在！'AS出错信息; #（a） END; SET@ERR=2; DESCmytab2; SELECT@ERRAS'DESC后'; #（b） SET@ERR=3;END;

SET@ERR=0;CALLproc_test();SELECT@ERRAS'CALL后'; #（c）

根据错误自动处理（5）修改第（4）步代码中的语句，将CONTINUE改成EXIT：DECLAREEXITHANDLERFORSQLSTATE'42S02'运行结果变成如图9.9所示。

（6）包含定义条件名称，修改成下列语句，显示完全相同。DECLAREnot_exist_tableCONDITIONFORSQLSTATE'42S02';DECLAREEXITHANDLERFORnot_exist_table根据错误自动处理【例9.5】向commodity表副本commodity_temp中插入一行重复记录：（'1A0101','洛川红富士5斤箱装',22.40,9000）已知编号1A0101在commodity表中已存在。如果出现错误，进行出错处理后程序继续执行。USEemarket;DROPTABLEIFEXISTScommodity_temp;CREATETABLEcommodity_tempAS (SELECT商品编号,商品名称,价格,库存量FROMcommodity);DROPPROCEDUREIFEXISTSproc_test;CREATEPROCEDUREproc_test()BEGIN DECLAREtagint(1); DECLARECONTINUEHANDLERFORsqlstate'23000' SELECT'插入的记录主键项重复'AS提示信息; INSERTINTOcommodity_temp VALUES('1A0101','洛川红富士5斤箱装',22.40,9000);END;

CALLproc_test();根据错误自动处理说明：（1）因为commodity_temp中没有复制commodity表的键（包括主键），所以即使已经存在商品编号为'1A0101'的记录，仍可插入重复记录。（2）在CREATEPROCEDURE语句前添加“商品编号”作为主键语句：ALTERTABLEcommodity_temp ADDPRIMARYKEY(商品编号);（3）重新执行CALLproc_test();显示结果如图9.10所示。02根据情况抛出信号根据情况抛出信号1）SIGNAL语句SIGNAL返回错误，向处理程序、应用程序或客户端提供错误信息，还可以控制错误的特征（错误号，SQLSTATE值，消息）。如果没有SIGNAL，则必须采用诸如故意引用不存在的表之类的解决方法来导致例程返回错误。SIGNAL条件值:[SET信息项,...]条件值：ERROR=MySQL错误代码|SQLSTATE[VALUE]sql状态值信息项：信息项名=值信息项名：CLASS_ORIGIN|SUBCLASS_ORIGIN|MESSAGE_TEXT|MYSQL_ERRNO|CONSTRAINT_CATALOG|CONSTRAINT_SCHEMA|CONSTRAINT_NAME|CATALOG_NAME|SCHEMA_NAME|TABLE_NAME|COLUMN_NAME|CURSOR_NAME根据情况抛出信号【例9.6】当前mydb数据库的mytab有3条记录，抛出信号测试。USEmydb;DROPPROCEDUREIFEXISTSproc_test;CREATEPROCEDUREproc_test()BEGIN DECLAREnint; DECLAREerrmsgvarchar(255); #（a） DECLAREmytjCONDITIONFORSQLSTATE'EX001'; #（b） DECLAREEXITHANDLERFORSQLSTATE'EX001' #（c） BEGIN SIGNALmytjSETMESSAGE_TEXT=errmsg; END; SELECTCOUNT(*)FROMmytabINTOn; IFn>0THEN SETerrmsg='mytab表不空'; SIGNALmytjSETMESSAGE_TEXT=errmsg; #（d） ENDIF;END;

CALLproc_test();根据情况抛出信号显示结果如图9.11所示，SHOWWARNINGS语句可以显示出错信息，包括Level、Code和Message。说明：（a）定义扩展错误存放信息字符串。（b）定义扩展错误条件名称。（c）定义扩展错误处理语句。（d）向客户端发送错误信息字符串信号。根据情况抛出信号2）RESIGNAL语句同样的，RESIGNAL也可以进行异常处理并返回错误信息。RESIGNAL[条件值][SET信号项,...]在使用SIGNAL方法时必须指定条件值，要先定义异常处理，可以在过程体中的任何位置使用SIGNAL语句，而RESIGNAL可以省略所有属性，甚至可以省略SQLSTATE值，但必须在错误或警告处理程序中使用RESIGNAL语句，否则将收到一条错误消息，指出“RESIGNALwhenhandlerisnotactive”。如果单独使用RESIGNAL语句，则所有属性与传递给条件处理程序的属性必须相同。例如，当没有操作表，向应用程序或者客户端输出提示信息。DECLAREEXITHANDLERFORSQLSTATE'42S02'RESIGNALSETMESSAGE_TEXT='没有操作表！';根据情况抛出信号3）GETDIAGNOSTICS语句获取错误缓冲区的内容，然后把这些内容输出到不同范围域的变量里，以便后续灵活操作处理。GET[CURRENT|STACKED]DIAGNOSTICS{

语句信息项,... |CONDITIONcondition_number

条件信息项,...[,condition_information_item]...}语句信息项：变量=NUMBER|ROW_COUNT条件信息项：变量=条件信息项名SQL语句执行情况信息捕获反馈，包括NUMBER、ROW_COUNT。根据情况抛出信号条件信息项名捕获异常情况信息，当条件发生，可以通过它去接收条件项目信息，但也不是所有的信息项MySQL都会进行填充赋值，也会出现空值。条件信息项名同SIGNAL语句信息项名。例如，GETDIAGNOSTICS语句测试。USEmydb;DELETEFROMmytab;GETDIAGNOSTICS@m1=NUMBER,@m2=ROW_COUNT;SELECT@m1,@m2;显示结果如图9.12所示。第9章

过程式对象程序设计——事务管理事务管理例如：购买商品编号='1A0101'商品6件，如果商品表（commodity）库存量满足，那么需要对表进行下列操作：（1）商品表（commodity）商品编号='1A0101'商品的库存量-6；（2）订单表（orders）中包含一个订单，如果不存在，需要插入订单记录，填入用户帐户名、订单号和该6件商品的“支付金额”；如果已经存在，则需要将6件商品的金额累加到“支付金额”中。（3）订单项表（orderitems）中增加一条记录，填入订单编号、商品编号、订货数量等。这些操作是不可分割的，假如进行（1）商品表（commodity）减了库存量6后系统出现问题无法进行（2）和（3）步，系统中商品编号='1A0101'的6件商品就不知去向，数据库数据就不一致了。同样用户退订商品也需要多个操作一起完成。事务管理事务归纳起来有如下四个重要特性（简称ACID）。（1）原子性（Atomicity）。如果一个事务由两个或者多个SQL语句组成，其中的SQL语句必须同时成功才能认为整个事务是成功的，否则系统将会返回到该事务以前的状态。例如：没有用户表帐户记录、商品记录和订单记录就不可能创建订单项记录。（2）一致性（Consistency）在整个事务的生命周期里，查询到的数据是一致的。MVCC多版本并发控制利用UNDO保存某一时刻数据快照，通过版本号来减少锁的争用，保证各个事务互不影响。（3）隔离性（Isolation）隔离性是指每个事务在它自己的发生空间，和其他发生在系统中的事务相隔离。当系统支持多个同时存在的用户和连接时，这尤为重要。（4）持久性（Durability）。只要事务提交了，这个事务就不会因为系统的崩溃而丢失。大多数RDBMS产品通过保存所有行为的日志来保证数据的持久性，这些行为是指在数据库中以任何方法更改数据，数据库日志记录了所有对于表的更新、查询、报表等。01事务处理1．关闭自动提交2．开始事务3．结束事务4．撤销事务5．回滚事务到指定点事务处理常见事务格式：DECLAREEXITHANDLERFORSQLEXCEPTIONROLLBACK;STARTTRANSACTION;DML(INSERT...;DELETE...;UPDATE...;)COMMIT;1．关闭自动提交在MySQL中，当一个会话开始时，系统变量AUTOCOMMIT值为1，即自动提交功能是打开的，当用户每执行一条SQL语句后，该语句对数据库的修改就立即被提交成为持久性修改保存到磁盘上，一个事务也就结束了。因此，用户必须关闭自动提交，才能由多条SQL语句组成事务，使用如下语句：SET@@AUTOCOMMIT=0;执行此语句后，必须明确地指示每个事务的终止，事务中的SQL语句对数据库所做的修改才能成为持久化修改。事务处理【例9.7】使用自动提交。（1）运行下列语句：SET@@AUTOCOMMIT=1;INSERTINTOcommodity_tempVALUES('3BA302','澳洲鲍鱼',248.00,50);（2）用Navicat工具打开commodity_temp表，看到增加了'3BA302'记录。（3）运行语句：DELETEFROMcommodity_tempWHERE商品编号='3BA302';再在Navicat中查看commodity_temp表，看不到'3BA302'记录了。【例9.7续】不使用自动提交。运行下列语句：SET@@AUTOCOMMIT=0;INSERTINTOcommodity_tempVALUES('3BA302','澳洲鲍鱼',248.00,50);用Navicat工具打开commodity_temp表，依旧看不到'3BA302'记录，这是因为这个修改并没有被持久化，因为自动提交已经关闭了。用户可以通过ROLLBACK撤销这一修改，或者使用COMMIT语句持久化这一修改。事务处理2．开始事务当一个应用程序的第一条SQL语句或者在COMMIT或ROLLBACK语句（后面介绍）后的第一条SQL执行后，一个新的事务也就开始了。可以使用一条STARTTRANSACTION语句来显式地声明一个事务：STARTTRANSACTION SQL语句也可以用一条BEGINWORK语句替代STARTTRANSACTION语句，但是STARTTRANSACTION更常用些。3．结束事务用COMMIT语句结束一个事务，它是提交语句，使得自从事务开始以来所执行的所有数据修改成为数据库的永久部分，也标志一个事务的结束。COMMIT[WORK][AND[NO]CHAIN][[NO]RELEASE]说明：（1）ANDCHAIN子句会在当前事务结束时立刻启动一个新事务，并且新事务与刚结束的事务有相同的隔离级别。（2）RELEASE子句在终止了当前事务后，会让服务器断开与当前客户端的连接。包含NO关键字可以抑制CHAIN或RELEASE完成。事务处理下面的这些SQL语句运行时都会隐式地执行一个COMMIT操作：DROPDATABASE/DROPTABLECREATEINDEX/DROPINDEXALTERTABLE/RENAMETABLELOCKTABLES/UNLOCKTABLESSETAUTOCOMMIT=1【例9.7续】事务提交演示。SET@@AUTOCOMMIT=0;COMMIT;此时，在Navicat中查看commodity_temp表，可看到'3BA302'记录。事务处理4．撤销事务ROLLBACK语句是撤销语句，它撤销事务所做的修改，并结束当前这个事务。ROLLBACK[WORK][AND[NO]CHAIN][[NO]RELEASE]例如，执行语句：SET@@AUTOCOMMIT=0;DELETEFROMcommodity_tempWHERE商品编号='3BA302';ROLLBACKWORK;执行完ROLLBACK语句后，前面的删除动作将被撤销，读者打开commodity_temp表查看，会发现编号“'3BA302'”的商品记录还在里面。事务处理5．回滚事务到指定点除了撤销整个事务，用户还可以使用ROLLBACKTO语句使事务回滚到事务中的某个点。当然首先需要在事务中使用SAVEPOINT语句来设置一个保存点：SAVEPOINT保存点名ROLLBACKTOSAVEPOINT语句会向已命名的保存点回滚一个事务。如果在保存点被设置后，当前事务对数据进行了更改，则这些更改会在回滚中被撤销，语法格式为：ROLLBACK[WORK]TOSAVEPOINT保存点名当事务回滚到某个保存点后，在该保存点之后设置的保存点将被删除。RELEASESAVEPOINT语句会从当前事务的一组保存点中删除已命名的保存点而不出现提交或回滚。如果保存点不存在，会出现错误：RELEASESAVEPOINT保存点名事务处理【例9.7续】回滚事务到指定的保存点。SET@@AUTOCOMMIT=0;STARTTRANSACTION;UPDATEcommodity_tempSET库存量=库存量-1WHERE商品编号='3BA302';SAVEPOINTp1;UPDATEcommodity_tempSET库存量=库存量-2WHERE商品编号='3BA302';SAVEPOINTp2;DELETEFROMcommodity_tempWHERE商品编号='3BA302';ROLLBACKTOSAVEPOINTp1; #（a）UPDATEcommodity_tempSET库存量=库存量-4WHERE商品编号='3BA302';COMMITWORK; #（b）在Navicat中查看commodity_temp表，看到'3BA302'记录如图9.13所示。说明：（a）ROLLBACKTOSAVEPOINTp1回滚到p1点，使p1点到此处的语句没有执行。（b）确认回滚以外的所有修改有效。02事务隔离级1.事务隔离级说明2.事务隔离级的查询和设置事务隔离级1.事务隔离级说明只有支持事务的存储引擎才可以定义一个隔离级。定义隔离级可以使用下列语句：SET[GLOBAL|SESSION]TRANSACTIONISOLATIONLEVEL SERIALIZABLE //可序列化 |REPEATABLEREAD //可重复读 |READCOMMITTED //提交读 |READUNCOMMITTED //未提交读说明：如果指定GLOBAL，那么定义的隔离级将适用于所有的SQL用户；如果指定SESSION，则隔离级只适用于当前运行的会话和连接。事务隔离级基于ANSI/ISOSQL规范，MySQL提供了4种隔离级：序列化、可重复读、提交读和未提交读。下面简单介绍这4种隔离级的含义。（1）可序列化（SERIALIZABLE）对于同一个数据来说，在同一个时间段内，只能有一个会话可以访问，包括SELECT和DML，这样可以避免幻读问题。也就是说，对于同一（行）记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。（2）可重复读（REPEATABLEREAD）当前在执行事务的变化仍然不能看到，也就是说，如果用户在同一个事务中执行同条SELECT语句数次，结果总是相同的。否则，虽然在事务提交确认前事务对表修改看不到，但SELECT语句却可以查询到它的变化。（3）提交读（READCOMMITTED）不仅处于这一级的事务可以看到其他事务添加的新记录，而且其他事务对现存记录做出的修改一旦被提交，也可以看到。这意味着在事务处理期间，如果其他事务修改了相应的表，那么同一个事务的多个SELECT语句可能返回不同的结果（幻读）。（4）未提交读（READUNCOMMITTED）提供了事务之间最小限度的隔离。除了容易产生虚幻的读操作和不能重复的读操作外，处于这个隔离级的事务可以读到其他事务还没有提交的数据（脏读），如果这个事务使用其他事务不提交的变化作为计算的基础，然后那些未提交的变化被它们的父事务撤销，这就导致了大量的数据变化。事务隔离级2.事务隔离级的查询和设置MySQL8.0默认为可重复读（REPEATABLEREAD）隔离级，其系统变量TRANSACTION_ISOLATION中存储了事务的隔离级，可以使用SELECT随时获得当前隔离级的值：SELECT@@TRANSACTION_ISOLATIONAS当前事务隔离级;默认情况下，这个系统变量的值是基于每个会话设置的，但是可以通过向SET命令行添加GLOBAL关键字修改该全局系统变量的值。当用户从无保护的READUNCOMMITTED隔离级转移到更安全的SERIALIZABLE级时，RDBMS的性能也要受到影响。原因很简单：用户要求系统提供越强的数据完整性，它就越需要做更多的工作，运行的速度也就越慢。因此，需要在隔离性需求和性能之间协调。事务隔离级没有一个标准公式来决定哪个隔离级适用于哪一个应用程序，大多数情况下，这是个主观的决定，它是基于应用程序的容错能力和应用程序开发者对于潜在数据错误的影响的判断。即使是在同一个应用程序中，对于不同的事务，隔离级的选择也可以不同，例如，同一个程序中的不同事务基于执行的任务需要不同的隔离级。图9.14中给出了事务隔离级和性能之间的关系曲线。03事务应用实例事务应用实例【例9.8】购物过程：如果指定商品库存量满足数量要求，商品表（commodity）库存量减购物数量，订单表（orders）加订单编号、帐户名和订货数量记录，订单项表（orderitems）加入订单编号、订货数量、下单时间记录。1）创建一个存储过程shopping。说明：（a）局部变量及其应用：定义局部变量存储查询到的指定商品的记录数（n）和价格（price）（a.1）；查询指定商品的记录数和价格保存到定义的局部变量n和price中；判断是否找到对应的记录（a.3）；获得的价格与数量相乘得到金额（a.4）。（b）事务处理：定义SQL事务出错局部变量（transErr），初始值为0（b.1）；定义SQL事务出错处理方法（继续运行），并且运行“SETtransErr=1”语句（b.2）；开始事务（b.3）；如果SQL事务出错，回滚到事务前状态（b.4）；否则事务提交确认（b.5）。（c）购买商品：商品表库存量减购买数量（c.1）；订单表（orders）增加记录（c.2）；订单项表（orderitems）增加记录（c.3）。（d）事务处理程序（SET@@AUTOCOMMIT=0）不能包含“DELIMITER$$”，而结束需要采用“END;”，否则无法运行。事务应用实例（e）调用存储过程shopping。操作结果显示如图9.15所示。打开commodity、orders和orderitems表，查看有关记录结果如图9.16所示。

（f）再次调用存储过程shopping，由于101订单已经存在，操作结果显示如图9.17所示。事务应用实例2）该购买商品存储过程并不完备，实际一个订单可能包含若干个商品，订单编号（自增属性）可在订单记录生成时自动生成，等等。另外，需要设置SET@@sql_mode=''，否则用户定义变量不能在事务中应用。3）事务功能测试。运行上述程序后，再人为制造事务无法正常完成的环境，例如：如果MySQL在非本机上的服务器，可以在上述（c.1）、（c.2）、（c.3）之间插入一个调用延时程序，在程序运行时，在延时程序时间内临时断开网络。或者重新运行，因为orders表已经存在订单编号=101的记录，所以在操作此表之前的修改commodity库存量的操作也会恢复。如果MySQL在本机上，可以临时将orderitems表改名，SET@oid=102，虽然该订单编号可以操作，但由于事务保证，三个表应该是没有变化的。4）可修改上面SQL语句，实现同样功能。DECLAREEXITHANDLERFORSQLEXCEPTIONROLLBACK; #（b.2）同时将（b.4）、（b.5）变成：COMMIT;第9章

过程式对象程序设计——游

标游标1.游标操作使用游标须遵循如下4个基本的步骤。（1）声明游标：DECLARECURSOR语句要使用游标，首先要声明它：DECLARE游标名CURSOR FOR SELECT语句说明：游标名：使用与表名同样的命名规则。一个存储过程可以声明多个游标，但是一个块中的每一个游标必须具有唯一的名字。SELECT语句：返回的是一行或多行数据。在声明了游标后，就把它连接到了一个由该SELECT语句返回的结果集中。注意，这里的SELECT语句不能有INTO子句。例如，定义在orderitems表上的游标声明cur_gitem，查询按商品编号统计订单个数和汇总订货数量。DECLAREcur_gitemCURSOR

FOR SELECT商品编号,COUNT(*)AS订单个数,SUM(订货数量)AS订货总量 FROMorderitems GROUPBY商品编号;游标（2）打开游标：OPEN游标名声明游标后，要使用游标从中提取数据，就必须打开游标。在MySQL程序中，一个游标可以打开多次，由于其他用户或程序本身可能已经更新了表，所以每次打开查看的结果可能会有不同。（3）读取游标：FETCH...INTO语句游标打开后，一次就可以从中读取一行数据。FETCH游标名INTO变量名,...说明：FETCH...INTO语句与前面介绍的SELECT...INTO语句存在相似之处，这里游标相当于一个指针，而FETCH...INTO语句正是将游标当前所指向的一行数据依次赋给后面跟的一系列变量，子句中变量的数目必须等于声明游标的SELECT语句中列的数目。（4）关闭游标：CLOSE游标名游标使用完以后要及时关闭。游标2.游标操作实例【例9.9】根据订单编号查询购买的商品编号。运行结果如图9.18所示。说明：（a）游标操作。（b）游标读取控制。定义逻辑局部变量myfound，初值为真（true）（b.1）；定义游标读不到记录（NOTFOUND，FOUND为系统定义）时，使myfound=false（b.2）；FETCH操作游标（b.3）；根据操作情况，如果myfound为false，则退出循环（b.4）。（c）循环控制。循环开始（c.1）；循环结束（c.3）；退出循环（c.2）。（d）用户变量置初值（d.1），读取（d.2），商品编号连接、商品种类计数、商品数量累加（d.3）。第9章

过程式对象程序设计——存储过程存储过程存储过程可以由应用程序、触发器、函数或者另一个存储过程来调用，执行存储过程过程体中的代码段。使用存储过程有以下优点：（1）存储过程在服务器端运行，执行速度快。（2）存储过程执行一次后就驻留在高速缓存中，以后的操作只需从高速缓存中调用已编译好的二进制代码执行即可，提高了系统性能。（3）确保数据库安全。用户使用存储过程可以完成所有数据库操作，根据调用用户情况控制对数据库信息的访问。01存储过程的基本操作1.创建存储过程2.查看存储过程3.调用存储过程4.修改存储过程5.删除存储过程存储过程的基本操作1.创建存储过程创建存储过程语句：CREATEPROCEDURE存储过程名([IN|OUT|INOUT参数名类型,...]) [存储过程体]说明：（1）创建存储过程的用户必须具有CREATEROUTINE权限。（2）存储过程名：遵守标识符命名规范。系统默认在当前数据库中创建存储过程，如果需要在特定数据库中创建，则写为“数据库名.存储过程名”。（3）参数名：支持三种类型的参数：输入参数、输出参数和输入/输出参数，对应的关键字分别是IN、OUT和INOUT。输入参数使数据可以传递给存储过程；当需要返回结果的时候，使用输出参数；输入/输出参数既可以充当输入参数也可以充当输出参数。参数名后跟参数的类型，当有多个参数的时候中间以逗号隔开。一个存储过程可以有一个或一个以上参数，也可以不带参数，但是存储过程名后面的括号是不可省略的。（4）存储过程体：通常这个部分总是以BEGIN开始以END结束。当然，当存储过程体中只有一条SQL语句时可以省略BEGIN...END语句。存储过程的基本操作2.查看存储过程要想查看数据库中有哪些存储过程，除了可以在Navicat中指定的数据库下“函数”树型目录下显示外，还可以通过下面语句：SHOWPROCEDURESTATUS;3.调用存储过程存储过程创建好后，可以在应用程序、触发器或者其他存储过程中调用，调用语句如下：CALL存储过程名(参数,...)如果没有参数，括号也是必须的。存储过程的基本操作4.修改存储过程存储过程建好后可以进行修改，修改存储过程包括修改其属性和修改功能。1）修改存储过程属性存储过程属性在创建时就可以指定（前面省略了），修改存储过程的某些属性语句如下：ALTERPROCEDURE存储过程名[ COMMENT'string' |LANGUAGESQL |{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA} |SQLSECURITY{DEFINER|INVOKER}}存储过程的基本操作说明：（1）COMMENT'string'：对存储过程的描述，string为描述的内容。这个信息可以用SHOWCREATEPROCEDURE语句来显示。（2）LANGUAGESQL：表明编写这个存储过程的语言为SQL语言，目前来讲，MySQL存储过程还不能用外部编程语言来编写，将来会对其扩展，最有可能第一个被支持的语言是PHP。（3）CONTAINSSQL：表示存储过程不包含读或写数据的语句。（4）NOSQL：表示存储过程不包含SQL语句。（5）READSSQLDATA：表示存储过程包含读数据的语句，但不包含写数据的语句。（6）MODIFIESSQLDATA：表示存储过程包含写数据的语句。（7）SQLSECURITY：后面专门介绍。2）修改存储过程的功能目前在MySQL中仅支持先删除再重新定义存储过程的方式来修改其功能。存储过程的基本操作5.删除存储过程DROPPROCEDURE[IFEXISTS]存储过程名说明：（1）IFEXISTS子句是MySQL的扩展，如果要删的存储过程不存在，用它可防止发生错误。（2）在删除之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。02存储过程的应用1.带参数存储过程2.存储过程调用存储过程存储过程的应用1.带参数存储过程输入参数可以为常数和表达式，输出参数可为用户定义变量。【例9.10】存储过程计算圆的周长和面积。USEmydb;DROPPROCEDUREIFEXISTSproc_circle;CREATEPROCEDUREproc_circle(INrint(1),OUTlenfloat,OUTareafloat)BEGIN SETlen=2*PI()*r; SETarea=PI()*r*r;END;

SET@r1=6;CALLproc_circle(6,@len1,@area1);SELECT@r1AS'半径',FORMAT(@len1,2)AS'周长',FORMAT(@area1,2)AS'面积';运行结果如图9.19所示。存储过程的应用2.存储过程调用存储过程【例9.11】生成符合条件订单的订单编号、商品种类数、商品数量和该订单所有商品编号。1）复制订单表（orders）符合条件的记录，创建“订单商品表”（myorder_comms），在myorder_comms表增加商品种类数、商品数量和订单商品编号列。商品种类数和商品数量由订单项表（orderitems）统计和汇总得到，查询获得的商品编号连在一起存放在订单商品编号列中。USEemarket;DROPPROCEDUREIFEXISTSproc_myorder_comms;CREATEPROCEDUREproc_myorder_comms(INuidchar(16),INcdatechar(10))BEGIN DROPTABLEIFEXISTSmyorder_comms; CREATETABLEmyorder_commsAS(SELECT*FROMorders WHERE帐户名LIKEuidAND下单时间>=cdate); ALTERTABLEmyorder_comms ADD商品种类数int(2), ADD商品数量int(3), ADD订单商品编号varchar(200);END;

CALLproc_myorder_comms('%','2019.01.01');#CALLproc_myorder_comms('%','2019.01.01');存储过程的应用2）创建存储过程proc_orders：调用proc_myorder_comms生成myorder_comms表。查询订单表（orders）所有订单编号记录作为游标，遍历游标记录。每一次从游标读取订单编号，调用存储过程proc_comm_CONCAT（参考前面游标操作实例），把指定订单编号对应的所有商品编号连接起来，更新myorder_comms表订单编号对应“订单商品编号”列的内容。说明：（a）为什么已经存在仅含“订单编号”项记录复制表myorder_comms，而创建游标仍然采用orders表？因为在游标操作过程中需要更新myorder_comms表的订单商品编号列，会干扰游标，使其不能正常运行。（b）共有三个存储过程：（b.1）创建proc_orders存储过程。（b.2）调用proc_myorder_comms存储过程，生成myorder_comms表；游标开始：顺序读取“订单编号”→oid。（b.3）调用proc_comm_CONCAT存储过程，根据“订单编号”查询所有商品编号，连接起来→@cids；更新myorder_comms表，@cids→订单商品编号；游标结束。（c）生成''帐户在'2019.01.01'及以后下单的商品数据，运行结果如图9.20所示。（d）生成所有帐户在'2019.01.01'及以后下单的商品数据。存储过程的应用3）存储过程proc_myorder_comms中创建和修改myorder_comms表结构，存储过程proc_orders修改myorder_comms表记录，均没有使用事务，一旦出现问题，表记录就不能保持正确和完备。但这个操作并没有修改基本表（commodity、user、supplier、orders、orderitems和category），所以即使出现问题也并不太严重，因为用户看到数据不正确时可以重新执行一次。03存储对象访问控制存储对象访问控制如果考虑存储过程权限控制，存储过程中还包含了指定用户对存储对象访问控制的选项。CREATEPROCEDURE存储过程名

[DEFINER=用户名] ([IN|OUT|INOUT参数名类型,...])

[SQLSECURITY{DEFINER|INVOKER}] [存储过程体]说明：（1）DEFINER=用户名：指定一个MySQL帐户。如果定义省略了DEFINER属性，默认的是创建存储过程对象的用户。可以指定帐户应该是已经创建的，并且具有SYSTEM_USER特权，否则就是创建存储过程的帐户。指定一个不存在的帐户，系统会显示错误。（2）SQLSECURITY：指定该存储过程使用创建该存储过程（DEFINER=用户名）属性指定的用户的许可来执行，还是使用调用者（INVOKER）的许可来执行，默认是DEFINER。第9章

过程式对象程序设计——存储函数01存储函数的基本操作1.创建存储函数2.调用存储函数3.查看存储函数4.删除存储函数5.修改存储函数存储函数的基本操作1.创建存储函数创建存储函数的语句如下：CREATEFUNCTION存储函数名([参数名类型,...]) RETURNS类型 [存储函数体]说明：（1）存储函数名：遵守标识符命名规范，不能与存储过程同名。（2）参数名：存储函数的参数只有名称和类型，且只能是输入参数。（3）RETURNS子句：声明函数返回值的数据类型。（4）存储函数体：与存储过程一样，包括SQL语句、流程控制语句、游标等。但是存储函数体中必须包含一个RETURN子句，给出返回值。存储函数只能返回一个值。（5）存储函数有关用户选项内容与存储过程相同。存储函数的基本操作2.调用存储函数存储函数创建后，就如同系统提供的内置函数一样可供调用使用，调用语句为：存储函数名([参数,...])调用存储函数的方式与调用存储过程相比，更加灵活多样。存储过程只能采用CALL语句直接调用，而存储函数则可以出现在各种语句中。例如，对于存储函数fu(x)，调用方式可以是如下：@y=fu(x);IFfu(x)...;WHILEfu(x)DO...;SELECTfu(x),...;SELECT...WHEREfu(x)...UPDATE...SET列名=fu(x);存储函数的基本操作3.查看存储函数查看数据库中存储函数使用如下语句：SHOWFUNCTIONSTATUS4.删除存储函数删除存储函数语句为：DROPFUNCTION[IFEXISTS]存储函数名存储函数的基本操作5.修改存储函数（1）修改存储函数的属性：ALTERFUNCTION存储函数名[ COMMENT‘string’ |LANGUAGESQL |{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA} |SQLSECURITY{DEFINER|INVOKER}}存储函数的基本操作（2）修改存储函数的功能，与修改存储过程功能一样也只能采取先删除后重新定义的方式实现。【例9.12】创建计算阶乘的存储函数factorial，计算5！+8！。SETGLOBALlog_bin_trust_function_creators=1;USEmydb;DROPFUNCTIONIFEXISTSfactorial;CREATEFUNCTIONfactorial(nint) RETURNSbigintBEGIN DECLAREn1intDEFAULT1; DECLAREsbigintDEFAULT1; SETn1=1; WHILEn1<=nDO SETs=s*n1; SETn1=n1+1; ENDWHILE; RETURNs;END;SET@n=5;SELECTfactorial(@n)+factorial(@n+3)AS'5!+8!=';运行结果如图9.21所示。02存储函数的应用1.存储函数辅助数据库操作2.存储函数查询数据库3.存储函数更新数据库存储函数的应用1.存储函数辅助数据库操作在emarket数据库中，用户表（user）包含的内容较多，列数据类型丰富，通过存储函数辅助，可以使user表操作更方便。例如：身份证号列包含了出生日期，除了对身份证号查询外，可以通过身份证号查询用户出生日期和计算年龄，如果出生日期查询频繁，可以在user表定义生成（虚拟）列“出生日期”如下：身份证号 char(18) NOTNULL,出生日期 char(8) AS(SUBSTR(身份证号,7,8)),但是，生成列会显著降低user表的工作效率。另外，因为不确定的系统函数不能出现在生成列定义的表达式中，例如CURDATE()，因为它获取的是系统当前日期，每一天的值都不同，是不确定值的函数，而如果我们要根据身份证号计算年龄却需要用到这个函数，由此可见，不是所有可以由其他列生成的内容表达式都可用于定义生成列。存储函数的应用【例9.13】定义存储函数sfz_age()，从身份证字符串中得到年龄，需要时调用该函数。USEemarket;DROPFUNCTIONIFEXISTSsfz_age;CREATEFUNCTIONsfz_age(sfzchar(18)) RETURNSint BEGIN DECLAREageint; SETage=CAST(SUBSTR(sfz,7,4)ASunsigned); SETage=YEAR(CURDATE())-age+1; RETURNage; END;

SELECT姓名,身份证号,sfz_age(身份证号)AS年龄 FROMuser WHEREsfz_age(身份证号)>50;运行结果如图9.22所示。存储函数的应用2.存储函数查询数据库【例9.14】显示所有指定供应商销售总金额。分析：orderitems为订单项表，商品编号第3位开始的2个字符就是该商品供应商编号，将指定供应商编号的记录过滤出来，通过商品编号与commodity表连接，获得commodity表中对应的商品价格，将orderitems中的订货数量与商品价格相乘，得到金额，然后将它们累加起来，最后将结果INTO局部变量中返回。（1）定义存储函数fsupplier(suidchar(2))，计算指定供应商销售总金额，函数输入参数为供应商编号。SETGLOBALlog_bin_trust_function_creators=1;USEemarket;DROPFUNCTIONIFEXISTSfsupplier;CREATEFUNCTIONfsupplier(suidchar(2)) RETURNSfloat(6,2)BEGIN DECLAREsjefloat(6,2); SELECTSUM(订货数量*价格)AS金额 FROMorderitemsLEFTJOINcommodity ONorderitems.商品编号=commodity.商品编号 WHERESUBSTR(orderitems.商品编号,3,2)=suid INTOsje; RETURNsje;END;存储函数的应用（2）查询供货商表（supplier）所有记录，输出项调用计算指定供应商销售总金额的存储函数fsupplier(供货商编号)，供货商编号列作为存储函数的输入参数。SELECT供货商编号,供货商名称,fsupplier(供货商编号)AS销售总额 FROMsupplier;执行查询，结果如图9.23所示。存储函数的应用3.存储函数更新数据库【例9.15】购物过程：如果指定商品库存量满足数量要求，商品表（commodity）库存量减购物数量，订单表（orders）加订单编号、帐户名和订货数量记录，订单项表（orderitems）加入订单编号、订货数量、下单时间记录。前面已经用存储过程（shopping）实现过，这里改用存储函数fshopping实现。运行结果如图9.24所示。

存储函数的应用说明：（a）第一次执行存储函数，系统中没有订单编号=102的订单，并且商品编号='2A1602'的商品库存量足够，所以购物成功。打开commodity、orders和orderitems表，可查看到更新的有关记录如图9.25所示。

存储函数的应用（b）第二次执行存储函数，由于订单编号=102的订单已经存在，所以执行不成功，显示相应的提示信息。（c）存储函数无法（也没有必要）返回表数据，而且用户对更新操作最关心的是做得怎么样、是否出现问题，所以这里函数值返回执行状态信息最合适。可以对不同状态信息事先约定不同的编号，调用存储函数后，根据函数返回值就可以知道本次操作的执行情况。本例函数返回一个整数值表示不同的操作结果信息（c.1）：-1表示订单编号已经存在（c.2）；1表示购物成功（c.3）；-2表示商品库存量不够（c.4）。（d）本存储函数购物操作更新了若干表：更新商品表commodity的库存量（d.1）；更新订单表orders，增加新订单记录（d.2）；更新订单项表orderitems，增加新的订单项（d.3）。第9章

过程式对象程序设计——触

发

器01触发器及其创建和修改1.创建触发器2.触发动作前后列名描述3.界面修改触发器过程体4.触发器的删除