Orcale中 merge into 用法

实际开发过程中，给客户写了一个接口，放到测试环境，但客户测试需要实时数据，想了一个办法，建立dblink,建立同义词，通过存储过程执行merge into 插入数据，建立job定时执行。

1建立dblink 

有两种方式：

a，通过netManger 的服务名直接创建

-- Create database link 
create database link DLIK_CSM
  connect to test1 identified by test1
  using 'orcl_server';--服务名

b,直接使用TNS的地址

create database link DLIK_CSM
  connect to test1 identified by test1
  using ' 
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl_server)
    )
  )';

View Code

2建立同义词

-- Create the synonym 
create or replace synonym TB_USER_1 --创建名为TB_USER_1的同义词
  for TB_USER@DILK_CSM.REGRESS.RDBMS.DEV.US.ORACLE.COM;

View Code

建了同义词就可以把这个同义词当作类似一个视图来处理了

select * from TB_USER_1

3建立存储过程（通过同义词作为数据源再插入这边同义词的表里）

create or replace procedure interface_importdata(BeginDate in varchar2,
                                                 EndDate   in varchar2,
                                                 v_code    out number,
                                                 v_msg     out varchar2) is
begin

  merge into TB_USER a
  using (select *
           from TB_USER_1
          where f_userstate between to_date(BeginDate, 'yyyy-mm-dd') and
                to_date(EndDate, 'yyyy-mm-dd')) b
  on (a.f_userid = b.f_userid)
  when matched then
    update
       set 
      --a.f_userid=b.f_userid--出现在匹配条件中的都不能作为set的条件（注）
       a.f_usercode=b.f_usercode,
       a.f_username=b.f_username,
       a.f_password=b.f_password,
       a.f_positionid=b.f_positionid,
       a.f_entrydate=b.f_entrydate,
       a.f_mobile=b.f_mobile,
       a.f_userstate=b.f_userstate,
       a.f_state=b.f_state
  when not matched then
    insert
      (f_userid,
       f_usercode,
       f_username,
       f_password,
       f_positionid,
       f_entrydate,
       f_mobile,
       f_userstate,
       f_state
       )
    values
      (b.f_userid,
       b.f_usercode,
       b.f_username,
       b.f_password,
       b.f_positionid,
       b.f_entrydate,
       b.f_mobile,
       b.f_userstate,
       b.f_state);
       commit;
       end;

View Code

4建立job

这里转自battier的相关job知识，便于查询

一、ORACLE JOB概述

定时在后台执行相关操作。

例如：每天晚上点将一张表的数据保存到另一张表中；定时备份数据库等操作。

二、创建ORACLE JOB

1、  创建表

/*1、创建表*/

create table job_table(

       j_id number(12),

       j_date date

)

2、  创建序列

/*2、创建序列*/

create sequence job_seq

minvalue 1 maxvalue 999999999999 start with 1 increment by 1;

3、  创建过程

/*3、创建存储过程*/

create or replace procedure job_pro

            is

            begin

                   insert into system.job_table values(job_seq.nextval,sysdate);

 end;

4、  创建job

declare job number;

begin

                         sys.dbms_job.submit(job => job,

                      what => 'job_pro;',

              next_date => to_date('13-07-2011 00:01:00',

'dd-mm-yyyy hh24:mi:ss'),

                      interval => 'trunc(sysdate)+1+1/1440');

               commit;

end;

           

            代码讲解：

            job：该参数是工作号（由submit（）过程返回的binary_integer），它在问题中唯一标识工作。

            what: 可以理解为，具体要做的操作，这里指向一个存储过程。

            next_date：该参数指示何时此操作被执行。

            interval：该参数指示一个工作重复执行的频度。

            no_parse：该蚕食指示此工作在提交时或执行时是否应进行语法分析，TRUE：标识第一次执行时进行语法分析；FALSE：指示立即进行语法分析

 

注意：如果在job => job, 这里，改成“job => :job,”，oracle则会报“并非所有变量都已绑定”异常。声明了变量后，请把这个冒号去掉！

 PS：dbms_job package包含以下过程：

 ·Broken()过程；

·change()过程；

·interval()过程；

·isubmit()过程；

·next_date()过程；

·remove()过程；

·run()过程；

·submit()过程；

·user_export()过程；

·what()过程；

--第一步：
create table JOB_TEST(a date);
--第二步
select * from job_test;
--第三步
 create or replace procedure JOB_PRO_TEST as
    begin
    insert into JOB_TEST values(sysdate);
    end;

    
/*declare jobtest number;
begin
sys.dbms_job.submit(job => :jobtest,--去掉冒号
what => 'JOB_PRO_TEST;',
next_date => sysdate,
interval => 'sysdate+1/64800');--每天64800秒，即每秒执行一次，但是实际oracle的jobs不能精确到秒，这里只是为了快速测试效果
commit;
end;*/

/*declare jobtest number;
begin
                         sys.dbms_job.submit(job => job,
                      what => 'JOB_PRO_TEST;',
              next_date =>sysdate ,
                      interval => 'TRUNC(SYSDATE + 1)');
               commit;
end;*/

declare jobtest number;
begin
sys.dbms_job.submit(jobtest,'job_pro_test;',sysdate,'sysdate+1/64800');
commit;
end;

　　

 

转载于:https://www.cnblogs.com/CodingHiding/p/A1005.html