oracle存储过程+游标基本用法

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;