第8章 习题答案

第8章习题答案习题1答案需创建beforeinsert和beforeupdate两种触发器，分别在插入选课记录和修改成绩时检查score字段值是否在0~100范围内，若超出范围则抛出错误阻止操作。具体实现步骤如下：1.创建插入前检查触发器sql

usechoose;

droptriggerifexistschoose_before_insert_score_trigger;

delimiter$$

createtriggerchoose_before_insert_score_trigger

beforeinsert

onchooseforeachrow

begin

--检查成绩是否在0~100之间

ifnew.score<0ornew.score>100then

signalsqlstate'45000'setmessage_text='成绩score必须在0~100范围内取值';

endif;

end;

$$

delimiter;2.创建修改前检查触发器sql

usechoose;

droptriggerifexistschoose_before_update_score_trigger;

delimiter$$

createtriggerchoose_before_update_score_trigger

beforeupdate

onchooseforeachrow

begin

--检查修改后的成绩是否在0~100之间

ifnew.score<0ornew.score>100then

signalsqlstate'45000'setmessage_text='成绩score必须在0~100范围内取值';

endif;

end;

$$

delimiter;3.关键说明选用before时机：确保在数据插入/修改前完成检查，避免非法数据进入表中；使用signal语句：手动抛出自定义错误（错误码45000为MySQL保留自定义错误区间），阻止非法操作；行级触发器：foreachrow确保每一条插入/修改的记录都被检查。习题2答案根据教材8.1.2节内容，答案如下：1.触发事件（3种）insert：使用insert或replace语句向表添加记录时触发；update：使用update语句修改表记录时触发；delete：使用delete或replace语句删除表记录时触发。2.运行时机（2种）before：在触发事件对应的操作（插入/修改/删除）执行前，执行触发程序；after：在触发事件对应的操作执行后，执行触发程序。补充：同一表最多可组合出6种触发器（如beforeinsert、afterinsert等）。习题3答案结合教材8.1.3节和8.1.9节内容，创建触发器的核心注意事项如下：指定所属数据库：触发器是数据库对象，创建前建议用use数据库名;打开目标数据库；命名唯一：同一数据库内，触发器名不能与已有触发器重名，建议命名格式：表名_运行时机_触发事件_trigger（如course_before_insert_trigger）；监控对象限制：触发器只能监控基表（实体表），不能监控临时表和视图；行级触发器限制：MySQL仅支持行级触发器（必须指定foreachrow），针对每一条受影响的记录执行触发程序；新旧记录关键字使用：

insert事件仅new可用，delete事件仅old可用，update事件两者均可用；old永远只读，new在after触发器中只读、在before触发器中可写；避免死循环：禁止触发程序对其监控的表执行insert/update/delete操作（若操作其他表则无此限制）；触发程序限制：

select语句不能产生结果集；不能包含事务相关语句（如starttransaction、commit等）；存储引擎影响：MyISAM引擎下触发器不保证原子性，InnoDB引擎（支持事务）可保证触发操作与主操作的原子性；严格测试：必须测试功能正确性（如约束是否生效）和兼容性（如是否妨碍合法操作），测试后再投入使用。习题4答案根据教材8.1.1节及后续案例，触发器可实现以下数据自动维护功能：自动维护冗余数据：针对表中的冗余字段，无需人工干预，由触发器自动同步更新。例如教材8.1.6节案例：

学生选课时（insert选课记录），自动将对应课程的“剩余名额”（available）减1；学生退课时（delete选课记录），自动将对应课程的“剩余名额”加1；模拟实现检查约束：MySQL本身不支持检查约束，可通过beforeinsert/beforeupdate触发器实现字段取值限制。例如教材8.1.7节案例：

限制课程容量（capacity）只能取60、150、230；限制成绩（score）只能在0~100范围内（对应习题1场景）；模拟实现外键级联操作：虽MySQL支持外键级联，但可通过触发器自定义更灵活的级联逻辑。例如：

删除父表（如teacher）记录时，自动删除子表（如course）中关联的记录；更新父表主键（如teacher_no）时，自动同步子表中对应的外键值；自动记录数据变更日志：无需手动插入日志，由触发器自动记录数据的新增、修改、删除记录。例如：

