游标模板

郭哥给我的模板，留着以后用。

declare
  i number;
  cursor select_data_table is
    select * from 
  (
    select A.*, rownum RN 
     from (select * from sms_temp) A 
    where rownum <= 100
  )
  WHERE RN >= 0; 
  
  --声明一个游标，游标的名称是select_data_table
  rec_select_data_table select_data_table%rowtype;

begin
  i := 1; --将变量i一个初始值

  --开始读数据
  if select_data_table%isopen then
    close select_data_table;
  end if;
  open select_data_table;
  loop
    fetch select_data_table
      into rec_select_data_table;
    if select_data_table%notfound then
      close select_data_table;
      exit;
    else
    
      DBMS_OUTPUT.put_line('手机号码：' || rec_select_data_table.phone ||
                           '，姓名：' || rec_select_data_table.name);
      insert into sms_info_down_batch
        (batch_id)
      values
        (seq_sms_down_batch_batch_id.nextval);
      insert into sms_info_down_detail
        (id, batch_id, phone, content)
      values
        (seq_sms_down_detail_id.nextval,
         seq_sms_down_batch_batch_id.currval,
         rec_select_data_table.phone,
         '短信' || to_char(i, '000') || '内容');
    
      commit;
    
    end if;
    i := i + 1;
  end loop;

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('系统出错的日志为:' || to_char(SQLERRM));    
    rollback;
END;
/