存储过程的简单使用(sqlServer)

一丶什么是存储过程

T-SQL中的存储过程，非常类似于net语言中的方法，它可以重复调用。当存储过程执行一次后，可以将语句缓存中，这样下次执行的时候直接使用缓存中的语句。

1. 存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。
2. 存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。
3. 由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快。
4. 同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、简单网络负担。

二丶优点

1、存储过程允许标准组件式编程
存储过程创建后可以在程序中被多次调用执行，而不必重新编写该存储过程的SQL语句。
而且数据库专业人员可以随时对存储过程进行修改，但对应用程序源代码却毫无影响，从而极大的提高了程序的可移植性。

2、存储过程能够实现较快的执行速度
如果某一操作包含大量的T-SQL语句代码，分别被多次执行，那么存储过程要比批处理的执行速度快得多。
因为存储过程是预编译的，在首次运行一个存储过程 时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的存储计划。
而批处理的T-SQL语句每次运行都需要预编译和优化，所以速度就要慢一些。

3、存储过程减轻网络流量
对于同一个针对数据库对象的操作，如果这一操作所涉及到的T-SQL语句被组织成一存储过程，
那么当在客户机上调用该存储过程时，网络中传递的只是该调用语句，否则将会是多条SQL语句。
从而减轻了网络流量，降低了网络负载。

4、存储过程可被作为一种安全机制来充分利用
系统管理员可以对执行的某一个存储过程进行权限限制，从而能够实现对某些数据访问的限制，避免非授权用户对数据的访问，保证数据的安全。

三丶缺点

1、运行速度
对于很简单的sql，存储过程运行速度没有什么优势。

2、代码可读性差，不易于维护
存储过程的开发调试要比一般程序困难（老版本DB2还只能用C写存储过程，更是一个灾难）。
代码可读性差，不易于难维护。

3、可移植性差
由于存储过程将应用程序绑定到SQLServer，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。
如果应用程序的可移植性在您的环境中非常重要，则将业务逻辑封装在不特定于RDBMS的中间层中可能是一个更佳的选择。

四丶简单使用

--基本语法
declare @d int --属性声明 
set @d=1 --赋值
if @d=1  --判断
begin 
	print '正确' --输出
end
else begin 
	print '错误'
end

--基本语法
declare @today int			--属性声明
declare @week varchar(50)   --属性声明
set @today=3				--属性赋值
set @week= case				--判断
	when @today=1 then '周一'
	when @today=2 then '周二'
	when @today=3 then '周三'
	when @today=4 then '周四'
	when @today=5 then '周五'
	when @today=6 then '周六'
	when @today=7 then '周天'
	else '错误'
end
print @week	--输出

--基本语法(循环)
declare @i int --属性声明
set @i=1	   --赋值
while @i<100   --循环
begin
	set @i=@i+1
	print @i
end

--创建返回结果集的无参存储过程
if(OBJECT_ID('proc_get_student','P') is not null) --判断是否存在
drop proc proc_get_student
go
create proc proc_get_student --创建存储过程
as
select * from TEST_USER --结果集

--存储过程调用
exec proc_get_student

--修改存储过程
alter proc proc_get_student
as
select * from TEST_USER where BH='A01' --修改返回结果集

--存储过程调用
exec proc_get_student

--带通配符存储过程
if(OBJECT_ID('proc_findDataByName','P') is not null) --判断是否存在
drop proc proc_findDataByName
go
create proc proc_findDataByName(@name varchar(50)='%张%', @nextName varchar(50)='%李%') --定义入参并默认值
as
select * from TEST_USER where XM like @name or XM like @nextName  --输出结果集
go

--调用存储过程
exec proc_findDataByName '%张%','%王%'

--创建返回结果集的有参存储过程
if(OBJECT_ID('proc_find_student','P') is not null) 
drop proc proc_find_student
go
create proc proc_find_student(@startAge int, @endAge int)
as 
select * from TEST_USER where NL between @startAge and @endAge
go

exec proc_find_student 10,100

--带输出参数存储过程
if(OBJECT_ID('proc_getStudentRecord','P') is not null) 
drop proc proc_getStudentRecord 
go
create proc proc_getStudentRecord(
	@id varchar(50),
	@name varchar(50) out,
	@age int output)
as
select @name=XM,@age=NL from TEST_USER where BH=@id and NL<@age
go

declare @id varchar(50),
@name varchar(50),
@age int;
set @id='B01'
set @name='李四'
set @age=30
exec proc_getStudentRecord @id,@name out,@age output;
select @name as XM,@age as NL;