存储过程---实践

1,fgw_proc1:


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

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

    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


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

    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

 

drop procedure fgw_proc2
exec fgw_proc2 1,1,1

3,fgw_proc3


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

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

    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)
 FETCH NEXT FROM cur_crzhsd INTO @cr_id, @zh_id, @cnt, @sym, @time_stamp
        END
    CLOSE cur_crzhsd
    DEALLOCATE cur_crzhsd
    SELECT * FROM #temp_proc3
    DROP TABLE #temp_proc3


drop procedure fgw_proc3
EXEC fgw_proc3 1, 1111111111

4,fgw_proc4

CREATE PROCEDURE fgw_proc4(@zh_id int , @level char(30) , @time_stamp int , @cnt int OUTPUT , @isOK int OUTPUT)
AS

    SET NOCOUNT ON
    SET @isOK = 0
    DECLARE cur_zh CURSOR FOR SELECT to_cnt,time_stamp FROM AHD.AHD.ztr_his WHERE id = @zh_id and to_status in (@#L1WIP@#,@#L2WIP@#) and time_stamp>@time_stamp
    OPEN cur_zh
    DECLARE @time_stamp1 int
 SET @time_stamp1=0
    FETCH cur_zh INTO @cnt, @time_stamp1
 IF @time_stamp1!=0
 BEGIN
  IF CHARINDEX(@#一级@#, @level) IS NOT NULL AND CHARINDEX(@#一级@#, @level)!=0
   BEGIN
    if @time_stamp1 - @time_stamp <600
    SET @isOK=1
   END
  ELSE IF CHARINDEX(@#二级@#, @level) IS NOT NULL AND CHARINDEX(@#二级@#, @level)!=0
   BEGIN
    if @time_stamp1 - @time_stamp <1800
    SET @isOK=1
   END
  ELSE IF CHARINDEX(@#三级@#, @level) IS NOT NULL AND CHARINDEX(@#三级@#, @level)!=0
   BEGIN
    if @time_stamp1 - @time_stamp <1800
    SET @isOK=1
   END
  ELSE IF CHARINDEX(@#四级@#, @level) IS NOT NULL AND CHARINDEX(@#四级@#, @level)!=0
   BEGIN
    if @time_stamp1 - @time_stamp <1800
    SET @isOK=1
   END
 END

    CLOSE cur_zh
    DEALLOCATE cur_zh
    --SELECT @isOK, @time_stamp1

 

drop procedure fgw_proc4
exec fgw_proc4 1,@#1@#,1,1,1

原文转自：http://www.ltesting.net