sql backup

create or replace procedure P_updateas
begin
update security_price p
set p.closing_price =
(select MIN(a.close_price) KEEP(DENSE_RANK last ORDER BY trade_date)
from DATAAPP1.cicc_index_daily@WIND a
where to_date(a.trade_date, 'yyyymmdd') <=p.pricing_date
and a.sec_name = '沪深300全收益'
and a.ticker = 'h00300'
)
where p.security_id = 1000006869;

update security_price p
set p.closing_price =
(select MIN(a.close_price) KEEP(DENSE_RANK last ORDER BY trade_date)
from DATAAPP1.cicc_index_daily@WIND a
where to_date(a.trade_date, 'yyyymmdd') <=p.pricing_date
and a.sec_name = '中证500全收益'
and a.ticker = 'h00905'
)
where p.security_id = 1000029000000002;

update security_price p
set p.closing_price =
(select MIN(a.close_price) KEEP(DENSE_RANK last ORDER BY trade_date)
from DATAAPP1.cicc_index_daily@WIND a
where to_date(a.trade_date, 'yyyymmdd') <=p.pricing_date
and a.sec_name = '上证50全收益'
and a.ticker = 'h00016'
)
where p.security_id = 1000034700000001;

update security s
set s.latest_close_price =
(select MIN(a.close_price) KEEP(DENSE_RANK last ORDER BY trade_date)
from DATAAPP1.cicc_index_daily@WIND a where a.sec_name = '沪深300全收益' and a.ticker = 'h00300')
where s.id = 1000006869;

update security s
set s.latest_close_price =
(select MIN(a.close_price) KEEP(DENSE_RANK last ORDER BY trade_date)
from DATAAPP1.cicc_index_daily@WIND a where a.sec_name = '中证500全收益' and a.ticker = 'h00905')
where s.id = 1000029000000002;

update security s
set s.latest_close_price =
(select MIN(a.close_price) KEEP(DENSE_RANK last ORDER BY trade_date)
from DATAAPP1.cicc_index_daily@WIND a where a.sec_name = '上证50全收益' and a.ticker = 'h00016')
where s.id = 1000034700000001;

COMMIT;

end p_update_price;

 

declare
job number;
begin
sys.dbms_job.submit(
job => job,
what => 'p_update_price;',
next_date => to_date('10-08-2018 18:30:00', 'dd-mm-yyyy hh24:mi:ss'),
interval =>'trunc(sysdate)+1+(18*60+30)/(24*60)');
commit;
end;

转载于:https://www.cnblogs.com/jjj250/p/9458816.html