mysql & orcle

insert into z_mrgnratetmpl(
tmplname,productcode,contractcode,exchangecode,phase,hedgeflag,direction,zipbegindate,marginexpr,
marginrate,remark,status,zipbeginid,zipenddate,zipendid,ziplog)
select
CONCAT(TRIM(b.productname),'模版') tmplname,a.productcode,'*' contractcode,a.exchangecode,a.phase phase,'*' hedgeflag,'*' direction,
str_to_date('20170512','%Y%m%d') zipbegindate,
case when a.expr = 'OPN-000+001TRA' or a.expr = 'OPN+000+001TRA' then '上市日'
when a.expr = 'DLY-002+010TRA' then '交割日-月初-002自然月+010交易日含'
when a.expr = 'DLY-001+010TRA' then '交割日-月初-001自然月+010交易日含'
when a.expr = 'DLY-001+001TRA' then '交割日-月初-001自然月+001交易日含'
when a.expr = 'DLY-000+001TRA' then '交割日-月初+001交易日含'
when a.expr = 'DLY-001+021DAT' then '交割日-月初-001自然月+021日历日'
when a.expr = 'DLY-001+011DAT' then '交割日-月初-001自然月+011日历日'
when a.expr = 'DLY-001+001DAT' then '交割日-月初-001自然月+001日历日'
when a.expr = 'DLY-000+001DAT' then '交割日-月初+001日历日'
when a.expr = 'DLY-000+015DAT+001TRA<002TRA'
or a.expr = 'DLY-001+001TAL-001TRA<002TRA'
or a.expr = 'DLY+000+015DAT+001TRA<002TRA' then '到期日-002交易日含' else a.expr/*'N'*/ end marginexpr,
a.marginrate,NULL remark,'A' status,0 zipbeginid,str_to_date('20500101','%Y%m%d') zipenddate,0 zipendid,NULL ziplog
from
(SELECT trim(exchangeid) exchangecode,UPPER(TRIM(instrumentid)) productcode,hlongmarginratiobymoney marginrate,
trim(begindateexpr) expr,
if(@pexchangeid=exchangeid && @pinstrumentid=instrumentid,@rank:=@rank+1,@rank:=1) as phase,
@pexchangeid:=exchangeid, @pinstrumentid:=instrumentid
from (
SELECT exchangeid,instrumentid,hlongmarginratiobymoney,begindateexpr from t_exchmarginrate
ORDER BY exchangeid ASC,instrumentid ASC,hlongmarginratiobymoney ASC
) exchmarginrate_tmp, (select @pexchangeid:=null,@pinstrumentid:=null,@rank:=0) tmp
) a
left join t_product b on a.productcode = b.productid and a.exchangecode = b.exchangeid
where not exists
(SELECT * FROM z_mrgnratetmpl b
WHERE contractcode = '*' and hedgeflag = '*' and direction = '*' AND zipbegindate <= str_to_date('20170512','%Y%m%d') and zipenddate > str_to_date('20170512','%Y%m%d')
and a.exchangecode = b.exchangecode and a.productcode = b.productcode and a.phase = b.phase
);

 

SET @v_cnt = 0;
SELECT COUNT(*) INTO @v_cnt
FROM
(
SELECT exchangecode,productcode,a.marginrate,phase
FROM z_mrgnratetmpl a
WHERE contractcode = '*'
and hedgeflag = '*' and direction = '*'
AND zipbegindate <= str_to_date('20170520','%Y%m%d') and zipenddate > str_to_date('20170520','%Y%m%d')
)a
LEFT JOIN
(SELECT trim(exchangeid) exchangecode,UPPER(TRIM(instrumentid)) productcode,hlongmarginratiobymoney marginrate,
if(@pexchangeid=exchangeid && @pinstrumentid=instrumentid,@rank:=@rank+1,@rank:=1) as phase,
@pexchangeid:=exchangeid, @pinstrumentid:=instrumentid
from (
SELECT exchangeid,instrumentid,hlongmarginratiobymoney from t_exchmarginrate
ORDER BY exchangeid ASC,instrumentid ASC,hlongmarginratiobymoney ASC
) exchmarginrate_tmp, (select @pexchangeid:=null,@pinstrumentid:=null,@rank:=0) tmp
) b
ON a.exchangecode = b.exchangecode AND a.productcode = b.productcode AND a.phase = b.phase
WHERE a.marginrate = b.marginrate;
SELECT @v_cnt;

