第7章SQL-SERVER储过程与触发器

第7章存储过程和触发器第7章SQLServer存储过程和触发器任课教师：郭黎明第7章存储过程和触发器7.2存储过程的使用[应用实例1]统计每一个学生的平均成绩,要求显示学生姓名和平均成绩。分析:关联查询和分组查询使用T-SQL语句实现：SELECTsname,avg(grade)FROMsc,studentWHEREsc.sno=student.snoGROUPBYsname第7章存储过程和触发器7.1存储过程的使用[应用实例1]统计学生所选课程所得的总学分,要求显示系科名,学号,姓名和总学分。使用存储过程实现：CREATEPROCEDUREAvg_of_GradeASSELECTsname,avg(grade)FROMsc,studentWHEREsc.sno=student.snoGROUPBYsname在查询分析器中运行过程：EXECAvg_of_Grade第7章存储过程和触发器7.1存储过程概述存储过程的概念存储过程是SQLServer服务器上一组预先定义并编译好的Transact-SQL语句，它可以接受参数，返回状态值和参数值。存储过程应用涉及服务器处理多，而与用户较少的程序存储过程的分类系统存储过程：sp开头SP_database本地存储过程临时存储过程远程存储过程扩展存储过程第7章存储过程和触发器7.1存储过程概述存储过程的优点模块化的程序设计创建一个存储过程存放在数据库中后，就可以被其他程序反复使用快速执行－当存储过程被编译并存储在高速缓冲区中时，它能以相当高的效率执行。减少网络通信量－时常会有长达数百行的SQL语句。如果能把那些功能封装进存储过程，就可以通过向SQLServer发送一行代码起到成百行的语句的作用，从而节省大量的网络带宽安全性－存储过程能作为一种安全性机制使用。当你授权许可一个用户或一组用户使用存储过程，他们将能够执行这个存储过程而不必有访问在存储过程中被访问的下层对象的许可。第7章存储过程和触发器7.2存储过程的使用存储过程创建存储过程的执行存储过程修改存储过程删除第7章存储过程和触发器7.2存储过程的使用存储过程创建存储过程的创建方法①利用SQLServer企业管理器创建存储过程。②使用Transact-SQL语句中的CREATE。③使用创建存储过程向导创建存储过程。存储过程的组成①所有的输入参数以及传给调用者的输出参数。②被执行的针对数据库的操作语句，包括调用其它存储过程的语句。③返回给调用者的状态值，以指明调用是成功还是失败。第7章存储过程和触发器7.2存储过程的使用存储过程创建使用Transact-SQL语句中的CREATE。CREATEPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n]WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]第7章存储过程和触发器7.2存储过程的使用存储过程创建procedure_name：用于指定要创建的存储过程的名称。number：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起除去。@parameter：过程中的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。data_type：用于指定参数的数据类型。VARYING：用于指定作为输出OUTPUT参数支持的结果集。Default：用于指定参数的默认值。OUTPUT：表明该参数是一个返回参数。第7章存储过程和触发器7.2存储过程的使用存储过程创建【实例1－－无参数的存储过程】：统计学生所选课程所得的总学分,要求显示系科名,学号,姓名和总学分。分析:关联查询和分组查询CREATEPROCEDURETotal_of_CreditASSELECTdname,student.sno,sname,sum(ccredit)FROMstudent,sc,course,deptWHEREstudent.sno=sc.snoANDcourse.cno=sc.cnoanddept.dno=student.dnoGROUPBYdname,student.sno,student.snameEXECTotal_of_CreditTotal_of_Credit第7章存储过程和触发器7.2存储过程的使用存储过程创建【实例2----带输入参数的存储过程】】建立一存储过程，用户输入学生姓名，学号，如果student表中存在此项，则删除后再插入，若不存在此学生，则直接插入该记录。说明：exists(select_statement)函数是指select语句是否能返回行，如果能返回，则为真，否则为假。分析:显然学号和姓名应该作为存储过程的传入参数第7章存储过程和触发器7.2存储过程的使用【Ins_Del_S】代码CREATEPROCEDUREIns_Del_S@snoASCHAR(6),@snameASCHAR(10)ASIFEXISTS(SELECT*FROMstudentWHEREsno=@sno)BEGINDELETEstudentWHEREsno=@snoINSERTINTOstudent(sno,sname)VALUES(@sno,@sname)ENDELSEINSERTINTOstudent(sno,sname)VALUES(@sno,@sname)GOexecins_del_s'1244','df'第7章存储过程和触发器7.2存储过程的使用存储过程创建【实例3－－带输出参数的存储过程】：根据用户键入的学生姓名，查询学生的年龄。CREATEPROCEDUREGetage@nameCHAR(8)=NULL,@ageINTOUTPUTASIF@name=NULLBEGINPRINT'查找哪个同学的年龄'RETURNENDSELECT@age=YEAR(Getdate())-YEAR(sbirthday)FROMstudentWHEREsname=@nameRETURNDECLARE@ageINTEXECGetage'李一名',@ageOUTPUTSELECT@age第7章存储过程和触发器7.2存储过程的使用存储过程创建【实例4－－通过Return参数返回状态】：创建一个存储过程,检查某学生的选课的门数RETURN语句返回的是整形值CREATEPROCEDURESelCount@snoCHAR(8)=NULLASDECLARE@countintegerSELECT@count=count(*)FROMscWHEREsno=@snoRETURN@countdeclare@countintegerexecute@count=selcount'95001'print@count第7章存储过程和触发器7.2存储过程的使用存储过程的执行直接执行存储过程可以使用EXECUTE命令来执行，其语法形式如下：[[EXEC[UTE]]{[@return_status=]{procedure_name[;number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,...n][WITHRECOMPILE]。第7章存储过程和触发器7.2存储过程的使用存储过程的修改使用Transact-SQL语句中的ALTER。ALTERPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n]WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]第7章存储过程和触发器7.2存储过程的使用存储过程的删除使用Transact-SQL语句中的DROP。DROPPROC[EDURE]procedure_name第7章存储过程和触发器7.2存储过程的使用存储过程的错误处理可以使用@@ERROR捕捉错误代码,@@ERROR在执行每一个T-SQL语句之后都会得到一个值,对于成功的执行,@@ERROR的值为0,如果出现错误@@ERROR的值大于0CREATEPROCEDUREINSERT_STUDENT@snochar(5),@snamechar(10),@ssexchar(2)asinsertintostudent(sno,sname,ssex)values(@sno,@sname,@ssex)if@@error=0print'执行成功'elseprint'执行失败'print@@error第7章存储过程和触发器练习综合练习1.根据用户键入的学生学号和课程号，查询学生的成绩。CREATEPROCGetgrade@snoVARCHAR(10),@cnoVARCHAR(10)ASSELECTstudent.sno,sname,cno,gradeFROMscWHEREsno=@snoandcno=@cno第7章存储过程和触发器练习二、存储过程综合练习2.建立一存储过程，使用户输入系别，查询该系学生选择各课程的情况。（模糊查询）列出系别、课程号以及对应的人数，并按系别排序。【Sel_Course】3.建立一存储过程，使用户输入课程名时，列出该课程名、对应的课程号、选择该课程的学生总人数、平均分【Sel_Stu】第7章存储过程和触发器参考答案二、存储过程综合练习【2】CREATEPROCEDURESel_Course@dnoASCHAR(2)ASSELECTdept.dno,dname,sc.cno,count(*)FROMsc,dept,studentWHEREsc.sno=student.snoANDstudent.dno=dept.dnoANDdept.dno=@dnoGROUPBYdept.dno,dname,sc.cnoGO第7章存储过程和触发器参考答案二、存储过程综合练习【3】CREATEPROCEDURESel_Stu@cnameASCHAR(20)ASSELECTsc.cno,cname,count(*),avg(grade)FROMsc,courseWHEREsc.cno=course.cnoANDcname=@cnameGROUPBYsc.cno,cnameGO第7章存储过程和触发器7.3触发器7.3.1触发器概述问题的引入触发器的概念触发器的原理触发器的分类7.3.2触发器的创建,删除应用示例第7章存储过程和触发器7.3.1触发器概述问题的引入在[学生管理数据库]中,某学生的[选课门数]是一个经常访问的属性,因此在student表中增加一个属性sselnum(smallint）．在实际应用实例展示增加一个记录删除一个记录(“95003”,”002”,56950010069041第7章存储过程和触发器7.3.1触发器概述问题的引入考虑该问题涉及两个表之间的关系，可否用参照完整性来实现？可否用嵌套的SQL语句来实现?updatestudentsetsselnum=(selectcount(*)fromscwheresc.sno=student.sno)存在的问题问题的解决：使用触发器trigger触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性第7章存储过程和触发器7.3.1触发器概述触发器的概念触发器是一种特殊类型的存储过程。在SQLServer里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程