数据库存储过程---实践

1,fgw_proc1:


CREATE PROCEDURE fgw_proc1(@begin int , @end int)
AS

SET NOCOUNT ON
DECLARE @userid int, @handled float, @total float copyright dedecms

CREATE TABLE #temp_proc1
(
userid int,
handled float,
total float
)
--get @total
DECLARE cur_cr CURSOR FOR SELECT count(*) FROM AHD.AHD.call_req where open_date>@begin and open_date<@end
OPEN cur_cr
FETCH cur_cr INTO @total
CLOSE cur_cr
DEALLOCATE cur_cr

DECLARE cur_ctct CURSOR FOR SELECT id FROM AHD.AHD.ctct
OPEN cur_ctct
FETCH cur_ctct INTO @userid
WHILE @@FETCH_STATUS = 0
BEGIN
--get @handle through exec fgw_proc2
EXEC fgw_proc2 @userid , @begin , @end , @handled output
INSERT INTO #temp_proc1 VALUES (@userid , @handled , @total)
FETCH NEXT FROM cur_ctct INTO @userid
END
CLOSE cur_ctct
DEALLOCATE cur_ctct
SELECT * FROM #temp_proc1
DROP TABLE #temp_proc1

copyright dedecms

drop procedure fgw_proc1
exec fgw_proc1 1,1


2,fgw_proc2

CREATE PROCEDURE fgw_proc2(@userid int , @begin int , @end int , @handled float OUTPUT)
AS copyright dedecms

SET NOCOUNT ON
SET @handled = 0
DECLARE @cr_id int, @zh_id int, @status char(20), @to_status char(20), @cnt int, @open_date int
DECLARE cur_crzh CURSOR FOR SELECT * FROM AHD.dbo.FGW_CR_ZH where cnt = @userid
OPEN cur_crzh
FETCH cur_crzh INTO @cr_id, @zh_id, @status, @to_status, @cnt, @open_date
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @count2 int
DECLARE cur_crzh2 CURSOR FOR SELECT count(*) FROM AHD.dbo.FGW_CR_ZH where cr_id = @cr_id and open_date>@begin and open_date<@end
OPEN cur_crzh2
FETCH cur_crzh2 INTO @count2
CLOSE cur_crzh2
DEALLOCATE cur_crzh2
IF @count2 != 0
SET @handled = @handled 1 / @count2
FETCH NEXT FROM cur_crzh INTO @cr_id, @zh_id, @status, @to_status, @cnt, @open_date
END
CLOSE cur_crzh
DEALLOCATE cur_crzh
--SELECT @handled 内容来自dedecms

内容来自dedecms

drop procedure fgw_proc2
exec fgw_proc2 1,1,1

3,fgw_proc3


CREATE PROCEDURE fgw_proc3(@begin int , @end int)
AS

内容来自dedecms

SET NOCOUNT ON
DECLARE @cr_id int, @zh_id int, @cnt int, @sym char(30), @time_stamp int, @isOK int dedecms.com

CREATE TABLE #temp_proc3
(
cr_id int,
zh_id int,
cnt int,
isOK int
)

DECLARE cur_crzhsd CURSOR FOR SELECT cr.id,zh.id,zh.to_cnt,sd.sym,zh.time_stamp FROM AHD.AHD.call_req as cr LEFT OUTER JOIN AHD.AHD.ztr_his as zh ON cr.persid=zh.call_req_id LEFT OUTER JOIN AHD.AHD.srv_desc as sd ON cr.support_lev=sd.code WHERE cr.type='I' and cr.open_date>@begin and cr.open_date<@end and zh.to_status='OP'
OPEN cur_crzhsd
FETCH cur_crzhsd INTO @cr_id, @zh_id, @cnt, @sym, @time_stamp
WHILE @@FETCH_STATUS = 0
BEGIN
--get @handle through exec fgw_proc2
EXEC fgw_proc4 @zh_id , @sym , @time_stamp , @cnt output , @isOK output
INSERT INTO #temp_proc3 VALUES (@cr_id , @zh_id , @cnt , @isOK)

看完这篇，您有何感觉呢？