create or replace procedure TEST_PROC is
t_amount_i number; --上一天罐存实测-当天罐存期初实测
p_amount_i number; --上一天管线油实测-当天管线油期初实测
t_amount number; --当天天罐存实测-当天罐存期期末实测
p_amount number; --当天管线油实测-当天管线油期末实测
d_account_date varchar2(10); --定义变量
cursor cur is
select t.* from STO_STORAGE_ACCOUNT t where t.data_validate_flag = 1; --定义游标
begin
--游标for循环开始:库存保管账实测值全量更新
for temp in cur loop
--temp为临时变量名,自己任意起
begin
select max(f.business_date)
into d_account_date
from STO_STORAGE_ACCOUNT f
where f.data_validate_flag = 1
and f.business_date < temp.business_date;
EXCEPTION
WHEN NO_DATA_FOUND THEN
d_account_date := '2017-01-01';
end;
begin
select nvl(sum(f.tank_amount), 0)
into t_amount_i
from sto_tank_metering f,
(select n.oil_id,
max(n.account_date) as account_date,
n.storage_id,
n.tank_id
from sto_tank_metering n
where n.DATA_VALIDATE_FLAG = 1
and n.storage_id = temp.storage_id
and n.OIL_id = temp.oil_id
and to_char(n.account_date, 'yyyy-mm-dd') <= d_account_date
group by n.oil_id, n.storage_id, n.tank_id) s
where f.oil_id = s.oil_id
and f.DATA_VALIDATE_FLAG = 1
and f.account_date = s.account_date
and f.tank_id = s.tank_id
and f.storage_id = s.storage_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
t_amount_i := 0;
end;
begin
select nvl(f.oil_amount, 0)
into p_amount_i
from STO_PIPELINE_MEASURE f,
(select n.oil_id, max(n.fill_date) as fill_date, n.storage_id
from STO_PIPELINE_MEASURE n
where n.DATA_VALIDATE_FLAG = 1
and n.storage_id = temp.storage_id
and n.OIL_id = temp.oil_id
and to_char(n.fill_date, 'yyyy-mm-dd') <= d_account_date
group by n.oil_id, n.storage_id) s
where f.oil_id = s.oil_id
and (f.DATA_VALIDATE_FLAG = 1 or f.DATA_VALIDATE_FLAG = 2)
and f.fill_date = s.fill_date
and f.storage_id = s.storage_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_amount_i := 0;
end;
begin
select nvl(sum(f.tank_amount), 0)
into t_amount
from sto_tank_metering f,
(select n.oil_id,
max(n.account_date) as account_date,
n.storage_id,
n.tank_id
from sto_tank_metering n
where n.DATA_VALIDATE_FLAG = 1
and n.storage_id = temp.storage_id
and n.OIL_id = temp.oil_id
and to_char(n.account_date, 'yyyy-mm-dd') <=
temp.business_date
group by n.oil_id, n.storage_id, n.tank_id) s
where f.oil_id = s.oil_id
and f.DATA_VALIDATE_FLAG = 1
and f.account_date = s.account_date
and f.tank_id = s.tank_id
and f.storage_id = s.storage_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
t_amount := 0;
end;
begin
select nvl(f.oil_amount, 0)
into p_amount
from STO_PIPELINE_MEASURE f,
(select n.oil_id, max(n.fill_date) as fill_date, n.storage_id
from STO_PIPELINE_MEASURE n
where n.DATA_VALIDATE_FLAG = 1
and n.storage_id = temp.storage_id
and n.OIL_id = temp.oil_id
and to_char(n.fill_date, 'yyyy-mm-dd') <=
temp.business_date
group by n.oil_id, n.storage_id) s
where f.oil_id = s.oil_id
and (f.DATA_VALIDATE_FLAG = 1 or f.DATA_VALIDATE_FLAG = 2)
and f.fill_date = s.fill_date
and f.storage_id = s.storage_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_amount := 0;
end;
--期初实测&期末实测
update STO_STORAGE_ACCOUNT t
set t.init_actual_amount = t_amount_i + p_amount_i,
t.actual_measure_amount = t_amount + p_amount
where t.id = temp.id;
commit;
--期末账面
update STO_STORAGE_ACCOUNT t set t.final_amount = t.init_amount+t.in_storage_amount-t.out_storage_amount+t.adjust_amount+t.approval_over_amount-t.approval_loss_amount
where t.id = temp.id;
commit;
--损益量
update STO_STORAGE_ACCOUNT t set t.over_loss_amount = t.actual_measure_amount-t.final_amount
where t.id = temp.id;
commit;
end loop;
--游标for循环结束
end TEST_PROC;