当course表修改课程信息时，自动向course_log日志表插入“修改前值、修改后值、修改时间、操作人”等信息；数据一致性校验与修正：在数据插入/修改前自动校验合法性，若存在微小异常可自动修正（需结合业务场景）。例如：

插入学生记录时，自动将学号（student_no）转为大写；校验课程状态，若插入“未审核”课程则自动补充审核时间为NULL。习题5答案以下基于教材8.2.8节和8.5.5节内容，完成选课存储过程的编写、调用与测试。一、编写选课存储过程（含错误处理）功能：实现学生选课逻辑，返回状态码标识选课结果（含学生不存在、课程已满等异常处理）。sql

usechoose;

dropprocedureifexistschoose_course_proc;

delimiter$$

createprocedurechoose_course_proc(

ins_nochar(11),--输入：学生学号

inc_noint,--输入：课程编号

inoutstatus_codeint--输入输出：选课状态码

)

modifiessqldata

begin

--定义局部变量

declaretempintdefault0;--存储临时数据（如已选课程数、剩余名额）

declarecourse_statuschar(8)default'未审核';--存储课程状态

--定义错误处理程序：捕获外键约束错误（学生不存在）

declareexithandlerfor1452

begin

setstatus_code=-5;--状态码-5：学生不存在

end;

--初始化状态码

setstatus_code=0;

--校验1：是否重复选课

ifc_noin(selectcourse_nofromchoosewherestudent_no=s_no)then

setstatus_code=-1;--状态码-1：重复选课

else

--校验2：是否超过选课上限（假设最多选2门）

selectcount(*)intotempfromchoosewherestudent_no=s_no;

iftemp>=2then

setstatus_code=-2;--状态码-2：超过选课上限

else

--校验3：课程是否已满（剩余名额available=0）

selectavailableintotempfromcoursewherecourse_no=c_no;

iftemp=0then

setstatus_code=-3;--状态码-3：课程已满

else

--校验4：课程是否已审核（或不存在）

selectstatusintocourse_statusfromcoursewherecourse_no=c_no;

ifcourse_status='未审核'then

setstatus_code=-4;--状态码-4：课程未审核或不存在

else

--所有校验通过，执行选课（插入选课记录）

ifstatus_code=0then

insertintochoose(student_no,course_no,create_time)

values(s_no,c_no,now());

setstatus_code=last_insert_id();--状态码>0：选课成功（返回自增ID）

endif;

endif;

endif;

endif;

endif;

end;

$$

delimiter;二、调用存储过程需通过用户变量接收状态码，步骤如下：sql

usechoose;

--1.定义用户变量（存储输入参数和状态码）

set@s_no='2024004';--学生学号（可替换为实际学号）

set@c_no=1;--课程编号（可替换为实际课程号）

set@status_code=0;--初始化状态码

--2.调用存储过程

callchoose_course_proc(@s_no,@c_no,@status_code);

--3.查看选课结果（通过状态码判断）

select@status_codeas选课状态码,

case

when@status_code>0then'选课成功'

when@status_code=-1then'重复选课'

when@status_code=-2then'超过选课上限（最多2门）'

when@status_code=-3then'课程已满'

when@status_code=-4then'课程未审核或不存在'

when@status_code=-5then'学生不存在'

else'未知错误'

endas结果说明;三、测试存储过程（覆盖所有场景）需测试以下5种异常场景和1种成功场景，确保功能完整：测试场景测试步骤预期结果（状态码+说明）场景1：重复选课学生2024004已选课程1，再次选课程1-1+重复选课场景2：超过选课上限学生2024004已选2门课程，再选课程3-2+超过选课上限场景3：课程已满学生2024004选课程6（available=0）-3+课程已满场景4：课程未审核学生2024004选课程5（状态为“未审核”）-4+课程未审核或不存在场景5：学生不存在学号2024000000（不存在）选课程1-5+学生不存在场景6：选课成功学生2024004（已选1门）选课程1（审核通过、有剩余名额）>0（如10）+选课成功习题6答案根据教材8.2.6节内容，查看存储过程定义的方法有3类，具体如下：1.查看存储过程状态（基础信息）使用showprocedurestatus，可查看存储过程的所属数据库、创建时间、最后修改时间等基础信息。sql

