存储过程&触发器上机

数据库基础及应用存储过程&触发器上机环境准备创建Students数据库创建Student、Course、SC数据表向三张表中添加数据存储过程的好处改善性能实现了模块化编程，一个存储过程可以被多个用户共享和重用。屏蔽数据库模式的详细资料，提高数据安全性用户不需要访问底层的数据库和数据库内的对象封装商务逻辑若规则或策略有变化，则只需要修改服务器上的存储过程，所有的客户端就可以直接使用减少网络通信量创建存储过程两种方式创建存储过程：按右图所示创建存储过程，窗口显示存储过程的模板，可自行修改代码。点击“新建查询”按钮，在窗口中输入代码创建存储过程。查询计算机系的学生-无参数USEstudentsGOCREATEPROCSomeDeptASSELECTsno,sname,ssex,sage,sdeptFROMstudentWHEREsdeptin('计算机系')GO------------------------------------------execSomeDept查询指定系的学生-1个输入参数USEstudentsGOCREATEPROCS_dept@deptnchar(10)ASselect*fromstudentwheresdept=@deptGO-----------------------------------execS_dept@dept='计算机系'查询指定系和年龄的学生-2个输入参数USEstudentsGOCREATEPROCS_dept_age@deptnchar(10),@ageintASselect*fromstudentwheresdept=@deptandsage=@ageGO------------------------------------------execS_dept_age@dept='计算机系',@age=20返回指定系的学生人数-1个输出参数USEstudentsGOCREATEPROCDept_Num@deptnchar(10),@NumintoutputASselect@Num=count(sdept)fromstudentwheresdept=@dept;GO---------------------------------------------------declare@numberintexecDept_Num'计算机系',@numberoutputprint@number存储过程的练习1创建一个带参数的存储过程，输入参数为课程名称，查询有哪些学生选修了这门课程及学生的考试成绩。存储过程的练习2在每个学年结束时，规定对每个学生进行学籍审查，审查时比较每个学生的应修学分和已修学分，若已修学分小于应修学分，则该学生的学籍审查不合格。每个院系的应修学分不同，但属于同一个院系的学生，其应修学分一样。所以学籍审查时应给出“院系名称”和该院系“应修学分”两个参数。创建存储过程，完成该操作。解题思路：1、统计每个学生已修总学分（用视图实现，参考P72，3（4））2、创建存储过程，查询指定院系学籍审查不合格的学生信息和学生人数，分两个sql语句完成，用begin、end将两个sql语句封装起来。3、执行存储过程，两个输入参数（院系名称，应修学分）查看存储过程内容execsp_helptext存储过程名修改存储过程ALTERPROC存储过程名其语法与创建存储过程一样，只是把CREATE换成ALTER。删除存储过程DROPPROC存储过程名触发器触发器是一种特殊的存储过程。它不能通过名称被直接调用，而是在添加、修改、删除数据时，自动执行。它主要完成数据的完整性检查。创建触发器两种方式创建触发器按右图所示创建触发器，窗口显示触发器的模板，可自行修改代码。点击“新建查询”按钮，在窗口中输入代码创建触发器。创建INSERT触发器创建一个INSERT触发器insert_student_sex，当向student表插入记录时，检查输入的性别是否是’男’或’女’，若满足条件，则插入成功；若不满足条件，则插入失败。解题思路：USEStudentsGOCREATETRIGGERinsert_student_sexONstudentFORinsertASDECLARE@sexchar(2)select@sex=ssexfrominsertedif(@sex=‘男’or@sex=‘女’)print(‘记录插入成功’)elsebeginprint(‘性别只能为男或女，否则不能插入记录，插入中止！’)ROLLBACKTRANSACTIONendGO说明：触发器中的inserted表和deleted表当触发器被触发时，SQLServer2005为每个触发器在服务器内存中创建两个临时表：inserted表和deleted表。这两个临时表由系统自动维护而不允许用户修改。触发器工作完成后，这两个表随之被删除。它们的结构与触发器操作的表结构相同。说明：触发器中的inserted表和deleted表INSERT触发器被触发时，会创建一个inserted表。DELETE触发器被触发时，会创建一个deleted表。UPDATE操作可以分为一个DELETE操作加一个INSERT操作。因此，UPDATE触发器被触发时会同时创建deleted表和inserted表。创建INSERT触发器练习创建一个INSERT触发器insert_student_age，当向student表插入记录时，检查输入的年龄是否大于0且小于50，若满足条件，则插入成功；若不满足条件，则插入失败。创建UPDATE触发器创建一个UPDATE触发器update_sc_sno，当修改sc表的某条记录时，不允许修改sno，若修改sno，则修改失败。解题思路：USEStudentsGOCREATETRIGGERupdate_sc_snoONscFORupdateASifupdate(sno)beginprint(‘不能修改学号，修改中止！’)ROLLBACKTRANSACTIONendGO创建UPDATE触发器练习创建一个UPDATE触发器update_sc_cno，当修改sc表的某条记录时，不允许修改cno，若修改cno，则修改失败。创建DELETE触发器创建一个DELETE触发器delete_student，当删除student表某条记录时，若选课表（sc）引用了该条记录的sno，则删除失败；否则删除成功。解题思路：USEStudentsGOCREATETRIGGERdelete_studentONstudentFORdeleteASif(selectcount(*)fromscjoindeletedonsc.sno=deleted.sno)0beginprint(‘该学号被选课表引用，不能删除该记录，删除中止！’)ROLLBACKTRANSACTIONendelseprint(‘记录已删除’)GO创建DELETE触发器练习创建一个DELETE触发器delete_course，当删除course表某条记录时，若选课表（sc）引用了该条记录的cno，则删除失败；否则删除成功。查看触发器内容EXECsp_helptrigger触发器名execsp_helptext触发器名修改触发器ALTERTRIGGER触发器名其语法与创建触发器一样，只是把CREATE换成ALTER。删除触发器DROPTRIGGER触发器名存储过程练习1解题思路：USEstudentsGOCREATEPROCkc@cnamenchar(10)ASselectsname,cname,gradefromstudentsjoinscons.sno=sc.snojoincourseconc.cno=sc.cnowherecname=@cnameGO------------------------------------------------execkc'VB'存储过程练习2解题思路：第一步：统计每个学生已修总学分createviewv_total_credit(sno,total_credit)asselectsno,sum(credit)fromcoursecjoinsconc.cno=sc.cnowheregrade=60groupbysno存储过程练习2解题思路：第二步：创建存储过程CREATEPROCxj@deptnchar(10),@creditint,@NumintoutputASbegin--查询指定系已修学分小于应修学分的学生信息，即学籍审查不合格学生信息selects.sno,sname,ssex,sage,sdept,total_creditfromstudentsjoinv_total_creditvons.sno=v.snowheresdept=@deptandtotal_credit@credit--统计指定系已修学分小于应修学分的学生人数，即学籍审查不合格学生人数select@Num=count(*)fromstudentsjoinv_total_creditvons.sno=v.snowheresdept=@deptandtotal_credit@creditendGO存储过程练习2解题思路：第三步：执行存储过程declare@numberintexecxj'计算机系',20,@numberoutputprint@number