oracle存储过程：游标使用，多重游标

--说明：2个日期内，以交易时间点为切片遍历3分钟内的数据，做统计判断
--        运用了双重游标循环
--入参2个，v_start_date 开始日期，v_end_date 结束日期
create or replace procedure zy_710507_procedure(
  v_start_date varchar,
  v_end_date varchar,
  p_error_info out nvarchar2
)
is
  --变量定义：开始日期、结束日期
  p_start_date varchar(10) := nvl(v_start_date, to_char(sysdate, 'yyyymmdd'));
  p_end_date   varchar(10) := nvl(v_end_date, to_char(sysdate, 'yyyymmdd'));
  date_temp    varchar(10);
  --日期游标
  cursor date_cur is 
         select distinct l_date from hs_aml.tradezy_thisrealdeal_cs  
         where l_date >= p_start_date and l_date <= p_end_date;
  --每天的时间游标
  cursor time_cur(date_c varchar2) is 
         select distinct l_date,l_business_time as end_time,to_char(to_date(lpad(L_BUSINESS_TIME,6,'0'),'hh24miss')-3/24/60,'hh24miss') as begin_time
         from hs_aml.tradezy_thisrealdeal_cs 
         where l_date =date_c and l_business_time>='93300' and l_business_time<='150000' order by l_business_time;
    --存储过程处理开始
  begin  

   --遍历游标
    for cur in date_cur loop  
        begin
            date_temp   :=  cur.l_date;
            --第一游标的值作为第二游标的入参
            for timecur in time_cur(date_temp) loop
            insert into zy_710507 (warning_date,warning_time,fund_id,stock_code,trade_side,business_date,begin_time,end_time,trade_amount,LAST_PRICE)
            select  to_char(sysdate, 'yyyymmdd') as warning_date,
                        to_char(sysdate, 'HH24mmss') as warning_time,
                        L_FUND_ID as fund_id,
                        VC_REPORT_CODE as stock_code,
                        trade_side,
                        l_date as business_date,
                        begin_time,
                        end_time,
                        trade_amount,
                        LAST_PRICE
            from (
                select a.l_date,
                       a.L_FUND_ID,
                       a.VC_REPORT_CODE,
                       sum(a.l_deal_amount * a.EN_DEAL_PRICE) as trade_amount,
                       timecur.begin_time as begin_time,
                       timecur.end_time as end_time,
                       '买入' as trade_side,
                       b.LAST_PRICE
                  from hs_aml.tradezy_thisrealdeal_cs a
                  left join hs_aml.src_ucbs_his_price b
                    on a.VC_REPORT_CODE = b.STOCK_CODE
          left join (select max(exch_date) as exch_date from hs_aml.conexchangedate where exch_date<timecur.l_date) c
          on b.INIT_DATE = c.exch_date
                 where a.L_DATE = timecur.l_date
                   and a.C_ENTRUST_DIRECTION = '1' --买入
                   and a.L_BUSINESS_TIME >= timecur.begin_time
                   and a.L_BUSINESS_TIME <= timecur.end_time
                   and a.EN_DEAL_PRICE > b.LAST_PRICE
           and b.INIT_DATE = c.exch_date
                 group by a.l_date, a.L_FUND_ID, a.VC_REPORT_CODE, b.LAST_PRICE
                union all
                select a.l_date,
                       a.L_FUND_ID,
                       a.VC_REPORT_CODE,
                       sum(a.l_deal_amount * a.EN_DEAL_PRICE) as trade_amount,
                       timecur.begin_time as begin_time,
                       timecur.end_time as end_time,
                       '卖出' as trade_side,
                       b.LAST_PRICE
                  from hs_aml.tradezy_thisrealdeal_cs a
                  left join hs_aml.src_ucbs_his_price b
                    on a.VC_REPORT_CODE = b.STOCK_CODE
          left join (select max(exch_date) as exch_date from hs_aml.conexchangedate where exch_date<timecur.l_date) c
          on b.INIT_DATE = c.exch_date
                 where a.L_DATE = timecur.l_date
                   and a.C_ENTRUST_DIRECTION = '2' --卖出
                   and a.L_BUSINESS_TIME >= timecur.begin_time
                   and a.L_BUSINESS_TIME <= timecur.end_time
                   and a.EN_DEAL_PRICE < b.LAST_PRICE
           and b.INIT_DATE = c.exch_date
                 group by a.l_date, a.L_FUND_ID, a.VC_REPORT_CODE, b.LAST_PRICE
             ) t where t.trade_amount >=1000000;
             commit;
             end loop ;
        end;
    end loop ;
--异常捕获
commit;
   exception
     when others then
       rollback;
       p_error_info :=sqlerrm;
end;