存储过程学习记录1
我的第一个存储过程
该存储过程的功能是去完成在多个无关系数据表中执行联合查询和计算，最后生成一个统计表。
这个存储过程中包括了：参数传递，存储过程间相互调用并返回结果集到临时表中，使用sp_executesql执行动态生成的Sql语句，临时表的创建以及使用
==============================================================
CREATE procedure sp_MakeCond
@FdName nvarchar(20),
@FdRecord nvarchar(250),
@CXZT int,
@GSBM nvarchar(10),
@Cond nvarchar(250) output
as
/*
功能：
根据@CXZT的状态，对于字段@FdName进行内容@FdRecord的匹配。
当@FdRecord中有内容的时候，进行匹配
当@GSBM中有内容的时候，添加GSBM的条件
输入:
@FdName 字段名称
@FdRecord 字段内容
@GSBM
@CXZT 查询状态 0-表示精确，1-表示模糊，2-表示智能
该状态仅对KHMC起作用
输出：
@Cond 条件语句 例如:
输入 dwmc,'上海',1,'RRL'
输出 dwmc like '上海%' and gsbm='RRL'
*/
/*
创建人：
创建日期：
--对于改存储过程的版本更新记录在这个地方
*/
declare @HaveFdRecord as int
Set @HaveFdRecord = 0
Set @Cond = ''
/* 添加FdRecord */
if len(rtrim(ltrim(@FdRecord)))>0
begin
select @Cond=
case @CXZT
when 0 then
@Cond + @FdName + N'=''' + @FdRecord + ''''
when 1 then
@Cond + @FdName + N' like ''' + @FdRecord + '%'''
when 2 then
@Cond + @FdName + N' like ''%' + @FdRecord + '%'''
end
set @HaveFdRecord = 1
end
/* 添加GSBM */
if len(rtrim(ltrim(@GSBM)))>0
begin
if @HaveFdRecord=1
begin
Set @Cond = @Cond + ' and '
end
set @Cond = @Cond + ' gsbm=''' + @GSBM + ''''
end
GO
==============================================================
CREATE Procedure sp_Math_KHYE
@KHMC nvarchar(250),
@JZRQ datetime,
@CXZT int,
@GSBM nvarchar(10)
as
/*
功能：
输入：
@KHMC 客户名称
@CXZT 查询状态 0-表示精确，1-表示模糊，2-表示智能
该状态仅对KHMC起作用
@JZRQ 截至日期
@GSBM
输出：
@ReturnKHYE 根据条件计算出来的客户余额
说明：在调用该过程前，需按照如下语句定义一个命名为#KHYEReturn的临时表
Create Table #KHYEReturn
(dwmc nvarchar(250),khye decimal(15,6))
在使用完毕#KHYEReturn后，应该立刻使用下列语句释放该临时表
delete from #KHYEReturn
drop Table #KHYEReturn
*/
/*
实现方法：
*/
/*
创建人：
创建日期：
--对于改存储过程的版本更新记录在这个地方
*/
Declare @SqlString nvarchar(1000)
Declare @Cond nvarchar(250)
-- 制作查询条件，存入@Cond
EXECUTE sp_MakeCond 'dwmc',@KHMC,@CXZT,@GSBM,@Cond output
/* */
set @SqlString = 'select dwmc,sum(xhsl*hsj) as xsje from xsdda where xsrq<=@vJZRQ and ' + @Cond
set @SqlString = @SqlString + ' group by dwmc'
Create table #sp_Math_KHYE_XSJE(dwmc nvarchar(250),xsje decimal(15,6))
Declare @ParmDefinition nvarchar(100)
set @ParmDefinition = '@vKHMC nvarchar(250),@vJZRQ datetime'
-- 这个地方就是调用sp_executesql来执行动态SqlString并将结果返回到一张临时表里面
-- 在这里，曾经试图将结果返回到游标中间，但是没有成功。
-- 根据资料 insert 和 exec 是不能够嵌套执行的，不过在这个地方比较奇怪，这两个可以嵌套执行。
-- 需要注意的地方：@ParmDefnition是用来定义在sp_executesql中间要使用的变量的。
-- 曾经试验这个地方直接传一个字符串，不使用变量传递，结果报错。不知道有没有其它的人成功过
-- 在@ParmDefinition后面就要传递一个变量列表，按照我的理解，
-- 就是在这个地方给sp_executesql过程中要使用的变量（也就是Sql语句的变量）赋值
insert into #sp_Math_KHYE_XSJE EXECUTE sp_executesql @SqlString,
@ParmDefinition,
@vKHMC=@KHMC,
@vJZRQ=@JZRQ
/* */
set @SqlString = ''
set @SqlString = 'select dwmc,sum(bcsk+ysk) as fkje from xhskdda where skrq<=@vJZRQ and ' + @Cond
set @SqlString = @SqlString + ' group by dwmc'
Create table #sp_Math_KHYE_FKJE(dwmc nvarchar(250),fkje decimal(15,6))
set @ParmDefinition=''
set @ParmDefinition = '@vKHMC nvarchar(250),@vJZRQ datetime'
insert into #sp_Math_KHYE_FKJE EXECUTE sp_executesql @SqlString,
@ParmDefinition,
@vKHMC=@KHMC,
@vJZRQ=@JZRQ
Create Table #sp_Math_KHYE_Result(
dwmc nvarchar(250),khye decimal(15,6))
-- 比较简单的Sql语句了，左联右联，BT的需求，只能用BT的写法
insert into #sp_Math_KHYE_Result
select distinct T1.dwmc as dwmc,(T1.fkje-T1.xsje) as khye from
(
select
#sp_Math_KHYE_XSJE.dwmc as dwmc,
isnull(#sp_Math_KHYE_XSJE.xsje,0) as xsje,
isnull(#sp_Math_KHYE_FKJE.fkje,0) as fkje
from #sp_Math_KHYE_XSJE,#sp_Math_KHYE_FKJE
where #sp_Math_KHYE_XSJE.dwmc*=#sp_Math_KHYE_FKJE.dwmc
union
select
#sp_Math_KHYE_XSJE.dwmc as dwmc,
isnull(#sp_Math_KHYE_XSJE.xsje,0) as xsje,
isnull(#sp_Math_KHYE_FKJE.fkje,0) as fkje
from #sp_Math_KHYE_XSJE,#sp_Math_KHYE_FKJE
where #sp_Math_KHYE_FKJE.dwmc*=#sp_Math_KHYE_XSJE.dwmc
) as T1
insert into #KHYEReturn select * from #sp_Math_KHYE_Result
GO
==================================================================
CREATE Procedure sp_KHYEXYTJ
@StartDate datetime,
@EndDate datetime,
@KHMC nvarchar(250),
@CXZT int,
@GSBM nvarchar(100)
as
/*
功能：
输入:
@StartDate 开始日期
@EndDate 截至日期
@KHMC 客户名称
@CXZT 查询状态 0-表示精确，1-表示模糊，2-表示智能
该状态仅对KHMC起作用
@GSBM
输出：
表结构：
dwmc ncye ssye bqxs qmye khxye cxye
*/
/*
创建人：
创建日期：
--对于改存储过程的版本更新记录在这个地方
*/
-- 这个地方求了一堆乱七八糟的数据，是需要用来输出的。
-- 求得表#sp_KHYEXYTJ_QMKHYE
Create Table #sp_KHYEXYTJ_QMKHYE
(dwmc nvarchar(250),QMkhye decimal(15,6))
Create Table #KHYEReturn
(dwmc nvarchar(250),khye decimal(15,6))
execute sp_Math_KHYE @KHMC,@EndDate,@CXZT,@GSBM
insert into #sp_KHYEXYTJ_QMKHYE(dwmc,QMKHYE) select * from #KHYEReturn
delete from #KHYEReturn
-- 求得表#sp_KHYEXYTJ_QMKHYE
Declare @NowDate datetime
set @NowDate = GetDate()
Create Table #sp_KHYEXYTJ_KHYE
(dwmc nvarchar(250),khye decimal(15,6))
execute sp_Math_KHYE @KHMC,@NowDate,@CXZT,@GSBM
insert into #sp_KHYEXYTJ_KHYE select * from #KHYEReturn
delete from #KHYEReturn
-- 求得表#sp_KHYEXYTJ_NCYE
Create Table #sp_KHYEXYTJ_NCYE
(dwmc nvarchar(250),ncye decimal(15,6))
Set @NowDate=cast(str(year(GetDate()))+'-01-01 00:00:00' as datetime)
execute sp_Math_KHYE @KHMC,@NowDate,@CXZT,@GSBM
insert into #sp_KHYEXYTJ_NCYE select * from #KHYEReturn
delete from #KHYEReturn
drop table #KHYEReturn
--
Declare @SqlString nvarchar(500)
Declare @Cond nvarchar(100)
-- 这里也是求查询条件的地方
EXECUTE sp_MakeCond 'dwmc',@KHMC,@CXZT,@GSBM,@Cond output
set @SqlString = 'select dwmc,sum(xhsl*hsj) as xsje from xsdda where xsrq<=@vEndDate and xsrq>=@vStartDate and ' + @Cond
set @SqlString = @SqlString + ' group by dwmc'
Create table #sp_KHYEXYTJ_XSJE(dwmc nvarchar(250),xsje decimal(15,6))
Declare @ParmDefinition nvarchar(100)
set @ParmDefinition = '@vKHMC nvarchar(250),@vEndDate datetime,@vStartDate datetime'
insert into #sp_KHYEXYTJ_XSJE EXECUTE sp_executesql @SqlString,
@ParmDefinition,
@vKHMC=@KHMC,
@vEndDate=@EndDate,
@vStartDate=@StartDate
-- 制作输出表
Create Table #sp_KHYEXYTJ_ReslutTable
(dwmc nvarchar(250),
ncye decimal(15,6),
ssye decimal(15,6),
bqxs decimal(15,6),
qmye decimal(15,6),
khxye decimal(15,6),
cxye decimal(15,6))
-- 下面就是一堆乱七八糟的东西了。本来是将数据放到游标中去，然后对游标进行循环处理
-- 不过好像速度却很慢，一共是3张数据表，三章表数据分别为1k,4k,2W，
-- 用游标处理，最终返回统计表600条数据用了27秒，一个比较让人发傻的时间
-- 可能是我写的不好吧，但是这个时间也确实太长了
-- 所以，就又换成按照临时表来处理，分别将各个需要统计的数据分开统计，
-- 然后放入到不同的临时表里面，最后再将所有的数据根据dwmc这个字段进行关联，一起输出。
-- 最终查询结果，同样数据量，用时2秒钟。
-- 对于这个时间来说，我还是比较满意的。因为算的数据比较多了，而且和前面用游标用了27s
-- 根本就不是一个等量级。不管好坏，先可以拿出来见人了。
-- 写入输出表,并生成统计列表
insert into #sp_KHYEXYTJ_ReslutTable(dwmc,bqxs)
select dwmc,xsje from #sp_KHYEXYTJ_XSJE
-- 写入输出表
update #sp_KHYEXYTJ_ReslutTable
set #sp_KHYEXYTJ_ReslutTable.ssye = #sp_KHYEXYTJ_KHYE.khye
from #sp_KHYEXYTJ_KHYE
where #sp_KHYEXYTJ_ReslutTable.dwmc = #sp_KHYEXYTJ_KHYE.dwmc
--写入输出表
update #sp_KHYEXYTJ_ReslutTable
set #sp_KHYEXYTJ_ReslutTable.qmye = #sp_KHYEXYTJ_QMKHYE.QMkhye
from #sp_KHYEXYTJ_QMKHYE
where #sp_KHYEXYTJ_ReslutTable.dwmc = #sp_KHYEXYTJ_QMKHYE.dwmc
-- 写入输出表
update #sp_KHYEXYTJ_ReslutTable
set #sp_KHYEXYTJ_ReslutTable.ncye = #sp_KHYEXYTJ_NCYE.ncye
from #sp_KHYEXYTJ_NCYE
where #sp_KHYEXYTJ_ReslutTable.dwmc = #sp_KHYEXYTJ_NCYE.dwmc
-- 计算
update #sp_KHYEXYTJ_ReslutTable
set #sp_KHYEXYTJ_ReslutTable.khxye = isnull(xhkhda.khxye,0),
#sp_KHYEXYTJ_ReslutTable.cxye = abs(#sp_KHYEXYTJ_ReslutTable.ssye)-isnull(xhkhda.khxye,0)
from xhkhda
where #sp_KHYEXYTJ_ReslutTable.dwmc = xhkhda.dwmc
--最终输出结果了，因为都是算得金额了，为了减小误差，在计算的时候
-- 全部使用6位小数，在输出的时候，取了两位小数来输出
-- 输入表
select
rtrim(ltrim(dwmc)) as dwmc,
cast(round(isnull(ncye,0),2) as decimal(15,2)) as ncye,
cast(round(isnull(ssye,0),2) as decimal(15,2)) as ssye,
cast(round(isnull(bqxs,0),2) as decimal(15,2)) as bqxs,
cast(round(isnull(qmye,0),2) as decimal(15,2)) as qmye,
cast(round(isnull(khxye,0),2) as decimal(15,2)) as khxye,
cast(round(isnull(
case
when cxye<= 0 then 0
when cxye>0 then cxye
end,0),2) as decimal(15,2)) as cxye
from #sp_KHYEXYTJ_ReslutTable
GO
这三个存储过程写的比较乱啦，毕竟是我的第一个存储过程嘛。可以谅解的。不过以后就不能写这么乱的存储过程了，否则不可饶恕。
在这三个存储过程中有一些地方写的还是不尽人意，比方说，存储过程A调用存储过程B，而存储过程B需要返回一个结果集供A使用。虽然用了一个临时表来作为存储用，可是总想象写OO一样，把临时表或者结果集象对象一样返回出去。记得在写这个地方的前一天偶然看了资料，可以把结果集放到游标中output出去。不过写的时候忘记了在什么地方看到的了，又犯了懒，所以干脆就直接使用一个临时表，反正游标返回出来后，也是要放到临时表里面去的嘛。