create or replace procedure proc_tzyj is
begin
insert into t_trade_activity@dw3_link.regress.rdbms.dev.us.oracle.com (select t1.Trade_Time , t1.user_phone , t1.glide_no , t1.err_code ,
t1.ACTIVITY_NUMBER , t2.activity_name , t5.org_id, t5.org_desc
from t_cash_trade_detail t1 ,
(select * from t_activity_info t
where (activity_id >=38 and activity_id <= 49 or activity_id >= 122) ) t2,
(select t3.org_id org_id, t3.term_id termid,t4.org_desc org_desc
from t_term_infomation t3, t_org t4
where t3.org_id = t4.org_id(+)
order by t3.org_id) t5
where trade_time >= trunc(sysdate - 1) and trade_time < trunc(sysdate) and t1.err_code >= 5 and t1.activity_id = t2.activity_id and t1.termid = t5.termid(+)
);
end proc_tzyj; variable job1_tz number;
begin
dbms_job.submit(job => :job1_tz,
what => 'proc_tzyj;',
next_date => TRUNC(SYSDATE + 1) ,
interval => 'TRUNC(SYSDATE + 1)');
commit;
end; begin
dbms_job.run(:job1);
end;
select * from dba_jobs;
delete from t_trade_activity@dw3_link.regress.rdbms.dev.us.oracle.com where
trade_time > trunc(sysdate -1);
select * from t_trade_activity@dw3_link.regress.rdbms.dev.us.oracle.com where
trade_time >= trunc(sysdate -1); insert into t_trade_activity@dw3_link.regress.rdbms.dev.us.oracle.com (select t1.Trade_Time , t1.user_phone , t1.glide_no , t1.err_code ,
t1.ACTIVITY_NUMBER , t2.activity_name , t5.org_id, t5.org_desc
from t_cash_trade_detail t1 ,
(select * from t_activity_info t
where (activity_id >=38 and activity_id <= 49 or activity_id >= 122) ) t2,
(select t3.org_id org_id, t3.term_id termid,t4.org_desc org_desc
from t_term_infomation t3, t_org t4
where t3.org_id = t4.org_id(+)
order by t3.org_id) t5
where trade_date >= 20130626 and trade_time >= trunc(sysdate, 'mi') - 1 and t1.err_code >= 5 and t1.activity_id = t2.activity_id and t1.termid = t5.termid(+)
); select t1.Trade_Time , t1.user_phone , t1.glide_no , t1.err_code ,
t1.ACTIVITY_NUMBER , t2.activity_name , t5.org_id, t5.org_desc
from t_cash_trade_detail t1 ,
(select * from t_activity_info t
where (activity_id >=38 and activity_id <= 49 or activity_id >= 122) ) t2,
(select t3.org_id org_id, t3.term_id termid,t4.org_desc org_desc
from t_term_infomation t3, t_org t4
where t3.org_id = t4.org_id(+)
order by t3.org_id) t5
where trade_date >= 20130625 and t1.err_code >= 5 and t1.activity_id = t2.activity_id and t1.termid = t5.termid(+)
order by trade_date desc; select t1.Trade_Time , t1.user_phone , t1.glide_no , t1.err_code ,
t1.ACTIVITY_NUMBER , t2.activity_name , t5.org_id, t5.org_desc
from t_cash_trade_detail t1 ,
(select * from t_activity_info t
where (activity_id >=38 and activity_id <= 49 or activity_id >= 122) ) t2,
(select t3.org_id org_id, t3.term_id termid,t4.org_desc org_desc
from t_term_infomation t3, t_org t4
where t3.org_id = t4.org_id(+)
order by t3.org_id) t5
where t1.err_code >= 5 and t1.activity_id = t2.activity_id and t1.termid = t5.termid(+); select t1.Trade_Time , t1.user_phone , t1.glide_no , t1.err_code ,
t1.ACTIVITY_NUMBER , t2.activity_name , t5.org_id, t5.org_desc
from t_cash_trade_detail t1 ,
(select * from t_activity_info t
where (activity_id >=38 and activity_id <= 49 or activity_id >= 122) order by activity_id ) t2,
(select t3.org_id org_id, t3.term_id termid,t4.org_desc org_desc from t_term_infomation t3, t_org t4
where t3.org_id = t4.org_id(+)
order by t3.org_id) t5
where t1.err_code >= 5 and t1.activity_id = t2.activity_id and t1.termid = t5.termid(+); select t1.Trade_Time , t1.user_phone , t1.glide_no , t1.err_code ,
t1.ACTIVITY_NUMBER , t2.activity_name , t5.org_id, t5.org_desc
from t_cash_trade_detail t1 , t_activity_info t2
, (select t3.org_id org_id, t3.term_id termid,t4.org_desc org_desc from t_term_infomation t3, t_org t4
where t3.org_id = t4.org_id(+)
order by t3.org_id) t5
where t1.err_code >= 5 and t1.activity_id = t2.activity_id(+) and t1.termid = t5.termid(+); create table test_20130625 as (select t1.Trade_Time , t1.user_phone , t1.glide_no , t1.err_code ,
t1.ACTIVITY_NUMBER , t2.activity_name , t5.org_id, t5.org_desc
from t_cash_trade_detail t1 , t_activity_info t2
, (select t3.org_id org_id, t3.term_id termid,t4.org_desc org_desc from t_term_infomation t3, t_org t4
where t3.org_id = t4.org_id(+)
order by t3.org_id) t5
where t1.err_code >= 5 and trade_date >= 20130625
and t1.activity_id = t2.activity_id(+)
and t1.termid = t5.termid(+)
); create table T_TRADE_ACTIVITY(
TRADE_TIME DATE not null,
USER_PHONE VARCHAR2(11) not null,
GLIDE_NO CHAR(14) PRIMARY KEY,
ERR_CODE VARCHAR2(22) ,
ACTIVITY_NUMBER VARCHAR2(50),
ACTIVITY_NAME VARCHAR2(100),
TERM_ID VARCHAR2(20),
ORG_DESC VARCHAR2(100)
);
comment on column T_TRADE_ACTIVITY.TRADE_TIME
is '交易时间';
comment on column T_TRADE_ACTIVITY.USER_PHONE
is '用户手机号码';
comment on column T_TRADE_ACTIVITY.GLIDE_NO
is '交易流水号,日期加6位流水号';
comment on column T_TRADE_ACTIVITY.ERR_CODE
is '错误代码,0现金充值成功,1现金充值失败需处理,2现金充值失败已退款,3现金充值失败已平帐,5预缴成功,6预缴失败需处理,7预缴失败已退款,8预缴失败已平帐,9预缴成功需退款,10预缴成功已退款,11预缴失败无需处理';
comment on column T_TRADE_ACTIVITY.ACTIVITY_NUMBER
is '预缴编号';
comment on column T_TRADE_ACTIVITY.ACTIVITY_NAME
is '优惠活动名称';
comment on column T_TRADE_ACTIVITY.TERM_ID
is '营业厅网点编号';
comment on column T_TRADE_ACTIVITY.ORG_DESC
is '营业厅名称'; DW3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = *)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dw3)
)
) create public database link dblink_tonw
connect to tztest identified by tztest
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.*)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = )
)
)';