批量游标常用套路

  
  
declare
  cursor cur_login is
    select user_id, login, login_time from user_login;
  type user_id_type is table of user_login.user_id%type;
  type login_type is table of user_login.login%type;
  type login_time_type is table of user_login.login_time%type;
  id_tab         user_id_type;
  login_tab      login_type;
  login_time_tab login_time_type;
  v_limit        pls_integer := 5000;
begin
  open cur_login;
  loop
    fetch cur_login bulk collect
      into id_tab, login_tab, login_time_tab limit v_limit;
    exit when id_tab.count = 0;
    forall i in id_tab.first .. id_tab.last
      insert into user_login_bak
      values
        (id_tab(i), login_tab(i), login_time_tab(i));
    commit;
  end loop;
  close cur_login;
end;
/ 
declare
  cursor cur is
    select USER_ID, LOGIN, LOGIN_TIME from USER_LOGIN;
  v_user_id    dbms_sql.Number_Table; ---不是记录了，是集合
  v_login      dbms_sql.Number_Table; ---不是记录了，是集合
  v_login_time dbms_sql.date_Table; ------不是记录了，是集合
begin
  open cur;
  loop
    fetch cur bulk collect
      into v_user_id, v_login, v_login_time limit 5000; ----一次批量获取5000行；
    forall i in 1 .. v_user_id.count
      insert into USER_LOGIN_bak
      values
        (v_user_id(i), v_login(i), v_login_time(i)); ----这里的 i 就是指 1..5000
    commit;
    exit when cur%notfound or cur%notfound is null;
  end loop;
  close cur;
  commit;
end;
declare
  cursor cur_user_login is
select USER_ID, LOGIN, LOGIN_TIME from USER_LOGIN;
  type cur_type is table of cur_user_login%rowtype index by pls_integer; ---  index by binary_integer 是数组的固定语法
  cur_tab cur_type;
begin
  open cur_user_login;
  loop
    fetch cur_user_login bulk collect
into cur_tab limit 5000; ----一次批量获取5000行；
    forall i in 1 .. cur_tab.count
      insert into USER_LOGIN_BAK
(USER_ID, LOGIN, LOGIN_TIME)
      values
(cur_tab(i).USER_ID, cur_tab(i).LOGIN, cur_tab(i).LOGIN_TIME);
    commit;
exit when cur_user_login%notfound or cur_user_login%notfound is null;
end loop;
  close cur_user_login;
  commit;
end;

   
   
除了批量游标，还有是动态游标：
什么是动态游标呢?
在执行的时候才知道什么sql 语句，而不是一开始就知道：
Ref cursor属于动态cursor(直到运行时才知道这条查询)。 
我们来看一个例子：
--- 这个是批量删除数据
create or replace procedure delBigTab(p_TableName in varchar2,
                                      p_Condition in VARCHAR2) AS
  type mycur is ref cursor;
  v_cur     mycur;
  v_cur_sql VARCHAR2(3000);
  Type v_rowid is table of varchar2(100) index by binary_integer; ---集合类型
  var_rowid v_rowid;
  v_count number :=0;
BEGIN
  v_cur_sql := 'select rowid from ' || p_TableName || ' where ' ||
               p_Condition || ' order by rowid';
  OPEN v_cur FOR v_cur_sql;
  LOOP
    FETCH v_cur BULK COLLECT
      INTO var_rowid LIMIT 20000;
    FORALL i IN 1 .. var_rowid.count EXECUTE IMMEDIATE
                     'delete from ' || p_TableName || ' where  rowid=:1'
                     USING var_rowid(i)
      ;
    COMMIT;
    EXIT WHEN v_cur%NOTFOUND OR v_cur%NOTFOUND IS NULL;
    v_count := var_rowid.count + v_count;
  END LOOP;
  CLOSE v_cur;
  DBMS_OUTPUT.PUT_LINE(v_count||' '||'rows deleted');
end;

用rowid

   
   
declare
  cursor mycursor is
    SELECT ROWID FROM TEST WHERE XXX = XXXX order by rowid;
  --------按ROWID排序的Cursor，删除条件是XXX=XXXX，根据实际情
  况来定。 type rowid_table_type is table of rowid index by pls_integer;
  v_rowid  rowid_table_type;
BEGIN
  open mycursor;
  loop
    fetch mycursor bulk collect
      into v_rowid limit 5000;
    --------每次处理5000行，也就是每5000行一提交
    exit when v_rowid.count = 0;
    forall i in v_rowid.first .. v_rowid.last
      delete from test where rowid = v_rowid(i);
    commit;
  end loop;
  close mycursor;
END;
/
declare
  cursor cur_user_login is
select USER_ID, LOGIN, LOGIN_TIME from USER_LOGIN;
  type cur_type is table of cur_user_login%rowtype index by pls_integer; ---  index by pls_integer 是数组的固定语法
  cur_tab cur_type;
begin
  open cur_user_login;
  loop
    fetch cur_user_login bulk collect
into cur_tab limit 5000; ----一次批量获取5000行；
    forall i in 1 .. cur_tab.count
      insert into USER_LOGIN_BAK
(USER_ID, LOGIN, LOGIN_TIME)
      values
(cur_tab(i).USER_ID, cur_tab(i).LOGIN, cur_tab(i).LOGIN_TIME);
    commit;
exit when cur_user_login%notfound or cur_user_login%notfound is null;
end loop;
  close cur_user_login;
  commit;
end;

    
    
declare
  cursor mycursor is
    SELECT ROWID FROM t_user_active_log_bak; --order by rowid;
 type rowid_table_type is table of rowid index by pls_integer;
  v_rowid  rowid_table_type;
BEGIN
  open mycursor;
  loop
    fetch mycursor bulk collect
      into v_rowid limit 5000;
    exit when v_rowid.count = 0;
    forall i in v_rowid.first .. v_rowid.last
      update t_user_active_log_bak set ctime=ctime+90 where rowid = v_rowid(i);
    commit;
  end loop;
  close mycursor;
END;