存储过程

使用 exec 执行sql语句 接收返回值：
declare @num int, @sqls nvarchar(4000) 
set @sqls='select @a=count(*) from user_guest ' 
exec sp_executesql @sqls,N'@a int output',@num output
print convert(varchar(10),@num)


数据类型转换：str(@int)。直接转换为（varchar类型）


什么是存储过程：
存储过程是Sql语句和控制语句的预编译集合，保存在数据库里，可由应用程序调用执行，而且允许用户声明变量、逻辑控制
语句及其他强大的编程功能。
存储过程可包含逻辑控制语句和数据操作语句，它可以接收参数、输出参数、返回单个或多个结果集及返回值。
存储过程可以只包含一条 select 语句，也可以包含一系列使用控制流的Sql语句。


使用存储过程有下列优点：
1.模块化程序设计：
只需创建一次存储过程并将其存储在数据库中，以后即可在程序中反复调用该存储过程。存储过程可由在数据库编程方面有专长的人
员创建，并可独立于程序源代码而单独修改


2.执行速度快，效率高：
如果某操作需要大量的T-Sql代码或需要重复执行，则存储过程将比T-Sql批处理代码的执行速度要快。
Sql Server 在创建存储过程时对其代码进行分析和优化，并可在首次执行该过程后使用该过程的内存中
的版本。此后每次调用已经创建的存储过程时，直接执行即可，不再需要编译优化，可以提高程序的运行性能。
但如果使用T-Sql批处理代码，则每次运行T-Sql语句时，都要从客户端重复发送，并且在Sql Server 每次执行
这些语句时，都要对其进行编译和优化。




3.减少网络流量：
使用存储过程后，一个需要数百行T-Sql代码的操作，由一条反选过程代码的单独语句即可实现，而不需要在网络中发送数百行代码


4.具有良好的安全性：
即使对于没有直接执行存储过程中语句的权限的用户，也可授予他们执行该存储过程的权限。
不同权限的用户使用不同的存储过程。另外，存储过程保存在数据库中，用户只需提交存储过程名称即可直接执行，
避免了攻击者非法截到Sql代码获得用户数据的可能性

存储过程分为以下两类：
系统存储过程（System Stored Procedures）、用户自定义的存储过程（User-defined Stored Procedures）

系统存储过程


常用的系统存储过程：

Sql Server 的系统存储过程的名称以“sp_”开头，并存放在Resource数据库中，系统管理员拥有这些存储过程的使用权限，可以在任何数据库中运行系统存储过程，但
执行的结果会反映在当前数据库中。

列出常用的系统存储过程：

系统存储过程 说明


sp_databases 列出服务器上的所有数据库信息，包括数据库名称和数据库大小


sp_helpdb 报告有关指定数据库或所有数据库的信息


sp_renamedb 更改数据库名称


sp_tables 返回当前环境下可查询的表或视图的信息


sp_columns 返回某个表或视图的列信息，包括列的数据和长度等


sp_help 查看某个数据库对象的信息，如：列名、主键、约束、外键、索引等


sp_helpconstraint查看某个表的约束


sp_helpindex 查看基本个表的索引

sp_stored_procedures显示存储过程的列表


sp_password 添加或修改登录帐户的密码


sp_helptext 显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本

语法：
exec [ute] 存储过程名 [参数值]

其中，exec 是 execute 的简写


如果执行存储过程的语句是批处理中的第一个语句，则可以不指定execute关键字。

如：

sp_databases MyCollege
go

常用的扩展存储过程

一个常用的扩展存储过程为 xp_cmdshell,它可以完成DOS命令下的一些操作，诸如创建文件夹、列出文件列表等。例如：在Sql Server Management Studio 中，希望把创建的
数据库文件保存在 D:\bank 目录下，如果当前没有此目录，则使用 create database 语句创建是会报错，应如何解决呢？

语法：


exec xp_cmdshell DOS 命令 [no_output]


其中，exec表示调用存储过程，no_output 为可选参数，设置执行DOS命令后是否输出返回信息，具体使用如下：








--sp_cmdshell 扩展存储过程的使用
use master 
go


/*- 若xp_cmdshell 服务器安全配置的一部分而被关闭，请使用如下语句启用 -*/
exec sp_configure 'show advanced options',1
go


reconfigure --重新配置
go


exec sp_configure 'xp_cmdshell',1--打开 xp_cmdshell 选项go


