SQL server学习（五）T-SQL编程之存储过程

chj0771

　　周五了，祝大家周末愉快。
　　之前一直在写SQL server的分享，今天再来个T-SQL编程中的存储过程。

存储过程

存储过程（procedure）类似于C语言中的函数，用来执行管理任务或应用复杂的业务规则，存储过程可以带参数，也可以返回结果。

存储过程可以包含数据操纵语句、变量、逻辑 控制语句等，比如：单个select语句，select语句块，select语句与逻辑控制块。

存储过程优点：　

　　执行速度更快

　　允许模块化程序设计

　　提高系统安全性

　　减少网络流通量

存储过程分类

系统存储过程

　　由系统定义，存放在master数据库中

　　类似C语言中的系统函数

　　系统存储过程的名称都以“sp_”开头或”xp_”开头

用户自定义存储过程

　　由用户在自己的数据库中创建的存储过程

　　类似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调用，调用上述表中存储过程如下：
　　

EXEC sp_databases　　

EXEC sp_renamedb 'Northwind','Northwind1'　　
USE stuDB
　　
GO
　　
EXEC sp_tables
　　
EXEC sp_columns stuInfo  
　　
EXEC sp_help stuInfo
　　
EXEC sp_helpconstraint stuInfo
　　
EXEC sp_helpindex stuMarks
　　
EXEC sp_helptext 'view_stuInfo_stuMarks'
　　
EXEC sp_stored_procedures  
　　

　　常用的扩展存储过程：xp_cmdshell，可以执行DOS命令下的一些的操作，以文本行方式返回任何输出
　　调用语法：
　　

EXEC xp_cmdshell DOS命令 [NO_OUTPUT]　　

　　扩展存储过程举例：
　　

USE master　　

GO　　
EXEC xp_cmdshell 'mkdir d:\bank', NO_OUTPUT
　　
IF EXISTS(SELECT * FROM sysdatabases WHERE name='bankDB')
　　DROP DATABASE bankDB
　　
GO
　　
CREATE DATABASE bankDB
　　(
　　…   
　　
)
　　
GO
　　
EXEC xp_cmdshell 'dir D:\bank\'
　　


创建存储过程
　　定义存储过程的语法
　　

CREATE  PROC[EDURE]  存储过程名@参数1  数据类型 = 默认值 OUTPUT,　　…… ,
@参数n  数据类型 = 默认值 OUTPUTAS　　SQL语句
　　

GO　　

　　存储过程和C语言的函数一样，参数可选，参数分为输入参数、输出参数，输入参数允许有默认值。我们来看一个例子：
　　例：请创建存储过程，查看本次考试平均分以及未通过考试的学员名单
　　

CREATE PROCEDURE proc_stuAS　　DECLARE @writtenAvg float,@labAvg float
　　
SELECT @writtenAvg=AVG(writtenExam),@labAvg=AVG(labExam)  
　　
FROM stuMarks
　　print '笔试平均分：'+convert(varchar(5),@writtenAvg)  
　　print '机试平均分：'+convert(varchar(5),@labAvg)
　　IF (@writtenAvg>70 AND @labAvg>70)
　　print '本班考试成绩：优秀'
　　ELSE
　　print '本班考试成绩：较差'
　　print '--------------------------------------------------'
　　print '           参加本次考试没有通过的学员：'
　　SELECT stuName,stuInfo.stuNo,writtenExam,labExam
　　FROM  stuInfo  INNER JOIN stuMarks ON  stuInfo.stuNo=stuMarks.stuNo
　　WHERE writtenExam<60 OR labExam<60
　　
GO
　　


存储过程的参数分为两种：输入参数和输出参数

输入参数
　　用于向存储过程传入值，类似C语言的按值传递
　　例：
　　

CREATE PROCEDURE proc_stu@writtenPass int,@labPass int　　AS
　　print '--------------------------------------------------'
　　print '           参加本次考试没有通过的学员：'
　　
SELECT stuName,stuInfo.stuNo,writtenExam,labExam  
　　
FROM  stuInfo
　　INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
　　WHERE 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　　AS
　　print '--------------------------------------------------'
　　print '           参加本次考试没有通过的学员：'
　　
SELECT stuName,stuInfo.stuNo,writtenExam,labExam  
　　
FROM  stuInfo
　　INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
　　WHERE 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.stuNo
　　WHERE 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>=3
　　print '未通过人数：'+convert(varchar(5),@sum)+ '人, 超过60%,及格分数线还应下调'
　　
ELSE
　　print '未通过人数：'+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      
　　AS
　　IF (NOT @writtenPass BETWEEN 0 AND 100)
　　OR (NOT @labPass BETWEEN 0 AND 100)
　　BEGIN
　　RAISERROR ('及格线错误，请指定0－100之间的分
　　数，统计中断退出',16,1)
　　RETURN  
　　END
　　…..其他语句同上例，略
　　
GO
　　

　　调用该存储过程：
　　

DECLARE @sum int,  @t int　　
EXEC proc_stu @sum OUTPUT ,604   
　　
SET @t=@@ERROR
　　
print  '错误号：'+convert(varchar(5),@t )
　　
IF @t<>0  
　　RETURN  
　　
print '--------------------------------------------------'
　　
IF @sum>=3
　　print '未通过人数：'+convert(varchar(5),@sum)+ '人,超过60%,及格分数线还应下调'
　　
ELSE
　　print '未通过人数：'+convert(varchar(5),@sum)+ '人,已控制在60%以下，及格分数线适中'
　　
GO
　　

　　

本文仅代表作者观点，系作者@温一壶清酒发表。　　欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
　　文章出处：http://www.cnblogs.com/hong-fithing/