数据库：SQL存储过程

1、存储过程,概述,问题要把某完成功能的SQL做成类似C语言的函数，供需要时调用，如何做？什么是存储过程？是一组被编译在一起的T-SQL语句的集合，它们被集合在一起以完成一个特定的任务。存储过程的分类系统存储过程扩展存储过程（提供从SQLServer到外部程序的接口，以便进行各种维护活动）用户自定义的存储过程,存储过程的作用,从以下几个方面考虑：模块化编程：创建一个存储过程存放在数据库中后，就可以被其他程序反复使用。快速执行：存储过程第一次被执行后，就驻留在内存中。以后执行就省去了重新分析、优化、编译的过程。减少网络通信量有了存储过程后，在网络上只要一条语句就能执行一个存储过程。安全机制通过隔离和

2、加密的方法提高了数据库的安全性，通过授权可以让用户只能执行存储过程而不能直接访问数据库对象。,存储过程和视图的比较,创建、执行简单的存储过程,创建格式：CREATEPROCEDUREproc_nameASSql_statements【问题】创建一个名为math_Student的存储过程，返回student表中数学系的学生信息执行格式：EXEC|EXECUTEproc_name,创建、执行简单的存储过程,创建格式：CREATEPROCEDUREproc_nameASSql_statementsuse学生选课gocreateproceduremath_studentasselect*fromStu

3、dentwhereSdept=ma,练习1,创建一个名为StuBy2009的存储过程，返回2009级的所有学生的信息执行该存储过程，并使用管理器查看该存储过程的定义,创建带输入参数的存储过程,创建格式：CREATEPROCEDUREproc_namepara_namedatatype=default,ASSql_statements【例】创建一个名为StuBySno的存储过程，该存储过程根据给定的学号显示相应学生的信息,创建带输入参数的存储过程,创建格式：CREATEPROCEDUREproc_namepara_namedatatype=default,ASSql_statementsuse学

4、生选课gocreateprocedureStuBySnoStudentSnochar(10)asselect*fromStudentwhereSNO=StudentSno,创建带输入参数的存储过程,【例】创建一个名为StuBySno的存储过程，该存储过程根据给定的学号显示相应学生的信息CREATEprocedureStuBySnoStudentSnochar(10)=NULLasIFStudentSnoISNULLBEGINPRINT必须提供一个数值作参数！RETURN13ENDIFNOTEXISTS(select*fromStudentwhereSNO=StudentSno)BEGINPRI

5、NT没有满足条件的记录！RETURN-103ENDselect*fromStudentwhereSNO=StudentSnoRETURN0,执行带输入参数的存储过程,执行格式：EXECproc_namepara_name=value,指定参数名的格式不指定参数名的格式思考：当缺省参数执行存储过程时，如何处理？,练习2,创建一个名为ScBySnoCno的存储过程，根据给定的学生学号和课程号，显示相应学生的选课信息use学生选课gocreateprocedureScBySnoCnoStudentSnochar(10),CourseCnoaschar(10)asselect*fromSCwhereS

6、NO=StudentSnoandCno=CourseCno,带输出参数的存储过程,定义格式同上，同时指明OUTPUT关键字CREATEPROCEDUREproc_namepara_namedatatype=defaultoutput,ASSql_statements执行方法：用output指明输出参数【例】创建存储过程P_DeptNum，要求能根据用户给定的系代码，统计该系的人数，并将人数通过输出变量返回给用户。如要查询纱代码为MA的学生人数，如何调用存储过程,带输出参数的存储过程,定义：use学生选课gocreateprocedureP_DeptNumDeptIdchar(15),DeptN

7、umsmallintOUTPUTasselectDeptNum=COUNT(*)fromStudentwhereSdept=DeptId调用：DECLARENUMSMALLINTEXECP_DeptNumDEPTID=MA,DEPTNUM=NUMOUTPUTPRINTNUM,【练习】创建存储过程Cou_credit，要求能根据用户给定的学分值，统计满足该学分值的课程数目，并把它返回给调用程序。如要统计2个学分的课程门数，该如何调用上述存储过程。【练习】创建名为mod_credit的存储过程，能修改用户指定课程号的课程学分值，修改值也由用户指定。,修改存储过程,修改格式：ALTERPROCEDUREproc_namepara_namedatatype=default,WITHENCRYPTIONASSql_statements,重命名、删除存储过程,重命名：sp_rename原存储过程名,新存储过程名删除格式：DROPPROCEDUREproc_name,重编译处理（RECOMPILE）,在创建时指明重编译添加WITHRECOMPILE选项在执行时指明重编译使用EXECUTE时指定WIT