oracle 定时修改表,Oracle的表数据导入存储过程及定时器创建修改

一.Oracle的存储过程

学习基础：https://www..com/taiguyiba/p/7809310.html

二.使用Oracle的存储过程来将一个数据库B的表数据导入另一数据库A中

1.首先创建公共数据库链接，在A数据库创建对数据库B的链接

CREATE PUBLIC DATABASE LINK "test_B"

CONNECT TO "ORACLE" IDENTIFIED BY VALUES ':1'

USING '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS =(PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

)

(CONNECT_DATA =(SERVICE_NAME = ORCL))

)';

2.在A数据库写存储过程

CREATE OR REPLACE

PROCEDURE TRANSFER_test

IS

cursor C_test is

select YU_JIAN_SHU_JU_BIAO_ID,

CHE_PAI_HAO,

CHE_SHEN_TU_PIANT

from test@test_B

where YU_JIAN_JIAN_CE_SHI_JIAN>=(sysdate-1);

V_test C_test%ROWTYPE;

begin

OPEN C_test;

loop

FETCH C_test INTO V_test;

EXIT WHEN C_ZCJL_R_YJSJB%NOTFOUND;

MERGE INTO test A

USING (SELECT V_ZCJL_R_YJSJB.YU_JIAN_SHU_JU_BIAO_ID as YU_JIAN_SHU_JU_BIAO_ID FROM DUAL) B

ON (A.YU_JIAN_SHU_JU_BIAO_ID=B.YU_JIAN_SHU_JU_BIAO_ID)

WHEN MATCHED THEN

UPDATE SET

CHE_PAI_HAO=V_ZCJL_R_YJSJB.CHE_PAI_HAO,

CHE_SHEN_TU_PIANT=V_ZCJL_R_YJSJB.CHE_SHEN_TU_PIANT

WHEN NOT MATCHED THEN

INSERT(

YU_JIAN_SHU_JU_BIAO_ID,

CHE_PAI_HAO,

CHE_SHEN_TU_PIANT)

VALUES(

V_ZCJL_R_YJSJB.YU_JIAN_SHU_JU_BIAO_ID,

V_ZCJL_R_YJSJB.CHE_PAI_HAO,

V_ZCJL_R_YJSJB.CHE_SHEN_TU_PIANT

);

END LOOP;

COMMIT;

CLOSE C_test;

END ;

三.创建定时器

##定时器创建

declare

job number;

begin

sys.dbms_job.submit(job,'TRANSFER_test;',sysdate,'TRUNC(sysdate) + 1 +2/ (24)');

end;

##定时器修改

begin

dbms_job.interval(10,'TRUNC(sysdate) + 1 +2/ (24)');//每天凌晨2点

commit;

end;

##查看定时器视图

select * from dba_jobs;

select * from all_jobs;

select * from user_jobs;