周五了，祝大家周末愉快。
之前一直在写SQLserver的分享，今天再来个T-SQL编程中的存储过程。
存储过程
存储过程（procedure）类似于C语言中的函数，用来执行管理任务或应用复杂的业务规则，存储过程可以带参数，也可以返回结果。
存储过程可以包含数据操纵语句、变量、逻辑 控制语句等，比如：单个select语句，select语句块，select语句与逻辑控制块。
存储过程优点：
执行速度更快
允许模块化程序设计
提高系统安全性
减少网络流通量
存储过程分类
系统存储过程
由系统定义，存放在master数据库中
类似C语言中的系统函数
系统存储过程的名称都以“sp_”开头或”xp_”开头
用户自定义存储过程
由用户在自己的数据库中创建的存储过程
类似C语言中的用户自定义函数
常用的系统存储过程如下表所示：
存储过程都是用exec调用，调用上述表中存储过程如下：
EXEC sp_databasesEXEC sp_renamedb 'Northwind','Northwind1'USE stuDBGOEXEC sp_tablesEXEC sp_columns stuInfo EXEC sp_help stuInfoEXEC sp_helpconstraint stuInfoEXEC sp_helpindex stuMarksEXEC sp_helptext 'view_stuInfo_stuMarks' EXEC sp_stored_procedures
常用的扩展存储过程：xp_cmdshell，可以执行DOS命令下的一些的操作，以文本行方式返回任何输出
调用语法：
EXEC xp_cmdshell DOS命令 [NO_OUTPUT]
扩展存储过程举例：
USE masterGOEXEC xp_cmdshell 'mkdir d:\bank', NO_OUTPUTIF EXISTS(SELECT * FROM sysdatabases WHERE name='bankDB')DROP DATABASE bankDBGOCREATE DATABASE bankDB(… --创建数据库语法略)GOEXEC xp_cmdshell 'dir D:\bank\' --查看文件
创建存储过程
定义存储过程的语法
CREATE PROC[EDURE] 存储过程名 @参数1 数据类型 = 默认值 OUTPUT,…… ,@参数n 数据类型 = 默认值 OUTPUTASSQL语句GO
存储过程和C语言的函数一样，参数可选，参数分为输入参数、输出参数，输入参数允许有默认值。我们来看一个例子：
例：请创建存储过程，查看本次考试平均分以及未通过考试的学员名单
CREATE PROCEDURE proc_stuASDECLARE @writtenAvg float,@labAvg floatSELECT @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<60 GO
存储过程的参数分为两种：输入参数和输出参数
输入参数
用于向存储过程传入值，类似C语言的按值传递
例：
CREATE PROCEDURE proc_stu @writtenPass int, @labPass int ASprint '--------------------------------------------------' print ' 参加本次考试没有通过的学员：'SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfoINNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNoWHERE writtenExam<@writtenPass OR labExam<@labPass GO
调用带输入参数存储过程：
EXEC proc_stu 60,55 或者：EXEC proc_stu @labPass=55,@writtenPass=60
同同样，还可以设置默认值，如下：
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<@writtenPass OR labExam<@labPass GO
调用带默认参数的存储过程：
EXEC proc_stu --都采用默认值 EXEC proc_stu 64 --机试采用默认值 EXEC proc_stu 60,55 --都不采用默认值 或者：EXEC proc_stu @labPass=55
输出参数
如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（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 stuMarks ON stuInfo.stuNo=stuMarks.stuNoWHERE writtenExam<@writtenPass OR labExam<@labPass SELECT @notpassSum=COUNT(stuNo) FROM stuMarks WHERE writtenExam<@writtenPass OR labExam<@labPass GO
调用带输出参数的存储过程
DECLARE @sum int EXEC proc_stu @sum OUTPUT ,64 print '--------------------------------------------------'IF @sum>=3print '未通过人数：'+convert(varchar(5),@sum)+ '人, 超过60%,及格分数线还应下调'ELSEprint '未通过人数：'+convert(varchar(5),@sum)+ '人,已控制在60%以下，及格分数线适中'GO
注意接收存储过程返回值时必须加output关键字
删除存储过程：drop proc p_test4
处理存储过程中的错误
RAISERROR 显示用户定义的错误信息时，可指定严重级别，设置系统变量@@ERROR，记录所发生的错误等。
语法如下：
RAISERROR (msg_id | msg_str,severity,state WITH option[,...n]])
msg_id：在sysmessages系统表中指定用户定义错误信息
msg_str：用户定义的特定信息，最长255个字符
severity：定义严重性级别。用户可使用的级别为0–18级
state：表示错误的状态，1至127之间的值
option：指示是否将错误记录到服务器错误日志中
例：
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)BEGINRAISERROR ('及格线错误，请指定0－100之间的分 数，统计中断退出',16,1)RETURN ---立即返回，退出存储过程END…..其他语句同上例，略GO
调用该存储过程：
DECLARE @sum int, @t intEXEC proc_stu @sum OUTPUT ,604 --及格线设置为604出错SET @t=@@ERROR print '错误号：'+convert(varchar(5),@t )IF @t<>0 RETURN --退出批处理，后续语句不再执行print '--------------------------------------------------'IF @sum>=3print '未通过人数：'+convert(varchar(5),@sum)+ '人,超过60%,及格分数线还应下调'ELSEprint '未通过人数：'+convert(varchar(5),@sum)+ '人,已控制在60%以下，及格分数线适中'GO
本文仅代表作者观点，系作者@温一壶清酒发表。 欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。 文章出处：/hong-fithing/
