oracle存储过程声明表,Oracle 利用存储过程创建表同步数据

CREATE OR REPLACE PROCEDURE TRANSFER_A  AUTHID current_user AS

CUR_ID    NUMBER(4);

PLAN_COUNT NUMBER(4);

BEGIN

EXECUTE IMMEDIATE 'drop TABLE remote_A ';

EXECUTE IMMEDIATE 'CREATE TABLE remote_A AS SELECT * FROM table_A@dblink_A';

PLAN_COUNT := 0;

DECLARE

CURSOR A_CURSOR IS

SELECT ID,NAME,INFO

FROM remote_A T ;

BEGIN

FOR REC IN A_CURSOR  LOOP

SELECT SEQ_TABLE_A.NEXTVAL INTO CUR_ID FROM DUAL;

INSERT INTO A  (ID,NAME,INFO)

SELECT CUR_ID,NAME,INFO   FROM DUAL;

PLAN_COUNT := PLAN_COUNT + 1;

END LOOP;

END;

COMMIT;

DBMS_OUTPUT.PUT_LINE('迁移完成,共迁移' || PLAN_COUNT || '条记录');

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

DBMS_OUTPUT.PUT_LINE('迁移失败');

END TRANSFER_A ;