reconfigure --重新配置
go




/*- 创建数据库 bankDB ,要求保存在 D:\bank -*/
exec xp_cmdshell 'mkdir D:\bank',no_output--创建文件夹 D:\bank


--创建数据库 bankDB
if exists (select * from sysdatabases where name='bankDB')
drop database bankDB
create database bankDB
on primary 
(
name="bankDB.data",
filename="D:\bank\bankDB.data.mdf",
size=5MB,
maxsize=10MB,
filegrowth=10%
)
log on 
(
name="bankDB.log",
filename="D:\bank\bankDB.log.ldf",
size=5MB,
maxsize=10MB
)
go


--查看 文件
exec xp_cmdshell 'dir D:\bank\'

用户自定义的存储过程


一个完整的存储过程包括以下3部分：
1.输入参数和输出参数。
2.在存储过程中执行的T-sql语句。
3.存储过程的返回值。


创建存储过程的T-Sql语句为 create procedure


创建不带参数的存储过程：

1.创建存储过程
语法：

create proc[edure] 存储过程名


[
{参数1 数据类型}[=默认值] [output],


{参数n 数据类型}[=默认值] [output]


]
as


其中，参数部分可选，这里先讨论不带参数的存储过程的用法


2.删除存储过程
语法：


drop procedure 存储过程名


成功创建一个存储过程对象之后，将在系统数据库表 sysobjects 中增加该存储过程的一条记录


/*-检查存储过程是否已经创建-*/
if exists (select * form sysobjects where name='存储过程名')

drop procedure 存储过程名

go


例：
 查询Java Logic 课程最近一次考试的玩分，依据平均分对本次考试成绩给出评价平均分高于70分者，评价为“考试成绩：优秀”：否则平价为“考试成绩：较差”：
 并给出未通过考试（及格分为60的赏名单）




use MyCollege
go


/*-检查是否存储在：存储过程存放在系统表 sysobjects 中-*/
if exists (select * from sysobjects where name='pro_student')
drop procedure pro_student
go
/*-创建存储过程-*/
create procedure pro_student
as 
declare @subjectNo int--课程编号
declare @date datetime--最近考试时间
select @subjectNo =SubjectNo from Subject where SubjectName='Winforms'
select @date = Max(ExamDate) from Result inner join Subject on Result.Cource_id=Subject.SubjectNo where Result.Cource_id=@SubjectNo
declare @avg decimal(18,2)--平均成绩
select @avg =avg(StudentResult) from Result where ExamDate =@date and Cource_id=@subjectNo

print '平均分：'+convert(varchar(5),@avg)


if(@avg>70)
print '考试成绩：优秀'
else
print '考试成绩：较差'


print '========================================================================================================'


select StudentName ,Student.StudentNo,Result.StudentResult from Student inner join Result on Result.StudentNo=Student.StudentNo where StudentResult<60 and ExamDate=@date and Cource_id=@subjectNo
go


--编译存储过程的代码后，示例的存储过程成功被创建了，但我们仍然无法看到运行结果，
--只有执行存储过程后才能得到存储过程的运行结果


/*-调用执行存储过程-*/
exec pro_student

创建带输入参数的存储过程


存储过程中的参数与此非常类似，分为以下两种：


1.输入参数：可以在调用时向存储过程传递参数，此参数可用来向存储过程中传入值
2.输出参数：如果希望返回值，则可以使用输出参数，输出参数后有“output”标记，执行存储过程后，将把返回值存放在输出参数中，可供其他T-Sql语句读取访问

1.创建带参数的存储过程


如果存储过程的参数后面有“output”关键字，则表示此参数为输出参数：否则被视为输入参数，输入参数还可以设置为默认值。

2.执行带参数的存储过程


语法：


exec[ute] [返回变量=] 存储过程名
[@参数1 =] 参数值1 [output] | [default], 


....
[@参数n =] 参数值n [output] | [default]



其中，output表明参数是输出参数，default表示参数的默认值


例：


create procedure pro_student
@subName varchar(50),
@score int
as 
select ....

/*-调用执行存储过程-*/
exec pro_student '课程名',分数或 exec pro_student @subName='课程名',@score=分数

设置默认值：


例：


create procedure pro_student
@subName varchar(50),
@score int=60 --利用赋值运算符为参数赋默认值
as 
select ....


/*-调用执行存储过程-*/
exec pro_student '课程名'或 exec pro_student @subName='课程名'

