存儲過程學習記錄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出去。不過寫的時候忘記了在什么地方看到的了，又犯了懶，所以干脆就直接使用一個臨時表，反正游標返回出來后，也是要放到臨時表里面去的嘛。