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