*** 注意：当创建存储过程中，有默认值的参数出现在没有默认值参数的前面，那么，调用存储过程为没有默认值参数赋值时需要指定参数名。
例：
exec pro_student @subName='课程名'


*** 建议：为了调用方便，最好将有默认值的参数放在存储过程参数列表的最后。

创建带输出参数的存储过程



调用存储过程后，返回一个或多个值，这时需要使用（output）参数



例：


use MyCollege 
go


/*-检测是否存在：存储过程存放在系统表 sysobjects 中-*/
if exists (select * from sysobjects where name='pro_student')
drop procedure pro_student
go


/*-创建存储过程-*/
create procedure pro_student
@UaPassNum int output,--输出参数，未通过人数
@TotalNum int output,-- 输出参数，参加考试总人数
@SubjectName varchar(10) ,-- 输入参数，课程名称
@Pass int =60 --输入参数，及格线，默认60分
as


declare @date datetime -- 最近一次考试时间
declare @subjectNo int -- 课程编号


select @date=max(ExamDate) from Result inner join Subject on Result.Cource_id=Subject.SubjectNo where SubjectName=@subjectName


select @subjectNo =subjectNo from Subject where SubjectName=@subjectName


print @SubjectName+'课程在 '+convert(varchar(20),@date,102)+'，考试的及格线是：'+cast(@Pass as varchar(20))


/*-未通过的学员的信息-*/


select Result.StudentNo,StudentName,StudentResult from Result inner join Student on Result.StudentNo=Student.StudentNo where ExamDate=@date and Cource_id=@subjectNo and StudentResult<@Pass



/*-获得未通过的学员人数-*/


select @UaPassNum=count(*) from Result where ExamDate =@date and Cource_id =@subjectNo and StudentResult<@Pass


/*-获得参加考试的学员总人数-*/


select @TotalNum =count(*) from Result where ExamDate=@date and Cource_id =@subjectNo
go




/*-调用执行存储过程-*/
declare @UnPassNum int   --未通过数
declare @TotalNum int -- 参加考试总人数


exec pro_student @UnPassNum output ,@TotalNum output ,'SQL Base',50


declare @ratio numeric(10,2)


set @ratio =convert(numeric(10,2),@TotalNum-@UnPassNum)/@TotalNum*100


print '未通过人数：'+convert(varchar(10),@UnPassNum)+'人，及格率是：'+cast(@ratio as varchar(10))+'%'


if(@UnPassNum>0)
begin
if(@ratio>60)
print '及格分数线不需下调'
else
print '及格分数线应下调'
end
else
begin
print '恭喜！本次考试成绩优良'
end

使用输出参数创建存储过程时，在参数后面需要跟随“output”关键字，调用时也需要在变量后跟随“output”关键字


执行带有参数的存储过程的方法有多种：

注：要保证参数的数据类型和位置必须与存储过程定义的一致。

1.按照存储过程的参数顺序依次传递参数值：如下：

exec pro_student @UnPassNum output ,@TotalNum output,'C#OOP',50

2.如果不按照参数顺序传递参数值，则要指定参数名，如下：

exec pro_student @UnPassNum output ,@TotalNum output,@SubjectName='C#OOP',@Pass=50

3.一旦某个参数按“@参数名=参数值”格式传递数据，那么该参数之后的其他参数都必须以同样的样式传递参数值


如下是错误的：
exec pro_student @UnPassNum output ,@TotalNum output,@SubjectName='C#OOP',default

exec pro_student @UnPassNum output ,@TotalNum output,@Pass=50,'C#OOP'

4.传递参数的默认值的使用：

exec pro_student @UnPassNum output ,@TotalNum output,'C#OOP',default

exec pro_student @UnPassNum output ,@TotalNum output,@SubjectName=default,@Pass=50

*** 注：在T-Sql 中，使用“/”运算符实现两个整型数值相除的结果仍是整型数，如果希望两个整型数值相除得到小数，则应该使用转换函数进行数据类型强制转换
如：


@UaPassNum int output,
@TotalNum int output,


set @ratio =convert(numeric(10,2),@TotalNum-@UnPassNum)/@TotalNum*100

处理错误信息

raiserror  语句

语法：
异常内容 级别 状态
raiserror ({msg_id | msg_str }{,severity,state}{with option [,-------n]})

