使用存储过程动态同步表数据

发表于 | 分类于 | 本文总阅读量 次

创建存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE OR REPLACE
PROCEDURE SYNFINANCIALDATA
AS
COUNTT NUMBER;
BEGIN

-----------------------1.TESTTABLE01数据同步------------------------
BEGIN
SELECT COUNT(*) INTO COUNTT FROM TABS WHERE TABLE_NAME='TESTTABLE01' || TO_CHAR(SYSDATE,'YYYY');
IF(COUNTT=0) THEN
EXECUTE IMMEDIATE 'CREATE TABLE TESTTABLE01' || TO_CHAR(SYSDATE,'YYYY') || ' AS SELECT * FROM TESTTABLE WHERE 1=2';
END IF;
EXECUTE IMMEDIATE 'DELETE FROM TESTTABLE01' || TO_CHAR(SYSDATE,'YYYY');
COMMIT;
EXECUTE IMMEDIATE 'MERGE INTO TESTTABLE01' || TO_CHAR(SYSDATE,'YYYY') .....
COMMIT;
END;
-----------------------1.TESTTABLE02数据同步------------------------
BEGIN
SELECT COUNT(*) INTO COUNTT FROM TABS WHERE TABLE_NAME='TESTTABLE02' || TO_CHAR(SYSDATE,'YYYY');
IF(COUNTT=0) THEN
EXECUTE IMMEDIATE 'CREATE TABLE TESTTABLE02' || TO_CHAR(SYSDATE,'YYYY') || ' AS SELECT * FROM TESTTABLE WHERE 1=2';
END IF;
EXECUTE IMMEDIATE 'DELETE FROM TESTTABLE02' || TO_CHAR(SYSDATE,'YYYY');
COMMIT;
EXECUTE IMMEDIATE 'MERGE INTO TESTTABLE02' || TO_CHAR(SYSDATE,'YYYY') .....
COMMIT;
END;

END;

调用存储过程

1
CALL SYNFINANCIALDATA();
0%