MySQL教程（新体系-综合应用实例视频）（第4版）习题及答案 -第09章

第9章过程式对象程序设计一、选择题1．下列说法错误的是（）。A．语句体包含一个以上语句需要采用BEGIN…ENDB．多重分支只能用CASE语句C．WHILE中循环体可以一次不执行D．注释内容不会产生任何动作2．关于存储过程的说法，说法错误的是（）。A．方便用户完成某些功能B．存储过程方便用户批量执行-SQL命令C．存储过程不能调用存储过程D．应用程序可以调用存储过程3．存储过程与外界的交互不能通过（）。A．表 B．输入参数 C．输出参数 D．游标4.关于存储函数，说法错误的是（）。A.函数值只能一个 B.不能采用事务C.不能包含输出参数 D.不能使用用户变量5.关于游标，说法错误的是（）。A.游标用于处理多条记录 B.一次只能读取一条记录C.只能顺序读取 D.游标定义不能保存6.关于事务，说法错误的是（）。A.事务一般用于一条以上增删改语句中 B.回滚后事务中记录的变化均不会发生C.必须关闭自动提交 D.同一个事务中不同查询的相同项可能检索到不同的结果7．关于触发器的错误说法是（）。A．INSERT触发器先插入记录后判断 B．DELETE触发器先删除记录后判断C．UPDATE触发器先判断后修改记录 D．A和B8．关于触发器的错误说法是（）。A．游标一般用于存储过程 B．游标也可用于触发器C．应用程序可以调用触发器 D．触发器一般针对表二、说明题1．为什么需要使用存储过程和存储函数？2．存储函数和存储过程有什么不同，它们分别应用在什么场合？3.为什么需要错误处理？什么情况下需要抛出错误信息？4.如何指定存储函数和存储过程调用者？5.加锁后是否不需要事务？6.事务处理和错误处理功能有什么不同？7.什么是幻读？什么是MVCC？8.为什么需要使用触发器？。9.为什么需要使用事件？定义的事件是否一定保存？三、编程题（基于xscj数据库）1.创建存储过程proc_area，计算三角形面积。输入参数为三边长（a,b,c），输出参数为三角形面积。调用存储过程，显示计算结果。2.创建存储函数func_area，计算三角形面积。输入参数为三边长（a,b,c），函数返回为三角形面积。调用存储函数，显示计算结果。边长小于等于0，返回NULL。3.编写插入成绩表（cj）记录存储过程CJ_INSERT，输入参数为学号、课程号、成绩，输出参数为状态。如果对应学号和课程号存在，返回状态0，否则进行插入操作。包括：（1）查找课程表（kc）对应该课程的学分，如果找不到该课程，课程号输入参数错误，状态为-11。（2）查找学生表（xs）对应学号，如果找不到该学号，学号输入参数错误，状态为-12。（3）如果输入参数正确，用输入参数对成绩表（cj）插入记录，同时更新（累加）学号对应记录总学分，返回状态1。插入操作需要采用事务，如果事务操作不成功，状态为-1。4.编写修改成绩表（cj）记录存储过程CJ_UPDATE，输入参数为学号、课程号、成绩，输出参数为状态。如果对应学号和课程号不存在，调用插入成绩表（cj）记录存储过程CJ_INSERT，将CJ_INSERT存储过程返回状态作为本存储过程状态返回。否则进行修改操作。包括：（1）读取课程表（kc）对应课程的学分。（2）如果原来成绩和新成绩均>=60或者均<60，修改成绩表对应成绩。返回状态1。（3）如果原来成绩>=60而新成绩<60，修改成绩表对应成绩，同时学生表（xs）总学分减该课程学分。返回状态1。（4）如果原来成绩<60而新成绩>=60，修改成绩表对应成绩，同时学生表（xs）总学分加该课程学分。返回状态1。修改操作需要采用事务，如果事务操作不成功，状态为-1。5.创建通过游标实现学生表（xs）总学分初始化存储过程XS_ZXF_RESET。输入参数为学号，输出参数为状态。成绩表（cj）对应学号查询定义作为游标，学生表（xs）对应学号学生记录加锁，总学分置0，循环读取游标记录，根据成绩，读取对应课程学分，累加学生表（xs）学号记录总学分。循环完成后，学生表（xs）对应学号学生记录解锁。6.创建学生表（xs）删除触发器，判断成绩表（cj）是否存在对应的学号记录，如果存在，发出错误信号，阻止学生表（xs）删除操作。参考答案习题-第09章一、选择题 1.B 2.C 3.D 4.D 5.D 6.D 7.D 8.C二、说明题 1.存储过程和存储函数在服务器端运行、执行速度快，其执行一次后，执行规划就驻留在高速缓冲存储器，以后操作只需从高速缓存中调用已编译好的二进制代码，提高了系统性能。另外，使用存储过程和存储函数可以完成所有数据库操作，并可通过编程方式控制上述操作对数据库信息访问的权限，确保数据库安全。 2.存储函数与存储过程的区别：（1）存储函数不能拥有输出参数，因为存储函数本身就是输出参数；（2）不能用CALL语句来调用存储函数；（3）存储函数必须包含一条RETURN语句，而这条特殊的SQL语句不允许包含于存储过程中。存储函数多用于辅助数据库操作（如克服生成列的局限性），但MySQL的存储函数并不支持使用显示或隐式事务，所以涉及基本表的更新操作一般不要采用存储函数实现，而是用存储过程结合事务的方法来保证更新数据的一致和完备性。 3.MySQL程序中处理SQL语句可能会出错，导致系统停止对程序的处理，使用错误处理功能可以为不同的错误定义不同的处理程序，让系统根据条件自动处理。在实际应用中有时需要根据情况主动向MySQL系统提供信号，以便服务器向处理程序、客户端提供当前数据库操作情况信息，这个时候就需要抛出错误信息。 4.存储过程创建好后，可以在应用程序、触发器或者其他存储过程中通过“CALL存储过程名(参数,...)”调用；而存储函数创建好后，就如同系统提供的内置函数一样可供随时调用使用，调用语句为“存储函数名([参数,...])”，调用存储函数的方式与调用存储过程相比，更加灵活多样。存储过程只能采用CALL语句直接调用，而存储函数则可以出现在各种语句中。 5.仍然需要事务的，因为加锁解决并发问题，而事务解决多语句操作记录的完整性。 6.事务处理是将一系列不可分割的增删改查操作放在同一个事务中执行以确保数据完整一致性，发生错误失败时事务会回滚，不会对数据库造成任何影响；而错误处理则是在出错时根据错误类型和返回码预先定义处理程序，发生错误时由MySQL自动执行处理程序，而错误所造成对数据的影响仍然是作用在数据库上的，只不过通过执行错误处理程序可以加以弥补和善后，并提示用户发生错误的原因以便排查。 7.幻读（PhantomRead）是当一个事务对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时发生的问题。事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其他事务删除。同样，由于其他事务的插入操作，事务的第二次读或后续读显示有一行已不存在于原始读中。 MVCC（MultiversionConcurrencyControl），即多版本并发控制技术。它通过数据行的多个版本管理来实现数据库的并发控制，简单来说就是保存数据的历史版本。可以通过比较版本号决定数据是否显示。读取数据的时候不需要加锁可以保证事务的隔离效果。 8.触发器与表的关系密切，用于保护表中的数据。当有操作影响到触发器保护的数据时，触发器自动执行，例如，通过触发器实现多个表间数据的一致性。当对表执行INSERT、DELETE或UPDATE操作时，将激活触发器。 9.事件的主要作用是：关闭账户；打开或关闭数据库指示器；使数据库中的数据在某个间隔后刷新；执行对进入数据的复杂的检查工作。定义的事件会被作为数据库对象保存在系统中，在设定的时刻被启动运行。三、编程题1.存储过程proc_area计算三角形面积，执行语句：USEmydb;DROPPROCEDUREIFEXISTSproc_area;CREATEPROCEDUREproc_area(INaint(1),INbint(1),INcint(1),OUTareafloat)BEGIN SETarea=(1/4)*SQRT((a+b+c)*(a+b-c)*(a+c-b)*(b+c-a));END;SET@a=3;SET@b=4;SET@c=5;CALLproc_area(@a,@b,@c,@area);SELECTFORMAT(@area,2)AS'三角形面积';2.存储函数func_area计算三角形面积，执行语句：SETGLOBALlog_bin_trust_function_creators=1;USEmydb;DROPFUNCTIONIFEXISTSfunc_area;CREATEFUNCTIONfunc_area(aint(1),bint(1),cint(1)) RETURNSfloatBEGIN DECLAREareafloat; IFa<=0ORb<=0ORc<=0THEN RETURNNULL; ENDIF; SETarea=(1/4)*SQRT((a+b+c)*(a+b-c)*(a+c-b)*(b+c-a)); RETURNarea;END;SET@a=3;SET@b=4;SET@c=5;SELECTFORMAT(func_area(@a,@b,@c),2)AS'三角形面积';3.编写插入成绩表（cj）记录存储过程CJ_INSERT，执行语句：USExscj;CREATEPROCEDURECJ_INSERT(INsidchar(6),INcidchar(3),INscoreint,OUTCodeint)label:BEGIN DECLARExfint(1)DEFAULTNULL; DECLARExhchar(6)DEFAULTNULL; DECLAREtransErrintDEFAULT0; DECLARECONTINUEHANDLERFORSQLEXCEPTIONSETtransErr=1; SETCode=0; SELECT学分INTOxfFROMkc WHERE课程号=cid; IFxfISNULLTHEN SETCode=-11; LEAVElabel; ENDIF; SELECT学号INTOxhFROMxs WHERE学号=sid; IFxhISNULLTHEN SETCode=-12; LEAVElabel; ENDIF; STARTTRANSACTION; INSERTINTOcj(学号,课程号,成绩) VALUES(sid,cid,score); UPDATExsSET总学分=总学分+xf WHERE学号=xh; IFtransErr=1THEN ROLLBACK; SETCode=-1; ELSE COMMITWORK; SETCode=1; ENDIF;END;测试执行语句：CALLCJ_INSERT('081102','100',99,@code);SELECT@code;CALLCJ_INSERT('091102','101',99,@code);SELECT@code;CALLCJ_INSERT('081102','101',99,@code);SELECT@code;4.编写修改成绩表（cj）记录存储过程CJ_UPDATE，执行语句：USExscj;CREATEPROCEDURECJ_UPDATE(INsidchar(6),INcidchar(3),INscoreint,OUTCodeint)label:BEGIN DECLARExfint(1)DEFAULTNULL; DECLARExhchar(6)DEFAULTNULL; DECLAREcjint(1)DEFAULTNULL; DECLAREcodeint(1)DEFAULT0; DECLAREtransErrintDEFAULT0; DECLARECONTINUEHANDLERFORSQLEXCEPTIONSETtransErr=1; SETCode=0; SELECT学分INTOxfFROMkc WHERE课程号=cid; IFxfISNULLTHEN CALLCJ_INSERT(sid,cid,score,@code); SELECT@codeINTOcode; SETCode=code; LEAVElabel; ENDIF; SELECT学号INTOxhFROMxs WHERE学号=sid; IFxhISNULLTHEN CALLCJ_INSERT(sid,cid,score,@code); SELECT@codeINTOcode; SETCode=code; LEAVElabel; ENDIF; SELECT成绩INTOcjFROMcj WHERE学号=sidAND课程号=cid; STARTTRANSACTION; IF(cj>=60ANDscore>=60)OR(cj<60ANDscore<60)THEN UPDATEcjSET成绩=score WHERE学号=sidAND课程号=cid; ENDIF; IFcj>=60ANDscore<60THEN UPDATEcjSET成绩=score WHERE学号=sidAND课程号=cid; UPDATExsSET总学分=总学分-xf WHERE学号=xh; ENDIF; IFcj<60ANDscore>=60THEN UPDATEcjSET成绩=score WHERE学号=sidAND课程号=cid; UPDATExsSET总学分=总学分+xf WHERE学号=xh; ENDIF; IFtransErr=1THEN ROLLBACK; SETCode=-1; ELSE COMMITWORK; SETCode=1; ENDIF;END;测试执行语句：CALLCJ_UPDATE('081102','101',95,@code);CALLCJ_UPDATE('081102','101',59,@code);CALLCJ_UPDATE('081102','101',90,@code);5.创建通过游标实现学生表（xs）总学分初始化存储过程XS_ZXF_RESET，执行语句：USExscj;CREATEPROCEDUREXS_ZXF_RESET(INsidchar(6),OUTCodeint)label:BEGIN DECLARExhchar(6)DEFAULTNULL; DECLAREkchchar(3)DEFAULTNULL; DECLARExfint(1)DEFAULTNULL; DECLAREcjint(1)DEFAULTNULL; DECLAREmyfoundbooleanDEFAULTtrue; DECLAREtransErrintDEFAULT0; DECLAREcur_cjCURSOR FOR SELECT课程号,成绩FROMcjWHERE学号=sid; DECLARECONTINUEHANDLERFORNOTFOUND SETmyfound=false; DECLARECONTINUEHANDLERFORSQLEXCEPTIONSETtransErr=1; SETCode=0; SELECTDISTINCT(学号)INTOxhFROMcj WHERE学号=sid; IFxhISNULLTHEN SETCode=-12; LEAVElabel; ENDIF; STARTTRANSACTION; SELECT*