数据库原理与应用（MySQL版） 第7章 SQL扩展编程 习题答案

第7章SQL扩展编程习题参考答案选择题1．下列不属于存储过程作用的是A．能够根据数据更改操作自动执行B．能够实现模块化程序设计C．可以减少网络流量D．可作为安全机制使用答案：A2．下列有关存储过程的叙述，错误的是A．MySQL允许在存储过程创建时引用一个不存在的对象B．存储过程可以带多个输入参数，也可以带多个输出参数C．使用存储过程可以减少网络流量D．在一个存储过程中不可以调用其他存储过程答案：D3．下列不能引发触发器执行的操作是A．INSERTB．DELETEC．SELECTD．UPDATE答案：C4．MySQL为触发器创建的两个临时表是A．max

和

minB．avg

和

sumC．int

和

charD．old

和

new答案：D5．下列说法错误的是A．触发器触发时机有BEFORE、AFTER两种B．对于同一张数据表，只能定义一个触发器C．由INSERT操作引发的触发器，NEW临时表用来保存新插入的数据D．OLD临时表中的数据是只读的，不能被更新答案：B二、简答题存储过程的作用是什么？答：（1）允许模块化程序设计（2）改善性能（3）减少网络流量（4）可作为安全机制使用存储过程和函数有哪些共同点和不同点？答：相同点：都是数据库系统中一组为了完成特定功能的SQL语句块，它将常用或复杂的工作预先用SQL语句编写脚本并指定名称存储在数据库服务器中，可以包含控制语句，有很强的灵活性，可以完成复杂的数据操作功能。不同点：（1）自定义函数不能有输出参数，这是因为自定义函数自身就是输出参数；而存储过程可以有输出参数。（2）自定义函数的函数体中必须包含一条RETURN语句，而存储过程不需要。（3）自定义函数是在SELECT语句中调用执行的，而存储过程是通过CALL语句调用执行的。存储过程的参数有哪几种形式？答：in：输入参数(默认情况下为in参数)，该参数的值在调用时指定。out：输出参数，存储过程运行过程中可以对该类参数赋值，起到将计算结果返回给调用程序的作用。inout：既是输入参数，又是输出参数，该参数的值可在调用时指定，又可以将计算结果返回给调用程序。存储过程和函数如何将结果返回给调用者？答：存储过程：通过OUT或INOUT参数返回结果。函数：通过RETURN语句返回单个值。什么是触发器？触发器和存储过程主要区别是什么？答：触发器是存储数据库中的一段完成特定功能的SQL语句集合。触发器和存储过程主要区别是触发器是由对数据进行的更改操作触发自动执行，这些操作包括INSERT、UPDATE、DELETE等。触发器的主要作用是什么？答：当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。触发器通常用于保证业务规则和复杂数据完整性。三、编程题请根据books、students和borrow表，编写实现下列要求的代码。定义变量num，统计所有图书的平均价格，将值赋给变量num并显示num的值。答：--定义变量并计算平均价格SET@num=(SELECTAVG(price)FROMbooks);--显示变量值SELECT@numASaverage_price;定义存储过程，输入参数是图书类型，求该类型的图书总数量，写出定义语句和调用语句。答：--定义存储过程DELIMITER//CREATEPROCEDUREGetBookCountByType(INbook_typeVARCHAR(50))BEGINSELECTCOUNT(*)AStotal_countFROMbooksWHEREtype=book_type;END//DELIMITER;--调用存储过程CALLGetBookCountByType('小说');定义存储过程，输入参数是出版社、出版年份，输出参数是图书数量，统计指定年份该出版社出版的图书数量，写出存储过程定义语句和调用语句。答：--定义存储过程DELIMITER//CREATEPROCEDUREGetBookCountByPublisherAndYear(INpublisher_nameVARCHAR(100),INpublish_yearINT,OUTbook_countINT)BEGINSELECTCOUNT(*)INTObook_countFROMbooksWHEREpress=publisher_nameANDYEAR(publish_date)=publish_year;END//DELIMITER;--调用存储过程CALLGetBookCountByPublisherAndYear('清华大学出版社',2022,@count);SELECT@countASbook_count;定义函数，输入参数是出生日期，返回值是学生年龄，写出函数定义语句和在SELECT语句调用此函数查询学生的姓名、年龄的语句。答：--定义函数DELIMITER//CREATEFUNCTIONCalculateAge(birth_dateDATE)RETURNSINTBEGINRETURNYEAR(CURDATE())-YEAR(birth_date)-(DATE_FORMAT(CURDATE(),'%m%d')<DATE_FORMAT(birth_date,'%m%d'));END//DELIMITER;--调用函数查询学生姓名和年龄SELECTname,CalculateAge(birth_date)ASageFROMstudents;查看题目3定义的存储过程的信息。答：SHOWPROCEDUREGetBookCountByPublisherAndYear;删除题目3定义的存储过程。答：DROPPROCEDUREGetBookCountByPublisherAndYear;定义触发器，在students表中插入新生信息前判断：如果新生年龄小于15岁或者大于60岁，则提示年龄输入错误。答：DELIMITER//CREATETRIGGERBeforeInsertStudentBEFOREINSERTONstudentsFOREACHROWBEGINDECLAREstudent_ageINT;SETstudent_age=YEAR(CURDATE())-YEAR(NEW.birth_date)-(DATE_FORMAT(CURDATE(),'%m%d')<DATE_FORMAT(NEW.birth_date,'%m%d'));IFstudent_age<15ORstudent_age>60THENSIGNALSQLSTATE'45000'SETMESSAGE_TEXT='年龄输入错误：年龄必须介于15到60岁之间';ENDIF;END//DELIMITER;为books表增加一个新列，列名：“状态”，类型为字符串类型。定义触发器，每当有图书被借出，判断该图书库存，如果库存为0，则将books表的“状态”列设为“已全部借出”。答：--增加新列ALTERTABLEbooksADDCOLUMN`状态`VARCHAR(50);--定义触发器DELIMITER//CREATETRIGGERAfterBorrowBookAFTERINSERTONborrowFOREACHROWBEGINDECLAREcurrent_stockINT;SELECTstockINTO