------------------------------------------------
create table DEP_WRTF_RSLT
(
sr_no_id NUMBER(28) not null,
dl_cd VARCHAR2(50) not null,
prdct_cd VARCHAR2(50),
acmltd_wrtf_ratio NUMBER(20,10),
exstng_st VARCHAR2(8),
wrtf_dt DATE,
wrtf_mthd VARCHAR2(8),
nr_leg_base_rmn_amnt NUMBER(30,10),
rmng_fr_leg_base_dl_amnt NUMBER(30,10),
nr_leg_rsk_rmn_amnt NUMBER(30,10),
fr_leg_rsk_rmn_amnt NUMBER(30,10),
nr_leg_cny_rmn_amnt NUMBER(30,10),
nr_leg_term_rmn_amnt NUMBER(30,10),
fleg_cny_rmn_amnt NUMBER(30,10),
rmng_fr_leg_term_dl_amnt NUMBER(30,10),
crtr VARCHAR2(100),
crt_tm TIMESTAMP(6),
updtr VARCHAR2(100),
upd_tm TIMESTAMP(6)
);
------------------------------------------------
create sequence SEQ_FRGN_CCY_IMPLD_IR_CRV_PRC minvalue 1 maxvalue 9999999999999999999999999999 start with 1 increment by 1 cache 20;
create table unique_test (id number, fname varchar2(20), lname varchar2(20), address varchar2(100), email varchar2(40), constraint name_unique unique(fname,lname))
创建表unique_test并将fname,lname联合起来作唯一主键
ALTER TABLE FX_OPTN_IMPLD_VLTLTY_CRV_PRCNG ADD CONSTRAINT XPKFX_OPTN_IMPLD_PRCNG PRIMARY KEY (SR_NO_ID);
alter table FX_DL_DTL add constraint UN_FX_DL_DTL unique (DL_CD);
--------------------------------------------------------
--初始化规则：
--数据范围：远端起息日大于当前日期，并且存续状态字段为空的外汇掉期交易
--存续状态：初始化为有效；若交易已应急删除，初始化为撤销；
--累计冲销比例：初始化设置为0.00；
--近端留存金额：初始化设置为近端基准货币金额；
--远端留存金额：初始化设置为远端基准货币金额；
--未删除的交易
INSERT INTO TSDEAL.WRTF_RSLT_INFO
(SR_NO_ID,
DL_CD,
PRDCT_CD,
ACMLTD_WRTF_RATIO,
EXSTNG_ST,
NR_LEG_BASE_RMN_AMNT,
NR_LEG_TERM_RMN_AMNT,
RMNG_FR_LEG_BASE_DL_AMNT,
RMNG_FR_LEG_TERM_DL_AMNT,
NR_LEG_RSK_RMN_AMNT,
FR_LEG_RSK_RMN_AMNT,
CRTR,
CRT_TM)
SELECT TSDEAL.SEQ_WRTF_RSLT_INFO.NEXTVAL,
A.DL_CD,
A.PRDCT_CD,
0,
'',
A.NR_LEG_BASE_AMNT,
A.NR_LEG_TERM_AMNT,
A.FR_LEG_BASE_AMNT,
A.FR_LEG_TERM_AMNT,
A.NR_LEG_RSK_AMNT,
A.FR_LEG_RSK_AMNT,
'ts-u-dealcommon',
SYSDATE
FROM TSDEAL.FX_DL_DTL_HSTRY A
WHERE A.DL_ST IN ('', '')
AND A.FR_LEG_VL_DT > TRUNC(SYSDATE)
AND A.PRDCT_CD = 'FXSWP'
AND NOT EXISTS
(SELECT 1 FROM TSDEAL.WRTF_RSLT_INFO B WHERE A.DL_CD = B.DL_CD);
---------------------------------------------------------------------------
INSERT INTO TSDEAL.WRTF_RSLT_INFO
(SR_NO_ID,
DL_CD,
PRDCT_CD,
ACMLTD_WRTF_RATIO,
EXSTNG_ST,
NR_LEG_BASE_RMN_AMNT,
NR_LEG_TERM_RMN_AMNT,
RMNG_FR_LEG_BASE_DL_AMNT,
RMNG_FR_LEG_TERM_DL_AMNT,
NR_LEG_RSK_RMN_AMNT,
FR_LEG_RSK_RMN_AMNT,
CRTR,
CRT_TM,
UPDTR,
UPD_TM)
SELECT TSDEAL.SEQ_WRTF_RSLT_INFO.NEXTVAL,
DL_CD,
PRDCT_CD,
ACMLTD_WRTF_RATIO,
EXSTNG_ST,
NR_LEG_BASE_RMN_AMNT,
NR_LEG_TERM_RMN_AMNT,
RMNG_FR_LEG_BASE_DL_AMNT,
RMNG_FR_LEG_TERM_DL_AMNT,
NR_LEG_RSK_RMN_AMNT,
FR_LEG_RSK_RMN_AMNT,
CRTR,
CRT_TM,
UPDTR,
UPD_TM
FROM TSDEAL.DEP_WRTF_RSLT T
WHERE T.WRTF_DT <= TRUNC(TO_DATE('2018-06-30','yyyy-MM-dd'));
--------------------------------------------------------------------------------
***********************************************************************************
DECLARE
BEGIN
FOR A IN(SELECT USR_CD,INSTN_CD FROM TSDEV.USR_WDGT WHERE WDGT_ID = '')
LOOP
INSERT INTO TSDEV.USR_WDGT (PRVLG_ID, USR_CD, WDGT_ID, CRT_TM, CRTR, UPD_TM, UPDTR, WDGT_SHOW_TP, INSTN_CD) VALUES (TSDEV.SEQ_USR_WDGT.NEXTVAL, A.USR_CD, '', SYSDATE, 'TS', SYSDATE, 'TS', '', A.INSTN_CD);
INSERT INTO TSDEV.USR_WDGT (PRVLG_ID, USR_CD, WDGT_ID, CRT_TM, CRTR, UPD_TM, UPDTR, WDGT_SHOW_TP, INSTN_CD) VALUES (TSDEV.SEQ_USR_WDGT.NEXTVAL, A.USR_CD, '', SYSDATE, 'TS', SYSDATE, 'TS', '', A.INSTN_CD);
INSERT INTO TSDEV.USR_WDGT (PRVLG_ID, USR_CD, WDGT_ID, CRT_TM, CRTR, UPD_TM, UPDTR, WDGT_SHOW_TP, INSTN_CD) VALUES (TSDEV.SEQ_USR_WDGT.NEXTVAL, A.USR_CD, '', SYSDATE, 'TS', SYSDATE, 'TS', '', A.INSTN_CD);
END LOOP;
COMMIT;
END;
-----------------------------------------------------------------------------------
DECLARE
BEGIN
FOR A IN (
SELECT T.USR_CD AS USR_CD,T.INSTN_CD AS INSTN_CD FROM TSDEV.USR_INFO T WHERE T.USR_TP = ''
) LOOP
DELETE FROM TSDEV.USR_WDGT U WHERE U.USR_CD = A.USR_CD AND U.INSTN_CD = A.INSTN_CD AND U.WDGT_ID = '';
INSERT INTO TSDEV.USR_WDGT (PRVLG_ID, USR_CD, WDGT_ID, CRT_TM, CRTR, UPD_TM, UPDTR, WDGT_SHOW_TP, INSTN_CD) VALUES (TSDEV.SEQ_USR_WDGT.NEXTVAL, A.USR_CD, '', SYSDATE, 'TS', SYSDATE, 'TS', '', A.INSTN_CD);
END LOOP;
COMMIT;
END;
***********************************************************************************
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$业务sql$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
SELECT tm,
agt,
prd,
swapPnt,
nrPrd,
nrPrice,
nrVol,
frPrd,
frPrice,
frVol,
CASE
WHEN QTNG_INSTN_CD = '' OR QT_INSTN_AGNCY_INSTN_CD = '' THEN
DECODE(tkrDir, 'B', 'S', 'S', 'B', tkrDir)
ELSE
tkrDir
END AS tkrDir,
CASE
WHEN QTNG_INSTN_CD = '' OR QT_INSTN_AGNCY_INSTN_CD = '' THEN
DECODE(nrDir, 'B', 'S', 'S', 'B', nrDir)
ELSE
nrDir
END AS nrDir,
CASE
WHEN QTNG_INSTN_CD = '' OR QT_INSTN_AGNCY_INSTN_CD = '' THEN
DECODE(frDir, 'B', 'S', 'S', 'B', frDir)
ELSE
frDir
END AS frDir
FROM (SELECT T.DL_TM + 0 AS TM,
T.PRD AS prd,
T.SWAP_PNT AS swapPnt,
T.TKR_DIR AS tkrDir,
T.NR_LEG_TERM AS nrPrd,
T.NLEG_FWD_PNT AS nrPrice,
T.INTR_NLEG_BASE_CCY_TRDNG_DIR AS nrDir,
T.NR_LEG_RSK_AMNT AS nrVol,
T.FR_LEG_TERM AS frPrd,
T.FLEG_FWD_PNT AS frPrice,
T.INTR_FLEG_BASE_CCY_DIR AS frDir,
T.FR_LEG_RSK_AMNT AS frVol,
T.QTNG_INSTN_CD,
T.QT_INSTN_AGNCY_INSTN_CD,
'N' AS AGT
FROM TSDEAL.GLD_DL_DTL T
WHERE T.DT_CNFRM = TO_DATE('2018-07-19', 'yyyy-MM-dd')
AND T.PRDCT_CD = ''
AND T.PRD = ''
AND T.CCY_PAIR_CD = ''
AND T.TRDNG_MD_CD = 'ODM'
AND T.TRDNG_MTHD_CD = 'Matching'
AND trunc(T.DL_TM, 'mi') =
to_date('2019-07-19', 'YYYY-MM-DD HH24:MI')
--AND (
AND T.DL_ST <> ''
UNION ALL
SELECT T.DL_TM + 0 AS TM,
T.PRD AS prd,
T.SWAP_PNT AS swapPnt,
T.TKR_DIR AS tkrDir,
T.NR_LEG_TERM AS nrPrd,
T.NLEG_FWD_PNT AS nrPrice,
T.INTR_NLEG_BASE_CCY_TRDNG_DIR AS nrDir,
T.NR_LEG_RSK_AMNT AS nrVol,
T.FR_LEG_TERM AS frPrd,
T.FLEG_FWD_PNT AS frPrice,
T.INTR_FLEG_BASE_CCY_DIR AS frDir,
T.FR_LEG_RSK_AMNT AS frVol,
T.QTNG_INSTN_CD,
T.QT_INSTN_AGNCY_INSTN_CD,
'N' AS AGT
FROM TSDEAL.GLD_DL_DTL_HSTRY T
WHERE T.DT_CNFRM = TO_DATE('2018-07-19', 'yyyy-MM-dd')
AND T.PRDCT_CD = ''
AND T.PRD = ''
AND T.CCY_PAIR_CD = ''
AND T.TRDNG_MD_CD = 'QDM'
AND T.FLR_TP = 'Bilateral'
AND trunc(T.DL_TM, 'mi') =
to_date('2018-07-19', 'YYYY-MM-DD HH24:MI')
-- AND (
AND T.DL_ST <> ''
UNION ALL
SELECT T.DL_TM + 0 AS TM,
T.PRD AS prd,
T.SWAP_PNT AS swapPnt,
T.TKR_DIR AS tkrDir,
T.NR_LEG_TERM AS nrPrd,
T.NLEG_FWD_PNT AS nrPrice,
T.INTR_NLEG_BASE_CCY_TRDNG_DIR AS nrDir,
T.NR_LEG_RSK_AMNT AS nrVol,
T.FR_LEG_TERM AS frPrd,
T.FLEG_FWD_PNT AS frPrice,
T.INTR_FLEG_BASE_CCY_DIR AS frDir,
T.FR_LEG_RSK_AMNT AS frVol,
T.QTNG_INSTN_CD,
T.QT_INSTN_AGNCY_INSTN_CD,
'Y' AS AGT
FROM TSDEAL.GLD_DL_DTL_HSTRY T
WHERE T.DT_CNFRM = TO_DATE('2018-07-19', 'yyyy-MM-dd')
AND T.PRDCT_CD = ''
AND T.PRD = ''
AND T.CCY_PAIR_CD = ''
AND T.TRDNG_MD_CD = 'QDM'
AND T.FLR_TP = 'Bilateral'
AND trunc(SYSDATE, 'mi') =
to_date('2018-07-19', 'YYYY-MM-DD HH24:MI')
-- AND ( T.MKT_DATA_UPD_TM
AND T.DL_ST <> ''
UNION ALL
SELECT T.DL_TM + 0 AS TM,
tm,
price,
vol,
agt,
CASE
WHEN QTNG_INSTN_CD = '' OR QT_INSTN_AGNCY_INSTN_CD = '' THEN
DECODE('1>2', 'B', 'S', 'S', 'B', '缺省值')
ELSE
''
END AS dir,
T.QTNG_INSTN_CD,
T.QT_INSTN_AGNCY_INSTN_CD,
'Y' AS AGT
FROM TSDEAL.GLD_DL_DTL_HSTRY T
WHERE T.DT_CNFRM = TO_DATE('2018-07-19', 'yyyy-MM-dd')
AND T.PRDCT_CD = ''
AND UPPER(T.PRD) IN ('', '')
AND T.CCY_PAIR_CD = ''
AND T.TRDNG_MD_CD = 'QDM'
AND T.FLR_TP = 'Bilateral'
AND trunc(SYSDATE, 'mi') =
to_date('2018-07-19', 'YYYY-MM-DD HH24:MI')
-- AND (
AND T.DL_ST <> '');
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$业务sql$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$