SQL Server之存储过程

存储过程的概念

存储过程PRocedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。

存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、简单网络负担。

--===========系统存储过程==============--显示系统数据库exec sp_databases--显示数据库详细信息exec sp_helpdb--给指定的数据库更换名称exec sp_renamedb 'aa','bb'--查看指定表名的详细信息exec sp_help student--查看指定索引、视图、存储过程等的创建文本信息exec sp_helptext sp_help--调用存储过程必须在批处理文件第一位if exists(select * from sysobjects where name = 'Table1')    drop table Table1go    sp_help sp_help--==========系统扩展存储过程================use mastergo--创建文件夹bankexec xp_cmdshell 'mkdir D:/bank',no_output    if exists(select * from sysdatabases where name = 'bankDB')        drop database bankDB    go        create database bankDB    on primary    (        name = 'bankDB',        filename = 'D:/bank/bankDB.mdf',        size = 5MB,        maxsize = 10MB,        filegrowth = 15%        )log on(        name = 'bankDB_log',        filename = 'D:/bank/bankDB_log.ldf',        size = 5MB,        filegrowth = 15%    )    --调用储存过程查看文件夹信息exec xp_cmdshell 'dir D:/bank/'--========创建存储过程，查询java Logic最近一次考试平均分以及未通过考试的学员名单=========use MySchoolgoif exists(select * from sysobjects where name = 'sp_getavgresult')drop proc sp_getavgresultgo--创建存储过程实现create proc sp_getavgresult    @returnnum int output, --返回未及格的人数    @returnsum int output, --参加考试总人数    @subjectName varchar(30), --添加科目    @score int = 60 --添加输入参数（及格分数）asdeclare @subjectId intdeclare @maxdate datedeclare @avg int--查询java logic课程的编号select @subjectId = SubjectId from Subject where SubjectName = @subjectName--查询java logic课程最近一次考试时间select @maxdate = MAX(ExamDate) from Result where SubjectId = @subjectId--查询java logic课程最近一次考试的平均分select @avg = AVG(StudentResult) from Result where     SubjectId = @subjectId and ExamDate = @maxdateprint '未通过考试的人员名单：======================='--查询java logic课程最近一次考试未通过的学生名单select studentName,studentResult from student s inner join Result r on r.StudentNo = s.StudentNo        where SubjectId = @subjectId         and ExamDate = @maxdate        and StudentResult < @score        --查询参加考试的总人数select @returnsum = COUNT(*) from Result where ExamDate = @maxdate and SubjectId = @subjectId--查询未及格的人数        select @returnnum = COUNT(*) from Result where ExamDate = @maxdate and SubjectId = @subjectId                                  and StudentResult < @score        if(@avg > 70)begin    print '考试结果：优秀'endelsebegin    print '考试结果：较差'endgo--=======调用储存过程实现业务逻辑===========----declare @sum int --参加考试总人数declare @num int --未及格人数declare @percent float(2) --及格百分比--调用存储过程exec sp_getavgresult @num output,@sum output,@subjectName = 'java logic',@score = 60print '========================================='print '参加考试人数为：' + convert(varchar(30),@sum)print '未及格人数为：' + convert(varchar(30),@num)--计算及格率set @percent = convert(float(2),(@sum - @num))/@sum * 100print '及格百分比：' + convert(varchar(30),@percent) +'%'--判断是否要调及格分数线if(@percent > 50)begin    print '不需要调分数线。。。'endelsebegin    print '需要降低分数线。。。'end