13_存储过程

回顾约束主键约束、外键约束、唯一约束、检查约束、默认约束、非空约束触发器有INSTEADOF触发器和AFTER触发器两种。当进行增删改操作时，先执行INSTEADOF触发器，再进行约束验证，再执行AFTER触发器。实验讲解使用替代触发器实现通过视图更新多表数据数据库原理与应用第13讲存储过程提纲重点对存储过程优点的理解创建和执行存储过程的方法难点存储过程的输入参数、输出参数和状态值1.存储过程关键词：预定义的，被存储的SQL批处理。(1)存储过程的概念是一组预编译的Transact-SQL语句，用于完成某项任务，它可以接受参数、返回状态值和参数值，并且可以嵌套调用。(2)存储过程的优点(1)模块化编程。一次创建，重复调用。(2)快速执行。创建存储过程（大量T-SQL代码），再执行不用再分析，优化和编译(3)减少网络通信量。几百条T-SQL语句组成的存储过程，执行仅用一条语句在网络上传输。(4)提供安全机制。对语句没有权限的用户，可以授权执行含该语句的存储过程。(5)保证操作一致性。封装查询，保持功能一致性。(3)SQLServer存储过程的类型：系统存储过程由系统提供的存储过程，主要存储在master数据库中并以sp_为前缀用户定义存储过程由用户创建并能完成某一特定功能(例如查询用户所需数据信息)的存储过程。它处于用户创建的数据库中临时存储过程在一个SQLSERVER实例中有效，重启后会消失。扩展存储过程。扩展存储过程是SQLServer可以动态装载和执行的动态链接库(DLL)。当扩展存储过程加载到SQLServer中，它的使用方法与系统存储过程一样。扩展存储过程只能添加到master数据库中，其前缀是xp_。(4)存储过程的创建、修改、删除与执行创建CREATEPROC存储过程名参数表[With{RECOMPILE|ENCRYPTION}]ASSQLSTATEMENT其中：RECOMPILE---执行时重新编译，ENCRPTION---对存储过程脚本加密。执行EXECUTE存储过程名参数表修改存储过程ALTERPROC存储过程名删除存储过程DROPPROC存储过程名2.存储过程创建与执行示例关键词：PROC，@，OUTPUT学号课程号成绩学号姓名性别班号课程号课程名学分学生表课程表成绩表(1)示例数据库Teach数据库包含三个表，表结构如下：(1)无参数的存储过程。从学生表和成绩表的连接中返回‘0102200114’学生的学号，姓名，课程编号，成绩的存储过程。创建CREATEPROCproc_GetAllScoreASSELECTa.学号,a.姓名,b.课程号,b.成绩FROM学生表aJOIN成绩表bONa.学号=b.学号Where学号=‘0102200114’执行EXECproc_GetAllScore10.2存储过程的创建与使用(2)带参数的存储过程存储过程和调用者之间通过参数交换数据，参数可分为：输入参数(input)和输出参数(output)。一个存储过程可以有多个参数，用“,”分隔。输入参数的定义格式：@参数名类型输出参数的定义格式：@参数名类型output执行对输入参数，在EXEC语句中用常量传值对输出参数，先要定义一个存储结果的参数，在EXEC语句中用OUTPUT显式声明它为输出项。带一个输入参数的存储过程举例:用于查询某个班级的选课信息的存储过程CREATEPROCEDUREproc_GetScoreBySid@sidchar(10)ASCREATEPROCproc_GetAllScoreASSELECTa.学号,a.姓名,b.课程号,b.成绩FROM学生表aJOIN成绩表bONa.学号=b.学号Where班号=@sid执行EXECproc_GetScoreBySid‘0102200114’练习：定义存储过程，实现当调用存储过程时向成绩表中插入数据。带多个输入参数的存储过程举例(1)：用于向成绩表插入一行的存储过程。创建CREATEPROCEDUREProc_AddScore@sidchar(10),@cidchar(6),@scoreintASINSERTINTO成绩表(学号,课程号,成绩)VALUES(@sid,@cid,@score)执行：EXECProc_AddScore‘0100200114’,‘NN1018’,83带多个输入参数的存储过程举例(2)：用于修改某学生某课程的成绩的存储过程。创建CREATEPROCEDUREProc_UpdateScore@sidchar(10),@cidchar(6),@scoreintASUPDATE成绩表SET成绩=@scoreWHERE学号=@sidand课程号=@cid执行：EXECProc_UpdateScore‘0100200114’,‘NN1018’,83带输入和输出参数的存储过程举例：用于获取某学生修读的总学分的存储过程。创建CREATEPROCEDUREProc_GETPointBySid@sidchar(10)，@pointintoutputASSELECT@point=sum(b.学分)FROM课程表ajoin成绩表bona.课程号=b.课程号WHEREa.学号=@sidPrint@sid+’修读总学分是：’+convert(char,@point)执行：DECLARE@pointintEXECProc_GetPointBySid‘0100200114’,@pointOUTPUT说明：在SQL语句体中生命变量，用DECLARE，SELECT用于显示查询结果。课堂练习：写存储过程Proc_ModifyCourse，用于修改一个给定了课程号的课程的课程名和学分。课程号课程名学分课程表3.存储过程的执行状态关键词：状态值是一个整数，0表示成功，其他的都用来表示执行错误。10.2.5存储过程参数与状态值（1）存储过程的状态值状态值表示过程是否成功执行，或者过程失败的原因。状态值是一个整数。当存储过程成功执行时，状态值0；当执行中发生错误，则会返回一个“-99~-1”之间的整数。这一功能是由系统提供的，通常用来捕捉错误原因。用户也可以在存储过程中自定义返回状态值，自定义状态值是大于0或小于-99的整数。用RETURN返回状态值。这种做法通常用来处理可预见的不正确执行方式。在执行存储过程时，可以用变量来存储状态值。格式：EXECUTE@status_var=procedure_name10.2.5存储过程参数与状态值自定义状态值举例：创建存储过程，输入学号和课程号，返回成绩。CREATEPROCproc_GetScoreBySidAndCid@sidchar(10)=NULL,@cidchar(6)=NULLASIF@sid=NULLor@cid=NULLRETURN15--用值15表示用户没有提供参数IFNOTEXISTS(SELECT*FROM学生表WHERE学号=@sid)RETURN-101--值-l01表示没有该学生IFNOTEXISTS(SELECT*FROM课程表WHERE学号=@cid)RETURN-102--值-102表示没有该课程SELECT*FROM成绩表WHERE学号=@sidAND课程号=@cidRETURN0--值0表示过程运行没有出错。执行存储过程，获取状态值举例，接上片。DECLARE@return_statusintEXEC@return_status=proc_GetScoreBySidAndCid‘0100200114’,‘NN1018’IF@return_status=15SELECT‘没有提供参数'ELSEIF@return_status=-101SELECT‘没有该学生’ELSEIF@return_status=-102SELECT‘没有该课程'总结1.存储过程是一组SQL语句和流程控制语句的集合以一个名字存储并作为一个单元处理。2.存储过程它可以接受输入参数和输出参数3.存储过程的执行返回状态值，状态值为0表示执行成功。