分摊大数据存储过程
分摊大数据存储过程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
openacc_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
页:
[1]