oracle大表复制存储过程

牛颖初

create or replace procedure P_HPS_TABLEBAK(P_TableOri   in VARCHAR2,
                                           P_TableBack  in VARCHAR2,
                                           P_TemplateId in VARCHAR2,
                                           P_Message    out INTEGER) as

  V_I           number(10);
  V_J           number(10);
  V_ERRCODE     varchar2(100);
  V_ERRMSG      varchar2(100);
  V_COMMITNUM   number := 10000;
  V_COLUMNNAMES varchar2(1000) := '';
  V_COLUMNNAME  T_HPS_TEMPLATE.COLUMN_NAME%type;
  n_insertAll   number := 0;
  v_sql         varchar(500) := 'select * from ' || P_TableOri;
  v_tempsql     varchar(500) := 'select COLUMN_NAME from T_HPS_TEMPLATE where TEMPLATE_ID = ''' ||
                                P_TemplateId || '''';
  O_CALLDATA    PACK_HPS.HPS_CURSOR; --临时游标
begin
  --开始执行复制，锁定表
  EXECUTE IMMEDIATE 'UPDATE T_HPS_TEMPLATE_OUTBOUND SET IS_LOCKED=3 WHERE OUTBOUND_NAME = ''' ||
                    P_TableOri || '''';
  COMMIT;
  EXECUTE IMMEDIATE 'select count(*) from ' || P_TableOri
    into n_insertAll;
  OPEN O_CALLDATA FOR v_tempsql;
  LOOP
    FETCH O_CALLDATA
      INTO V_COLUMNNAME;
    EXIT WHEN O_CALLDATA%NOTFOUND;
    V_COLUMNNAMES := V_COLUMNNAMES || ',' || V_COLUMNNAME;
  END LOOP;
  close O_CALLDATA;
  V_COLUMNNAMES := SUBSTR(V_COLUMNNAMES, 2);
  if n_insertAll > 0 then
    EXECUTE IMMEDIATE 'create table  ' || P_TableBack ||
                      ' as select * from  ' || P_TableOri ||
                      ' where  1!=1 ';
    V_I := MOD(n_insertAll, V_COMMITNUM);
    V_J := n_insertAll / V_COMMITNUM;
    if V_I = 0 then
      V_I := V_J;
    else
      V_I := V_J + 1;
    end if;
    for i in 1 .. V_I loop
      v_sql := 'insert into ' || P_TableBack || ' select ' || V_COLUMNNAMES ||
               ' from (select row_.*, rownum rownum_  from (select *' ||
               ' from ' || P_TableOri || ') row_ where rownum <= ' ||
               V_COMMITNUM * i || ' ) where rownum_ >=' ||
               (V_COMMITNUM * (i - 1) + 1);
      EXECUTE IMMEDIATE v_sql;
      COMMIT;
    end loop;
    EXECUTE IMMEDIATE 'select count(*) from ' || P_TableBack
      into P_Message;
  else
    P_Message := 0;
  end if;
  --任务执行完成，解除表锁定
  EXECUTE IMMEDIATE 'UPDATE T_HPS_TEMPLATE_OUTBOUND SET IS_LOCKED=0 WHERE OUTBOUND_NAME = ''' ||
                    P_TableOri || '''';
  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    --执行失败，解除表锁定
    EXECUTE IMMEDIATE 'UPDATE T_HPS_TEMPLATE_OUTBOUND SET IS_LOCKED=0 WHERE OUTBOUND_NAME = ''' ||
                      P_TableOri || '''';
    COMMIT;
    V_ERRCODE := SQLCODE;
    V_ERRMSG  := SQLERRM;
    INSERT INTO T_HPS_DEBUGMSG
      (LOGTIME, OBJNAME, MESSAGE)
    VALUES
      (SYSDATE, 'P_HPS_TABLEBAK', V_ERRCODE || ':' || V_ERRMSG);
    OPEN O_CALLDATA FOR
      SELECT 1 FROM DUAL WHERE 1 = 2;
    COMMIT;
    RETURN;
end P_HPS_TABLEBAK;