例：    raiserror ('及格线错误，请指定0-100之间的，统计中断退出',16,1)


各参数的包含如处：


msg_id:在 sysmessages 系统中指定的用户定义错误信息


msg_str:用户定义的特定信息，最长为255个字符


severity:与特定信息相关联，表示用户定义的严重性级别，用户可使用的级别为0-18级：19-25级是为 sysadmin 固定角色的成员预留的，并且需要指定 with log 选项：20-25级被认为是致命错误。


state:表示错误的状态，是1-255中的值


option:错误的自定义选项，可以是下列任一值，


log：在 Microsoft Sql Server 数据库引擎实例的错误日志和应用程序日志中记录错误

nowait:将消息立即发送给客户端


seterror:将@@error值和error number 值设置为 msg_id 或5000，不用考虑严重级别。


错误的严重级别大于10，将自动设置系统全局变量@@error为非零值，表示语句执行出错。所以我们可以在调用存储过程后，判断全局变量@@error是否为0，决定是否继续执行后续语句。


例：《当用户调用存储过程时 ，判断传入的及格分数取值是否为0-100，如果不在此范围内，则弹出错误警告，并终止存储过程的执行》


代码如下：
use MyCollege 
go


/*-检测是否存在：存储过程存放在系统表 sysobjects 中-*/
if exists (select * from sysobjects where name='pro_student')
drop procedure pro_student
go


/*-创建存储过程-*/
create procedure pro_student
@UaPassNum int output,--输出参数，未通过人数
@TotalNum int output,-- 输出参数，参加考试总人数
@SubjectName varchar(10) ,-- 输入参数，课程名称
@Pass int =60 --输入参数，及格线
as
if(@Pass between 1 and 100)
begin
declare @date datetime -- 最近一次考试时间
declare @subjectNo int -- 课程编号


select @date=max(ExamDate) from Result inner join Subject on Result.Cource_id=Subject.SubjectNo where SubjectName=@subjectName


select @subjectNo =subjectNo from Subject where SubjectName=@subjectName


print @SubjectName+'课程在 '+convert(varchar(20),@date,102)+'，考试的及格线是：'+cast(@Pass as varchar(20))


/*-未通过的学员的信息-*/


select Result.StudentNo,StudentName,StudentResult from Result inner join Student on Result.StudentNo=Student.StudentNo where ExamDate=@date and Cource_id=@subjectNo and StudentResult<@Pass

/*-获得未通过的学员人数-*/


select @UaPassNum=count(*) from Result where ExamDate =@date and Cource_id =@subjectNo and StudentResult<@Pass


/*-获得参加考试的学员总人数-*/


select @TotalNum =count(*) from Result where ExamDate=@date and Cource_id =@subjectNo
end
else
begin
raiserror ('及格线错误，请指定0-100之间的，统计中断退出',16,1)
return
end
go


/*-调用执行存储过程-*/
declare @UnPassNum int   --未通过数
declare @TotalNum int -- 参加考试总人数


exec pro_student @UnPassNum output ,@TotalNum output ,'SQL Base',-20


declare @err int 
set @err = @@error
if(@err=0)
begin
declare @ratio numeric(10,2)


set @ratio =convert(numeric(10,2),@TotalNum-@UnPassNum)/@TotalNum*100


print '未通过人数：'+convert(varchar(10),@UnPassNum)+'人，及格率是：'+cast(@ratio as varchar(10))+'%'


if(@UnPassNum>0)
begin
if(@ratio>60)
print '及格分数线不需下调'
else
print '及格分数线应下调'
end
else
begin
print '恭喜！本次考试成绩优良'
end


end
else
begin
print '错误号：'+convert(varchar(10),@err)
return 
end




--exec pro_student @UnPassNum output ,@TotalNum output,'C#OOP',50

本章总结：
存储过程是一组预编译Sql语句，存储过程可以包含数据操纵语句、逻辑控制语句和调用函数等。

存储过程可加快查询的执行速度，提高访问数据的速度，帮助实现模块化编程，保持一致性和提高安全性


存储过程可分为以下两种：
1.系统存储过程
2.用户自定义的存储过程
create procedure 语句用于创建用户定义的存储过程


execute 语句用于调用执行存储过程


存储过程的参数分为输入参数和输出参数，输入参数胜为向存储过程中传入值，输出参数用于从存储过程中返回（输出）值，后面跟随“output”关键字。


raiserror  语句用来向用户报告错误