分摊大数据存储过程

admin

分摊大数据存储过程
[mw_shl_code=sql,true]exec "p_ddm_account";

先将大数据account表记录导入account_test 然后执行语句 exec "p_ddm_account"; 系统自动将数据劈开，导入account_ls表中，然后再在acount表中删除所导的大数据，
最后将account_ls导入account,完成。


以下为过程语句


ALTER PROCEDURE "wuerp"."p_ddm_account"
AS
BEGIN
    declare @ls_organ nvarchar(4)
    declare @ls_class nvarchar(2)
    declare @ls_busdate timestamp
    declare @ls_resume nvarchar(30)
    declare @billid nvarchar(20)
    declare @ls_code nvarchar(4)
    declare @ls_sumcost integer
    declare @ls_acceptid nvarchar(20)
    declare @ls_state integer
    declare @ls_provider nvarchar(8)
    declare @ls_jyfs nvarchar(10)
    declare @ls_store nvarchar(6)
    declare @ls_tax integer
    declare @ls_moditime timestamp
    declare acc_cursor cursor for select organ,class,busdate,resume,billid,code,sum_cost,acceptid,state,provider,jyfs,store,tax,moditime from account_test
    open  acc_cursor
    fetch acc_cursor into  @ls_organ,@ls_class,@ls_busdate,@ls_resume,@billid,@ls_code,@ls_sumcost,@ls_acceptid,@ls_state,@ls_provider,@ls_jyfs,@ls_store,@ls_tax,@ls_moditime
    while (@@FETCH_STATUS=0)
    begin
    insert into account_lsls(organ,class,busdate,resume,billid,code,sum_cost,acceptid,state,provider,jyfs,store,tax,amount,sum_price,sum_disc,cost_statu,price_statu,moditime,sum_cost1,acctype)
                (select @ls_organ,@ls_class,@ls_busdate,@ls_resume,@billid,a.code,a.sum_cost,@ls_acceptid,@ls_state,@ls_provider,@ls_jyfs,@ls_store,@ls_tax,round(a.sum_cost/a.cost_price,0),0,0,0,0,@ls_moditime,0,'A' from
                      (select code as code ,cost_price,cost_price*(@ls_sumcost/(select sum(cost_price)   from account_ls_price where class=@ls_class)) as sum_cost from account_ls_price where class=@ls_class) a  )

    fetch acc_cursor into  @ls_organ,@ls_class,@ls_busdate,@ls_resume,@billid,@ls_code,@ls_sumcost,@ls_acceptid,@ls_state,@ls_provider,@ls_jyfs,@ls_store,@ls_tax,@ls_moditime
   
end
    close acc_cursor
    deallocate acc_cursor
END[/mw_shl_code]