sql server 的存储过程实例

/**********
实现功能，将项目每年的月份中的资金数据累加
使用了三个循环嵌套游标，可能性能不是很好，主要演示存储过程的实现
***************/

CREATE  PROCEDURE accumulate
AS
declare @temp varchar(255)
declare @Field int

declare @practice_all int
declare @capatital_all int
declare @temppractice int
declare @tempcapital int

declare @month int
declare @year int

declare prject_cursor cursor for SELECT [projectid] AS projectid FROM v_project_year_month GROUP BY projectid

set @practice_all=0
set @capatital_all=0

open prject_cursor    --打开游标
fetch next from prject_cursor into @Field
while(@@fetch_status=0)    --循环开始
begin
    declare year_cursor cursor for SELECT [year_yd] FROM v_project_year_month WHERE (projectid = @Field) GROUP BY year_yd
    open year_cursor
    fetch next from year_cursor into @year
    while(@@fetch_status=0)
    begin
        print cast(@year as varchar)+'年'+cast(@Field as varchar)+'============项目'
        declare month_cursor cursor for SELECT [Month_yf],[Month_practice],[Month_capital] FROM v_project_year_month WHERE (year_yd=@year) and (projectid =@Field) ORDER BY Month_yf
        open month_cursor
        fetch next from month_cursor into @month,@temppractice,@tempcapital
        while(@@fetch_status=0)
        begin
            
            set @temp=cast(@month as varchar)
            print '月份--'+@temp
            print '投资：'+cast(@temppractice as varchar)+'资金：'+cast(@tempcapital as varchar)
            set @practice_all=@practice_all+@temppractice
            set @capatital_all=@capatital_all+@tempcapital
            --累计投资
            Exec('update v_project_year_month set Month_practice_total='+@practice_all+' where projectid='+@Field+' and month_yf='+@month+'')
            --累计资金
            Exec('update v_project_year_month set Month_capital_total='+@capatital_all+' where projectid='+@Field+' and month_yf='+@month+'')
            
            print @practice_all
            print @capatital_all
            fetch next from month_cursor into @month,@temppractice,@tempcapital
        end
        close month_cursor    --关闭游标
        deallocate month_cursor--释放资源
    fetch next from year_cursor into @year
    end
    close year_cursor    --关闭游标
    deallocate year_cursor    --释放资源
fetch next from prject_cursor into @Field --下个prject
set @practice_all=0    --归零
set @temppractice=0    --归零
set @capatital_all=0    --归零
set @tempcapital=0    --归零
end
close prject_cursor    --关闭游标
deallocate prject_cursor--释放资源

GO