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

一.Oracle的存储过程

学习基础：https://www.cnblogs.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;