存储过程应用1

之前做项目不会存储过程，时不时还被人鄙视，现在的工作上仍旧继续使用存储过程开发业务。

也不管启用存储过程和弃用存储过程的争执了，工作需要嘛。

 

create or replace procedure PROC_SH_DRAW_HIS(start_date in varchar2,
                                                        end_date   in varchar2,
                                                        resp_code  out varchar2,
                                                        msg        out varchar2) is
  v_share_count number(30) := 0;
  v_days                   number(30) := 0;
  v_n_day                  number(30) := 0;
  v_curr_day               varchar2(10);
  v_batch_step             varchar2(100);
  v_exchange_status        SH_DRAW_HIS.exchange_status%type := '0';
begin
  resp_code := '00';
  msg       := 'PROC_SH_DRAW_HIS end, successful completion!';

  select to_date(end_date, 'yyyymmdd') - to_date(start_date, 'yyyymmdd')
    into v_days
    from dual;

  --业务提供的截止日期必须大于或等于开始日期
  if v_days < 0 then
    resp_code := '98';
    msg       := 'the end_date must more than the start_date or same time!';
    return;
  end if;

  /*RED PACKETS SHARE HISTORY*/
  v_batch_step := 'OPEN SHARE HISTORY';

  while v_n_day <= v_days loop
    --算出当前是取那一天，时间在后面查询有用
    select to_char(to_date(start_date, 'yyyymmdd') + v_n_day, 'yyyymmdd')
      into v_curr_day
      from dual;
  
    --红包分享表的创建时间要建所引加快查询速度，索引格式to_char(create_time, 'yyyymmdd')
    for x in (select *
                from SHARE           t1,
                     WINNING_HISTORY t2
               where t1.packet_id = t2.packet_id
                 and to_char(t1.create_time, 'yyyymmdd') = v_curr_day) loop
    
      --PRIZE_UNIT=’01‘，取IS_EXCHANGE赋值
      if x.prize_unit = '01' then
        v_exchange_status := x.is_exchange;
      end if;
    
      --PRIZE_UNIT=‘02’：取STATUS赋值
      if x.prize_unit = '02' then
        v_exchange_status := x.status;
      end if;
    
      insert into SH_DRAW_HIS
        (pkey_id,
         main_packet_id,
         exchange_status,
         batch_history_status,
         batch_history_date)
      values
        (SEQ_TAB_SDH_ID.nextval,
         x.packet_id,
         v_exchange_status,
         '1',
         x.pdate);

      v_share_count := v_share_count + 1;
      --默认是每100笔提交一次
      if mod(v_share_count, 100) = 0 then
        commit work;
      end if;
    end loop;
    --提交剩余的记录，就是上次100笔提交之后，累计小于100的记录
    commit work;
    v_n_day := v_n_day + 1;
  end loop;
EXCEPTION
  WHEN OTHERS THEN
    resp_code := '99';
    msg       := 'BATCH_STEP : ' || v_batch_step || '||ORA : ' || sqlerrm || '!';
    rollback;
    return;
end PROC_SH_DRAW_HIS;

 

这个存储过程是把两张旧表的数据导入新表中。

这里面用到的小知识点：

1，参数字段类型和指定表字段类型一样，可以在对应字段类型未知情况下使用，另外就是设置默认值。

v_exchange_status       SH_DRAW_HIS.exchange_status%type := '0';

 

2，算出间隔时间，这个while循环要使用。

select to_date(end_date, 'yyyymmdd') - to_date(start_date, 'yyyymmdd')
    into v_days
    from dual;

 

3，while遍历循环获取需要计算的时间。

while v_n_day <= v_days loop

select to_char(to_date(start_date, 'yyyymmdd') + v_n_day, 'yyyymmdd')
      into v_curr_day
      from dual;

v_n_day := v_n_day + 1;

 end loop;

 

4，游标遍历数据，并且每100条提交一次。

for x in (select *
                from SHARE           t1,
                     WINNING_HISTORY t2
               where t1.packet_id = t2.packet_id
                 and to_char(t1.create_time, 'yyyymmdd') = v_curr_day) loop

v_share_count := v_share_count + 1;
      --默认是每100笔提交一次
      if mod(v_share_count, 100) = 0 then
        commit work;
      end if;

 end loop;

commit work;

转载于:https://my.oschina.net/robinsonlu/blog/806585