--查看所有数据库中所有存储过程的状态

showprocedurestatus\G

--查看指定名称的存储过程状态（如get_name_proc）

showprocedurestatuslike'get_name_proc'\G

--查看指定数据库（如choose）中所有存储过程的状态

showprocedurestatuswheredb='choose'\G2.查看存储过程完整创建语句使用showcreateprocedure，可查看存储过程的完整SQL定义（包括参数、过程体、选项等），最常用。sql

usechoose;--先打开目标数据库

--查看指定存储过程（如choose_course_proc）的完整定义

showcreateprocedurechoose_course_proc\G3.查询系统表information_schema.routines存储程序（存储过程、函数）的元数据存储在information_schema.routines表中，可通过select语句灵活筛选。sql

--查看所有存储过程的定义（排除函数）

selectroutine_name,routine_definition

frominformation_schema.routines

whereroutine_type='PROCEDURE'\G

--查看choose数据库中所有存储过程的定义

selectroutine_name,routine_definition

frominformation_schema.routines

whereroutine_type='PROCEDURE'androutine_schema='choose'\G

--查看指定存储过程（如get_student_of_course_proc）的定义

selectroutine_definition

frominformation_schema.routines

whereroutine_name='get_student_of_course_proc'androutine_type='PROCEDURE'\G说明：routine_type='PROCEDURE'表示筛选存储过程，routine_type='FUNCTION'表示筛选自定义函数。习题7答案存储过程与自定义函数同属“存储程序”，核心是实现模块化编程，但在功能、语法、使用场景上有明显区别，具体如下：一、联系（共同点）均为数据库对象，创建时需指定所属数据库（建议先执行use数据库名;）；均可实现模块化编程：将重复业务逻辑封装，提高代码复用性和维护性；均可节省网络开销：应用程序仅需调用存储程序名+参数，无需传输多条SQL语句；均可增强数据安全：可通过权限控制，仅允许特定用户执行存储程序；均需严格测试：需验证功能正确性、兼容性（如是否妨碍合法操作）后再投入使用；语法基础一致：均支持局部变量、流程控制（if、else、loop等）、错误处理等。二、区别对比维度存储过程自定义函数返回值无return语句，无“返回值”；可通过out/inout参数返回多个结果（配合用户变量接收）必须有return语句，且仅能返回一个结果（返回值类型在创建时指定）调用方式必须用call关键字调用，格式：call存储过程名(参数);，不能嵌入SQL语句可直接嵌入SQL语句（如select、where子句），格式：函数名(参数)，无需call关键字select语句限制过程体内的select语句可产生结果集，甚至多条select语句产生多个结果集函数体内的select语句不能产生结果集（仅允许用into子句赋值给变量）参数类型支持in（输入）、out（输出）、inout（输入输出）三种参数仅支持in类型参数（默认，可省略），无out/inout参数业务场景适合复杂业务逻辑：如选课（多校验步骤）、数据批量处理、事务操作等；需返回多个结果或结果集时优先使用适合简单值计算/转换：如计算成绩平均分、格式化日期等；需嵌入SQL语句（如select字段列表）时优先使用代码限制限制少，支持事务相关语句（starttransaction、commit等）、预处理SQL等限制多，不支持事务语句、预处理SQL；部分函数（如now()）使用有特殊限制总结：存储过程功能更强大、灵活，适合复杂业务；自定义函数针对性强，适合简单值计算和SQL语句扩展。习题8答案在错误处理机制中，continue和exit是两种核心错误处理方式，决定了错误发生后的程序执行流程，具体区别如下：1.错误处理方式：continue（继续执行）执行流程程序执行到某行代码时抛出错误；触发对应的错误处理程序，执行处理逻辑（如记录日志、赋值错误标识）；错误处理程序执行完毕后，回到抛出错误的代码行的下一行，继续执行后续代码。示例（结合选课场景）sql

declarecontinuehand