连接远程数据库后(源数据库),将源数据库中指定表的数据覆盖到本地数据库中的指定表
创建远程连接后,执行该存储过程
CREATE OR REPLACE PROCEDURE TSTEST.SYS_DATAMOVEEXE
AS
ERRCODE NUMBER(18,0);
ERRDESC VARCHAR2(256);
V_STEP INT;
ISLIVE NUMBER(18,0);
BEGIN
ERRCODE:=0;
ERRDESC:='';
ISLIVE:=-1; --大于0表示该表存在
--迁移数据步骤:清空TSTEST中表数据,将TS对应的表数据拷进来
--MOVE1
V_STEP:=0;
ERRDESC:='查找科室表:';
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE ProduceDepart';
INSERT INTO PRODUCEDEPART NOLOGGING
SELECT *
FROM TSTEST.PRODUCEDEPART@DBL_ORCLIU;
END;
--MOVE2
V_STEP:=0;
ISLIVE := -1;
ERRDESC:='查找物料配套主表:';
BEGIN
SELECT COUNT(1) INTO ISLIVE
FROM USER_TABLES
WHERE TABLE_NAME = 'PRODUCTMATCH';
IF ISLIVE>0 THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE PRODUCTMATCH';
INSERT INTO PRODUCTMATCH NOLOGGING
SELECT *
FROM TSTEST.PRODUCTMATCH@DBL_ORCLIU;
ELSE
EXECUTE IMMEDIATE 'CREATE TABLE PRODUCTMATCH/
(/
ID NUMBER(18,0) NOT NULL,/
CATEGORYID NUMBER(18,0) NOT NULL,/
MODELID NUMBER(18,0) NOT NULL,/
REMARK VARCHAR2(200),/
TSTOKEN VARCHAR2(20),/
CONSTRAINT PRODUCTMATCH_PK PRIMARY KEY (ID) USING INDEX/
PCTFREE 10/
INITRANS 2/
MAXTRANS 255/
TABLESPACE TSTESTING/
STORAGE(INITIAL 64K MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)/
LOGGING/
)PCTFREE 10/
MAXTRANS 255/
TABLESPACE TSTESTING/
STORAGE(INITIAL 64K MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)/
NOCACHE/
LOGGING';
INSERT INTO PRODUCTMATCH NOLOGGING
SELECT *
FROM TSTEST.PRODUCTMATCH@DBL_ORCLIU;
END IF;
END;
COMMIT;
END SYS_DATAMOVEEXE;
/
写于 2010-10-15