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;