数据库技术与应用实验报告

一、实验目的（1）掌握通过SQLServer管理平台和Transact-SQL语句CREATEPROCEDURE创建存储过程的方法和步骤。（2）掌握使用Transact-SQL语句EXECUTE执行存储过程的方法。（3）掌握通过SQLServer管理平台和Transact-SQL语句ALTERPROCEDURE修改存储过程的方法。（4）掌握通过SQLServer管理平台和Transact-SQL语句DROPPROCEDURE删除存储过程的方法。（5）掌握通过SQLServer管理平台和Transact-SQL语句CREATETRIGGER创建触发器的方法和步骤。（6）掌握引发触发器的方法。（7）掌握使用SQLServer管理平台或Transact-SQL语句修改和删除触发器。（8）掌握事务、命名事务的创建方法，了解不同类型的事务的处理情况。二、实验内容及步骤（1）在查询设计器中输入以下代码，创建一个利用流控制语句的存储过程letters_print，该存储过程能够显示26个小写字母。CREATEPROCEDUREletters_printASDECLARE@countintSET@count=0WHILE@count26BEGINPRINTCHAR(ASCII('a')+@count)SET@count=@count+1END单击查询分析器的“执行查询”按钮，查看studentsdb数据库的存储过程是否有letters_print。使用EXECUTE命令执行letters_print存储过程。（2）输入以下代码，创建存储过程stu_info，执行时通过输入姓名，可以查询该姓名对应的学生的各科成绩。CREATEPROCEDUREstu_info@namevarchar(40)ASSELECTa.学号,姓名,课程编号,分数FROMstudent_infoaINNERJOINgradetaONa.学号=ta.学号WHERE姓名=@name使用EXECUTE命令执行存储过程stu_info，其参数值为“马东”。如果存储过程stu_info执行时没有提供参数，要求能按默认值查询（设姓名为“刘卫平”），如何修改该过程的定义？（3）使用studentsdb数据库中的student_info表、curriculum表、grade表。①创建一个存储过程stu_grade，查询学号为0001的学生的姓名、课程名称、分数。②执行存储过程stu_grade，查询0001学生的姓名、课程名称、分数。③使用系统存储过程sp_rename将存储过程stu_grade更名为stu_g。（4）使用student_info表、curriculum表、grade表。①创建一个带参数的存储过程stu_g_p，当任意输入一个学生的姓名时，将从3个表中返回该学生的学号、选修的课程名称和课程成绩。②执行存储过程stu_g_p，查询“刘卫平”的学号、选修课程和课程成绩。③使用系统存储过程sp_helptext，查看存储过程stu_g_p的文本信息。（5）使用student_info表。①创建一个加密的存储过程stu_en，查询所有男学生的信息。②执行存储过程stu_en，查看返回学生的情况。③使用Transact-SQL语句DROPPROCEDURE删除存储过程stu_en。（6）使用grade表。①创建一个存储过程stu_g_r，当输入一个学生的学号时，通过返回输出参数获取该学生各门课程的平均成绩。②执行存储过程stu_g_r，输入学号0002。③显示0002号学生的平均成绩。（7）输入以下代码，复制student_info表命名为stu2，为stu2表创建一个触发器stu_tr，当stu2表插入一条记录时，为该记录生成一个学号，该学号为学号列数据的最大值加1。--复制student_info表命名为stu2SELECT*INTOstu2FROMstudent_infoGO--为stu2表创建一个INSERT型触发器stu_trCREATETRIGGERstu_trONstu2FORINSERTASDECLARE@maxchar(4)SET@max=(SELECTMAX(学号)FROMstu2)SET@max=@max+1UPDATEstu2SET学号=REPLICATE('0',4-len(@max))+@maxFROMstu2INNERJOINinsertedonstu2.学号=inserted.学号执行以上代码，查看studentsdb数据库中是否有stu2表，展开stu2，查看其触发器项中是否有stu_str触发器。在查询设计器的编辑窗口输入以下代码：INSERTINTOstu2(学号,姓名,性别)VALUES('0001','张主','女')运行以上代码，查看stu2表的变化情况，为什么插入记录的学号值发生了改变？（8）为grade表建立一个名为insert_g_tr的INSERT触发器，当用户向grade表中插入记录时，如果插入的是在curriculum表中没有的课程编号，则提示用户不能插入记录，否则提示记录插入成功。在进行插入测试时，分别输入以下数据：学号课程编号分数00040003760005000769观察插入数据时的运行情况，说明为什么？（9）为curriculum表创建一个名为del_c_tr的DELETE触发器，该触发器的作用是禁止删除curriculum表中的记录。（10）为student_info表创建一个名为update_s_tr的UPDATE触发器，该触发器的作用是禁止更新student_info表中的“姓名”字段的内容。（11）使用Transact-SQL语句DROPTRIGGER删除update_s_tr触发器。（12）为student_info表建立删除触发器del_s_tr，要求当student_info表的记录被删除后，grade表中相应的记录也能自动删除。（13）在studentsdb数据库中，执行以下事务处理过程，说明这些事务属于哪一种事务类型（隐性事务、显性事务或自动式事务）。①BEGINTRANSACTIONINSERTINTOstudent_info(学号,姓名)VALUES('0009','李青')COMMITTRANSACTION②SETIMPLICIT_TRANSACTIONSONGOINSERTINTOgrade(学号,课程编号)VALUES('0005','0007')GOIF((SELECTcount(*)FROMcurriculumWHERE课程编号='0007')=0)三、实验结果（1）在查询设计器中输入以下代码，创建一个利用流控制语句的存储过程letters_print，该存储过程能够显示26个小写字母。CREATEPROCEDUREletters_printASDECLARE@countintSET@count=0WHILE@count26BEGINPRINTCHAR(ASCII('a')+@count)SET@count=@count+1END单击查询分析器的“执行查询”按钮，查看studentsdb数据库的存储过程是否有letters_print。使用EXECUTE命令执行letters_print存储过程。（2）输入以下代码，创建存储过程stu_info，执行时通过输入姓名，可以查询该姓名对应的学生的各科成绩。CREATEPROCEDUREstu_info@namevarchar(40)ASSELECTa.学号,姓名,课程编号,分数FROMstudent_infoaINNERJOINgradetaONa.学号=ta.学号WHERE姓名=@name使用EXECUTE命令执行存储过程stu_info，其参数值为“马东”。（3）使用studentsdb数据库中的student_info表、curriculum表、grade表。①创建一个存储过程stu_grade，查询学号为0001的学生的姓名、课程名称、分数。②执行存储过程stu_grade，查询0001学生的姓名、课程名称、分数。③使用系统存储过程sp_rename将存储过程stu_grade更名为stu_g。（4）使用student_info表、curriculum表、grade表。①创建一个带参数的存储过程stu_g_p，当任意输入一个学生的姓名时，将从3个表中返回该学生的学号、选修的课程名称和课程成绩。②执行存储过程stu_g_p，查询“刘卫平”的学号、选修课程和课程成绩。③使用系统存储过程sp_helptext，查看存储过程stu_g_p的文本信息。（5）使用student_info表。①创建一个加密的存储过程stu_en，查询所有男学生的信息。②执行存储过程stu_en，查看返回学生的情况。③使用Transact-SQL语句DROPPROCEDURE删除存储过程stu_en。（6）使用grade表。①创建一个存储过程stu_g_r，当输入一个学生的学号时，通过返回输出参数获取该学生各门课程的平均成绩。②执行存储过程stu_g_r，输入学号0002。③显示0002号学生的平均成绩。（7）输入以下代码，复制student_info表命名为stu2，为stu2表创建一个触发器stu_tr，当stu2表插入一条记录时，为该记录生成一个学号，该学号为学号列数据的最大值加1。--复制student_info表命名为stu2SELECT*INTOstu2FROMstudent_infoGO--为stu2表创建一个INSERT型触发器stu_trCREATETRIGGERstu_trONstu2FORINSERTASDECLARE@maxchar(4)SET@max=(SELECTMAX(学号)FROMstu2)SET@max=@max+1UPDATEstu2SET学号=REPLICATE('0',4-len(@max))+@maxFROMstu2INNERJOINinsertedonstu2.学号=inserted.学号执行以上代码，查看studentsdb数据库中是否有stu2表，展开stu2，查看其触发器项中是否有stu_str触发器。在查询设计器的编辑窗口输入以下代码：INSERTINTOstu2(学号,姓名,性别)VALUES('0001','张主','女')运行以上代码，查看stu2表的变化情况，为什么插入记录的学号值发生了改变？答:因为触发器中,设置要插入的学号为已插入数据中最大学号值+1,一旦插入就执行（8）为grade表建立一个名为insert_g_tr的INSERT触发器，当用户向grade表中插入记录时，如果插入的是在curriculum表中没有的课程编号，则提示用户不能插入记录，否则提示记录插入成功。在进行插入测试时，分别输入以下数据：学号课程编号分数00040003760005000769观察插入数据时的运行情况，说明为什么？（9）为curriculum表创建一个名为del_c_tr的DELETE触发器，该触发器的作用是禁止删除curriculum表中的记录。（10）为student_info表创建一个名为update_s_tr的UPDATE触发器，该触发器的作用是禁止更新student_info表中的“姓名”字段的内容。（11）使用Transact-SQL语句DROPTRIGGER删除update_s_tr触发器。