IF @v_cnt > 0 THEN
UPDATE z_mrgnratetmpl t SET t.marginrate =
(SELECT b.marginrate FROM
(SELECT trim(exchangeid) exchangecode,UPPER(TRIM(instrumentid)) productcode,hlongmarginratiobymoney marginrate,
if(@pexchangeid=exchangeid && @pinstrumentid=instrumentid,@rank:=@rank+1,@rank:=1) as phase,
@pexchangeid:=exchangeid, @pinstrumentid:=instrumentid
from (
SELECT exchangeid,instrumentid,hlongmarginratiobymoney from t_exchmarginrate
ORDER BY exchangeid ASC,instrumentid ASC,hlongmarginratiobymoney ASC
) exchmarginrate_tmp, (select @pexchangeid:=null,@pinstrumentid:=null,@rank:=0) tmp
) b
WHERE t.exchangecode = b.exchangecode AND t.productcode = b.productcode AND t.phase = b.phase
)
WHERE EXISTS
(SELECT 1 FROM
(SELECT trim(exchangeid) exchangecode,UPPER(TRIM(instrumentid)) productcode,hlongmarginratiobymoney marginrate,
if(@pexchangeid=exchangeid && @pinstrumentid=instrumentid,@rank:=@rank+1,@rank:=1) as phase,
@pexchangeid:=exchangeid, @pinstrumentid:=instrumentid
from (
SELECT exchangeid,instrumentid,hlongmarginratiobymoney from t_exchmarginrate
ORDER BY exchangeid ASC,instrumentid ASC,hlongmarginratiobymoney ASC
) exchmarginrate_tmp, (select @pexchangeid:=null,@pinstrumentid:=null,@rank:=0) tmp
) b
WHERE t.exchangecode = b.exchangecode AND t.productcode = b.productcode AND t.phase = b.phase AND t.marginrate <> b.marginrate
and t.zipbegindate <= str_to_date('20170520','%Y%m%d') and t.zipenddate > str_to_date('20170520','%Y%m%d')
)
and t.contractcode = '*' and t.hedgeflag = '*' and t.direction = '*';
END IF;

 

 

 

insert into z_mrgnratetmpl(
TMPLNAME,productcode,contractcode,EXCHANGECODE,PHASE,HEDGEFLAG,direction,ZIPBEGINDATE,MARGINEXPR,
MARGINRATE,REMARK,STATUS,ZIPBEGINID,ZIPENDDATE,ZIPENDID,ZIPLOG)
select
b.productname || '模版' TMPLNAME,a.productcode,'*' contractcode,a.EXCHANGECODE,a.phase PHASE,'*' HEDGEFLAG,'*' direction,
to_date(i_nexttrdday,'YYYYMMDD') ZIPBEGINDATE,
case when a.expr = 'OPN-000+001TRA' or a.expr = 'OPN+000+001TRA' then '上市日'
when a.expr = 'DLY-002+010TRA' then '交割日-月初-002自然月+010交易日含'
when a.expr = 'DLY-001+010TRA' then '交割日-月初-001自然月+010交易日含'
when a.expr = 'DLY-001+001TRA' then '交割日-月初-001自然月+001交易日含'
when a.expr = 'DLY-000+001TRA' then '交割日-月初+001交易日含'
when a.expr = 'DLY-001+021DAT' then '交割日-月初-001自然月+021日历日'
when a.expr = 'DLY-001+011DAT' then '交割日-月初-001自然月+011日历日'
when a.expr = 'DLY-001+001DAT' then '交割日-月初-001自然月+001日历日'
when a.expr = 'DLY-000+001DAT' then '交割日-月初+001日历日'
when a.expr = 'DLY-000+015DAT+001TRA<002TRA'
or a.expr = 'DLY-001+001TAL-001TRA<002TRA'
or a.expr = 'DLY+000+015DAT+001TRA<002TRA' then '到期日-002交易日含' else a.expr/*'N'*/ end MARGINEXPR,
a.MARGINRATE,NULL REMARK,'A' STATUS,0 ZIPBEGINID,to_date('20500101','YYYYMMDD') ZIPENDDATE,0 ZIPENDID,NULL ZIPLOG
from
(SELECT trim(exchangeid) exchangecode,UPPER(TRIM(instrumentid)) productcode,hlongmarginratiobymoney marginrate,
trim(begindateexpr) expr,row_number()OVER(PARTITION BY exchangeid,instrumentid ORDER BY hlongmarginratiobymoney) phase
FROM settlement.t_exchmarginrate@dl_ctp
) a
left join t_product b on a.productcode = b.productcode and a.exchangecode = b.exchangecode
where not exists
(SELECT * FROM z_mrgnratetmpl b
WHERE contractcode = '*' and HEDGEFLAG = '*' and direction = '*' AND zipbegindate <= to_date(i_nexttrdday,'YYYYMMDD') and zipenddate > to_date(i_nexttrdday,'YYYYMMDD')
and a.exchangecode = b.exchangecode and a.productcode = b.productcode and a.phase = b.phase
);

 

转载于:https://www.cnblogs.com/kaishan1990/p/6856525.html