SQL Server创建存储过程

什么是存储过程？q 存储过程（procedure）类似于C语言中的函数q 用来执行管理任务或应用复杂的业务规则q 存储过程可以带参数，也可以返回结果q 存储过程可以包含数据操纵语句、变量、逻辑 控制语句等存储过程的优点(1)执行速度快。存储过程创建是就已经通过语法检查和性能优化，在执行时无需每次编译。存储在数据库服务器，性能高。(2)允许模块化设计。只需创建存储过程一次并将其存储在数据库中，以后即可在程序中调用该过程任意次。存储过程可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改 。(3)提高系统安全性。可将存储过程作为用户存取数据的管道。可以限制用户对数据表的存取权限，建立特定的存储过程供用户使用，完成对数据的访问。存储过程的定义文本可以被加密，使用户不能查看其内容。(4)减少网络流量：一个需要数百行Transact-SQL代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。存储过程的分类q 系统存储过程q 由系统定义，存放在master数据库中q 类似C语言中的系统函数q 系统存储过程的名称都以“sp_”开头或”xp_”开头q 用户自定义存储过程q 由用户在自己的数据库中创建的存储过程q 类似C语言中的用户自定义函数常用的系统存储过程

系统存储过程说明sp_databases列出服务器上的所有数据库。sp_helpdb报告有关指定数据库或所有数据库的信息sp_renamedb更改数据库的名称sp_tables返回当前环境下可查询的对象的列表sp_columns回某个表列的信息sp_help查看某个表的所有信息sp_helpconstraint查看某个表的约束sp_helpindex查看某个表的索引sp_stored_procedures列出当前环境中的所有存储过程。sp_password添加或修改登录帐户的密码。sp_helptext显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。EXEC sp_databases /*列出当前系统中的数据库*/EXEC sp_renamedb 'Northwind','Northwind1' /*修改数据库的名称(单用户访问, 最简单的办法就是执行SQL语句时关掉企业管理器)*/USE stuDBGOEXEC sp_tables /*当前数据库中查询的对象的列表*/EXEC sp_columns stuInfo /*返回某个表列的信息*/EXEC sp_help stuInfo /*查看表stuInfo的信息*/EXEC sp_helpconstraint stuInfo /*查看表stuInfo的约束*/EXEC sp_helpindex stuMarks /*查看表stuMarks的索引*/EXEC sp_helptext 'view_stuInfo_stuMarks' /*查看视图的语句文本*/EXEC sp_stored_procedures /*查看当前数据库中的存储过程*/常用的扩展存储过程q 常用的扩展存储过程：xp_cmdshellq 可以执行DOS命令下的一些的操作q 以文本行方式返回任何输出q 调用语法：q EXEC xp_cmdshell DOS命令 [NO_OUTPUT]USE masterGOEXEC xp_cmdshell 'mkdir d:/bank', NO_OUTPUT /*创建文件夹D:/bank*/IF EXISTS(SELECT * FROM sysdatabasesWHERE name='bankDB')DROP DATABASE bankDBGOCREATE DATABASE bankDB(…)GOEXEC xp_cmdshell 'dir D:/bank/' --查看文件 /*查看文件夹D:/bank*/ 如何创建存储过程？q 定义存储过程的语法CREATE PROC[EDURE] 存储过程名@参数1 数据类型 = 默认值,…… ,@参数n 数据类型 OUTPUTASSQL语句GOq 和C语言的函数一样，参数可选q 参数分为输入参数、输出参数q 输入参数允许有默认值创建不带参数的存储过程CREATE PROCEDURE proc_stu /* proc_stu为存储过程的名称*/ASDECLARE @writtenAvg float,@labAvg float /* 笔试平均分和机试平均分变量 */SELECT @writtenAvg=AVG(writtenExam),@labAvg=AVG(labExam) FROM stuMarksprint '笔试平均分：'+convert(varchar(5),@writtenAvg) print '机试平均分：'+convert(varchar(5),@labAvg)IF (@writtenAvg>70 AND @labAvg>70)print '本班考试成绩：优秀' /* 显示考试成绩的等级 */ELSEprint '本班考试成绩：较差'print '--------------------------------------------------'print ' 参加本次考试没有通过的学员：'SELECT stuName,stuInfo.stuNo,writtenExam,labExam /* 显示未通过的学员 */FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNoWHERE writtenExam<60 OR labExam<60GO调用存储过程q EXECUTE（执行）语句用来调用存储过程q 调用的语法EXEC 过程名 [参数]创建带参数的存储过程q 存储过程的参数分两种：q 输入参数q 输出参数q 输入参数：用于向存储过程传入值，类似C语言的按值传递;q 输出参数：用于在调用存储过程后，返回结果，类似C语言的按引用传递; 带输入参数的存储过程问题：修改上例：由于每次考试的难易程度不一样，每次 笔试和机试的及格线可能随时变化（不再是60分），这导致考试的评判结果也相应变化。分析：在述存储过程添加2个输入参数：@writtenPass 笔试及格线@labPass 机试及格线CREATE PROCEDURE proc_stu@writtenPass int = 60, /*输入参数：笔试及格线*/@labPass int = 60 /*输入参数：机试及格线*/ASprint '--------------------------------------------------'print ' 参加本次考试没有通过的学员：'SELECT stuName,stuInfo.stuNo,writtenExam, /*查询没有通过考试的学员*/labExam FROM stuInfoINNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNoWHERE writtenExam<@writtenPassOR labExam<@labPassGOq 调用带参数的存储过程假定本次考试机试偏难，机试的及格线定为55分，笔试及格线定为60分EXEC proc_stu 60,55 --或这样调用：EXEC proc_stu @labPass=55,@writtenPass=60带输出参数的存储过程q 如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（OUTPUT）参数了问题：修改上例，返回未通过考试的学员人数。CREATE PROCEDURE proc_stu@notpassSum int OUTPUT, /*输出（返回）参数：表示没有通过的人数*/@writtenPass int=60, /*推荐将默认参数放在最后*/@labPass int=60 AS……SELECT stuName,stuInfo.stuNo,writtenExam, /*统计并返回没有通过考试的学员人数*/labExam FROM stuInfo INNER JOIN stuMarksON stuInfo.stuNo=stuMarks.stuNoWHERE writtenExam<@writtenPassOR labExam<@labPassSELECT @notpassSum=COUNT(stuNo)FROM stuMarks WHERE writtenExam<@writtenPassOR labExam<@labPassGOq 调用带输出参数的存储过程/*---调用存储过程----*/DECLARE @sum int /*调用时必须带OUTPUT关键字 ，返回结果将存放在变量@sum中*/EXEC proc_stu @sum OUTPUT ,64 print '--------------------------------------------------'IF @sum>=3 /*后续语句引用返回结果*/print '未通过人数：'+convert(varchar(5),@sum)+ '人,超过60%,及格分数线还应下调'ELSEprint '未通过人数：'+convert(varchar(5),@sum)+ '人,已控制在60%以下，及格分数线适中'GO注意：调用时也必须跟随关键字OUTPUT，否则SQL Server将视为输入参数。处理存储过程中的错误q 可以使用PRINT语句显示错误信息，但这 些信息是临时的，只能显示给用户q RAISERROR 显示用户定义的错误信息时q 可指定严重级别，q 设置系统变量@@ERRORq 记录所发生的错误等q RAISERROR语句的用法如下：RAISERROR (msg_id | msg_str,severity,state WITH option[,...n]])•msg_id：在sysmessages系统表中指定用户定义错误信息•msg_str：用户定义的特定信息，最长255个字符•severity：定义严重性级别。用户可使用的级别为0–18级•state：表示错误的状态，1至127之间的值•option：指示是否将错误记录到服务器错误日志中RAISERROR 语句每个参数的详细讲解，可以查阅SQL帮助!问题：完善上例，当用户调用存储过程时，传入的及格线参数不在0～100之间时，将弹出错误警告，终止存储过程的执行。CREATE PROCEDURE proc_stu@notpassSum int OUTPUT, --输出参数@writtenPass int=60, --默认参数放后@labPass int=60 --默认参数放后ASIF (NOT @writtenPass BETWEEN 0 AND 100)OR (NOT @labPass BETWEEN 0 AND 100)/*引发系统错误，指定错误的严重级别16，调用状态为1（默认）,并影响@@ERROR系统变量的值 */BEGINRAISERROR (‘及格线错误，请指定0－100之间的分数，统计中断退出',16,1)RETURN ---立即返回，退出存储过程END…..其他语句同上例，略GO/*---调用存储过程，测试RAISERROR语句----*/DECLARE @sum int, @t intEXEC proc_stu @sum OUTPUT ,604 /*笔试及格线误输入604分*/SET @t=@@ERRORprint '错误号：'+convert(varchar(5),@t )IF @t<>0 /*如果执行了RAISERROR，系统全局@@ERROR将不等于0，表示出现错误*/RETURN --退出批处理，后续语句不再执行print '--------------------------------------------------'IF @sum>=3print '未通过人数：'+convert(varchar(5),@sum)+ '人,超过60%,及格分数线还应下调'ELSEprint '未通过人数：'+convert(varchar(5),@sum)+ '人,已控制在60%以下，及格分数线适中'GO好，我们来做个总结：•存储过程是一组预编译的SQL语句，它可以包含数据操纵语句、变量、逻辑控制语句等•存储过程允许带参数，参数分为：– 输入参数– 输出参数其中，输入参数可以有默认值。•输入参数：可以在调用时向存储过程传递参数，此类参数可用来向存储过程中传入值•输出参数从存储过程中返回（输出）值，后面跟随OUTPUT关键字•RAISERROR语句用来向用户报告错误