实际开发过程中,给客户写了一个接口,放到测试环境,但客户测试需要实时数据,想了一个办法,建立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) ) )';
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;
建了同义词就可以把这个同义词当作类似一个视图来处理了
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;
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;