Oracle for loop and forall loop
PROCEDURE PROC_IF_ZIP_test_orig(IN_PARAM IN OUT PROC_PARAM_TYPE)
IS
V_MIN_ETL_DATE_1 DATE; --拉链目标表中最小ETL_DATE_1
V_MAX_ETL_DATE_1 DATE; --拉链目标表中最大ETL_DATE_1
TYPE V_CURSOR_TYPE IS REF CURSOR;
V_CURSOR V_CURSOR_TYPE;
TYPE REC_ZIPTABLE_TYPE IS RECORD
(
ETL_DATE_1 DATE,
ETL_DATE_2 DATE,
LINK_TYPE VARCHAR2(1),
MIS_DATE DATE,
V_EYA_CTRL1 VARCHAR2(50),
V_EYA_CTRL2 VARCHAR2(50),
V_EYA_CTRL3 VARCHAR2(50),
V_EYA_CTRL4 VARCHAR2(50),
V_EYA_ACCT_NUMB VARCHAR2(50),
V_EYA_PRODCODE VARCHAR2(50),
V_EYA_PROD_KEY VARCHAR2(50),
N_EYA_POTENTIAL_PAY NUMBER,
V_EYA_ALT_CCY VARCHAR2(50),
N_EYA_STRIKE_PRICE NUMBER,
N_EYA_SPOT_REF_FX1 NUMBER,
N_EYA_OTM NUMBER,
D_EYA_MLD_EXPIRY DATE,
N_EYA_EY_AMT_INV NUMBER,
N_EYA_TD_RATE NUMBER,
N_EYA_MLD_INT_RATE NUMBER,
N_EYA_EY_PRM_AMT NUMBER,
N_EYA_EY_RATE_BM NUMBER,
V_EYA_DOM_AO VARCHAR2(50),
V_EYA_MAKER_ID VARCHAR2(50),
V_EYA_SUB_STAT VARCHAR2(50),
V_EYA_INV_CCY VARCHAR2(50),
N_EYA_BOOK_DATE NUMBER,
N_EYA_EY_SPREAD1 NUMBER,
N_EYA_SPOT_RATE_EXPIRY NUMBER,
N_EYA_MIS_AMT_INV1 NUMBER,
N_EYA_DELTA NUMBER,
N_EYA_EY_SPREAD2 NUMBER,
N_EYA_BAS_CCY NUMBER,
N_EYA_FWD_FX_RATE NUMBER,
N_EYA_CINT_TOT_PAID NUMBER,
N_EYA_OPT_PRM_INV NUMBER,
N_EYA_OPT_PRM_USD NUMBER,
N_EYA_MIS_AMT_INV2 NUMBER,
V_EYA_OPT_TYPE VARCHAR2(50),
N_EYA_SPOT_REF_FX2 NUMBER,
N_EYA_OPT_PRFT_LOSS NUMBER,
N_EYA_OPT_COST NUMBER,
V_EYA_CHRG_TO_BR_IND VARCHAR2(50),
V_EYA_EY_DEAL VARCHAR2(50),
DATA_DATE INTEGER
);
TYPE V_MAIN_TYPE IS TABLE OF REC_ZIPTABLE_TYPE INDEX BY BINARY_INTEGER;
V_MAIN V_MAIN_TYPE;
/*TYPE V_MAIN_TYPE IS TABLE OF Test20181012%ROWTYPE INDEX BY BINARY_INTEGER;
V_MAIN V_MAIN_TYPE;*/
BEGIN
--1.V_SP_DATA_LINK_CLEAN
SELECT MIN(ETL_DATE_1) MIN_ETL_DATE_1,
MAX(ETL_DATE_1) MAX_ETL_DATE_1
INTO V_MIN_ETL_DATE_1, V_MAX_ETL_DATE_1
FROM Test20181012;
--拉链表中最小的日期>传入要求清理的日期则TRUNCATE
IF V_MIN_ETL_DATE_1 >= IN_PARAM.IN_BUSI_DT
THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE Test20181012';
--拉链表中最小的日期<传入要求清理的日期<=拉链表中最大的日期
--则先DELETE ETL_DATE_1>=传入要求清理的日期
--再UPDATE将ETL_DATE_2为前一日的数据的ETL_DATE_2改至99991231
ELSIF IN_PARAM.IN_BUSI_DT > V_MIN_ETL_DATE_1
AND IN_PARAM.IN_BUSI_DT <= V_MAX_ETL_DATE_1
THEN
DELETE FROM Test20181012 T
WHERE T.ETL_DATE_1 >= IN_PARAM.IN_BUSI_DT;
UPDATE Test20181012 T
SET T.ETL_DATE_2 = PKG_COMM_UTIL.FUNC_GET_OPEN_END_DT
WHERE T.ETL_DATE_2 = IN_PARAM.IN_BUSI_DT - 1;
ELSE
NULL;
END IF;
--2.V_SP_DATA_LINK
--MERGE UPDATE
MERGE INTO Test20181012 Z
USING (SELECT Y1.ROWID ROW_ID
FROM Test20181012 Y1,
(SELECT NULL AS MIS_DATE,
REGEXP_REPLACE(V_EYA_CTRL1,'^[(]*null[)]*$',NULL,1,1,'i') AS V_EYA_CTRL1,
REGEXP_REPLACE(V_EYA_CTRL2,'^[(]*null[)]*$',NULL,1,1,'i') AS V_EYA_CTRL2,
REGEXP_REPLACE(V_EYA_CTRL3,'^[(]*null[)]*$',NULL,1,1,'i') AS V_EYA_CTRL3,
REGEXP_REPLACE(V_EYA_CTRL4,'^[(]*null[)]*$',NULL,1,1,'i') AS V_EYA_CTRL4,
REGEXP_REPLACE(V_EYA_ACCT_NUMB,'^[(]*null[)]*$',NULL,1,1,'i') AS V_EYA_ACCT_NUMB,
REGEXP_REPLACE(V_EYA_PRODCODE,'^[(]*null[)]*$',NULL,1,1,'i') AS V_EYA_PRODCODE,
REGEXP_REPLACE(V_EYA_PROD_KEY,'^[(]*null[)]*$',NULL,1,1,'i') AS V_EYA_PROD_KEY,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_POTENTIAL_PAY),'NULL',NULL,N_EYA_POTENTIAL_PAY)) AS N_EYA_POTENTIAL_PAY,
REGEXP_REPLACE(V_EYA_ALT_CCY,'^[(]*null[)]*$',NULL,1,1,'i') AS V_EYA_ALT_CCY,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_STRIKE_PRICE),'NULL',NULL,N_EYA_STRIKE_PRICE)) AS N_EYA_STRIKE_PRICE,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_SPOT_REF_FX1),'NULL',NULL,N_EYA_SPOT_REF_FX1)) AS N_EYA_SPOT_REF_FX1,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_OTM),'NULL',NULL,N_EYA_OTM)) AS N_EYA_OTM,
PKG_COMM_UTIL.FUNC_TO_DATE(DECODE(UPPER(D_EYA_MLD_EXPIRY),'NULL',NULL,D_EYA_MLD_EXPIRY),'YYYY-MM-DD') AS D_EYA_MLD_EXPIRY,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_EY_AMT_INV),'NULL',NULL,N_EYA_EY_AMT_INV)) AS N_EYA_EY_AMT_INV,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_TD_RATE),'NULL',NULL,N_EYA_TD_RATE)) AS N_EYA_TD_RATE,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_MLD_INT_RATE),'NULL',NULL,N_EYA_MLD_INT_RATE)) AS N_EYA_MLD_INT_RATE,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_EY_PRM_AMT),'NULL',NULL,N_EYA_EY_PRM_AMT)) AS N_EYA_EY_PRM_AMT,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_EY_RATE_BM),'NULL',NULL,N_EYA_EY_RATE_BM)) AS N_EYA_EY_RATE_BM,
REGEXP_REPLACE(V_EYA_DOM_AO,'^[(]*null[)]*$',NULL,1,1,'i') AS V_EYA_DOM_AO,
REGEXP_REPLACE(V_EYA_MAKER_ID,'^[(]*null[)]*$',NULL,1,1,'i') AS V_EYA_MAKER_ID,
REGEXP_REPLACE(V_EYA_SUB_STAT,'^[(]*null[)]*$',NULL,1,1,'i') AS V_EYA_SUB_STAT,
REGEXP_REPLACE(V_EYA_INV_CCY, '^[(]*null[)]*$',NULL,1,1,'i') AS V_EYA_INV_CCY,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_BOOK_DATE),'NULL',NULL,N_EYA_BOOK_DATE)) AS N_EYA_BOOK_DATE,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_EY_SPREAD1),'NULL',NULL,N_EYA_EY_SPREAD1)) AS N_EYA_EY_SPREAD1,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_SPOT_RATE_EXPIRY),'NULL',NULL,N_EYA_SPOT_RATE_EXPIRY)) AS N_EYA_SPOT_RATE_EXPIRY,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_MIS_AMT_INV1),'NULL',NULL,N_EYA_MIS_AMT_INV1)) AS N_EYA_MIS_AMT_INV1,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_DELTA),'NULL',NULL,N_EYA_DELTA)) AS N_EYA_DELTA,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_EY_SPREAD2),'NULL',NULL,N_EYA_EY_SPREAD2)) AS N_EYA_EY_SPREAD2,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_BAS_CCY),'NULL',NULL,N_EYA_BAS_CCY)) AS N_EYA_BAS_CCY,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_FWD_FX_RATE),'NULL',NULL,N_EYA_FWD_FX_RATE)) AS N_EYA_FWD_FX_RATE,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_CINT_TOT_PAID),'NULL',NULL,N_EYA_CINT_TOT_PAID)) AS N_EYA_CINT_TOT_PAID,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_OPT_PRM_INV),'NULL',NULL,N_EYA_OPT_PRM_INV)) AS N_EYA_OPT_PRM_INV,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_OPT_PRM_USD),'NULL',NULL,N_EYA_OPT_PRM_USD)) AS N_EYA_OPT_PRM_USD,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_MIS_AMT_INV2),'NULL',NULL,N_EYA_MIS_AMT_INV2)) AS N_EYA_MIS_AMT_INV2,
REGEXP_REPLACE(V_EYA_OPT_TYPE,'^[(]*null[)]*$',NULL,1,1,'i') AS V_EYA_OPT_TYPE,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_SPOT_REF_FX2),'NULL',NULL,N_EYA_SPOT_REF_FX2)) AS N_EYA_SPOT_REF_FX2,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_OPT_PRFT_LOSS),'NULL',NULL,N_EYA_OPT_PRFT_LOSS)) AS N_EYA_OPT_PRFT_LOSS,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_OPT_COST),'NULL',NULL,N_EYA_OPT_COST)) AS N_EYA_OPT_COST,
REGEXP_REPLACE(V_EYA_CHRG_TO_BR_IND,'^[(]*null[)]*$',NULL,1,1,'i') AS V_EYA_CHRG_TO_BR_IND,
REGEXP_REPLACE(V_EYA_EY_DEAL,'^[(]*null[)]*$',NULL,1,1,'i') AS V_EYA_EY_DEAL,
0 AS DATA_DATE
FROM ODS_IN_DWH_test_orig
WHERE /*BUSI_DT = IN_PARAM.IN_BUSI_DT*/MIS_DATE = TO_CHAR(IN_PARAM.IN_BUSI_DT,'YYYY-MM-DD')
--按天跑批
/*
SELECT * FROM(
SELECT ROW_NUMBER() OVER(PARTITION BY V_EYA_EY_DEAL ORDER BY MIS_DATE DESC) RN,I.*
FROM test_orig I
WHERE DATA_DATE='2018-01-31') TP
WHERE RN=1;
*/
MINUS
SELECT
MIS_DATE,
V_EYA_CTRL1,
V_EYA_CTRL2,
V_EYA_CTRL3,
V_EYA_CTRL4,
V_EYA_ACCT_NUMB,
V_EYA_PRODCODE,
V_EYA_PROD_KEY,
N_EYA_POTENTIAL_PAY,
V_EYA_ALT_CCY,
N_EYA_STRIKE_PRICE,
N_EYA_SPOT_REF_FX1,
N_EYA_OTM,
D_EYA_MLD_EXPIRY,
N_EYA_EY_AMT_INV,
N_EYA_TD_RATE,
N_EYA_MLD_INT_RATE,
N_EYA_EY_PRM_AMT,
N_EYA_EY_RATE_BM,
V_EYA_DOM_AO,
V_EYA_MAKER_ID,
V_EYA_SUB_STAT,
V_EYA_INV_CCY,
N_EYA_BOOK_DATE,
N_EYA_EY_SPREAD1,
N_EYA_SPOT_RATE_EXPIRY,
N_EYA_MIS_AMT_INV1,
N_EYA_DELTA,
N_EYA_EY_SPREAD2,
N_EYA_BAS_CCY,
N_EYA_FWD_FX_RATE,
N_EYA_CINT_TOT_PAID,
N_EYA_OPT_PRM_INV,
N_EYA_OPT_PRM_USD,
N_EYA_MIS_AMT_INV2,
V_EYA_OPT_TYPE,
N_EYA_SPOT_REF_FX2,
N_EYA_OPT_PRFT_LOSS,
N_EYA_OPT_COST,
V_EYA_CHRG_TO_BR_IND,
V_EYA_EY_DEAL,
DATA_DATE
FROM Test20181012
WHERE ETL_DATE_2 = PKG_COMM_UTIL.FUNC_GET_OPEN_END_DT) Y2
WHERE Y1.V_EYA_ACCT_NUMB = Y2.V_EYA_ACCT_NUMB
AND Y1.V_EYA_PRODCODE = Y2.V_EYA_PRODCODE
AND Y1.V_EYA_PROD_KEY = Y2.V_EYA_PROD_KEY) Y
ON (Y.ROW_ID = Z.ROWID)
WHEN MATCHED THEN
UPDATE
SET Z.ETL_DATE_2 = IN_PARAM.IN_BUSI_DT - 1
WHERE Z.ETL_DATE_2 = PKG_COMM_UTIL.FUNC_GET_OPEN_END_DT;
--INSERT
OPEN V_CURSOR FOR
SELECT /*DATA_ID,
BUSI_DT,
BRANCH_CD,
BRANCH_CD_ORG,
SRC_SYS_NO,
INPUT_DT,*/
IN_PARAM.IN_BUSI_DT,
PKG_COMM_UTIL.FUNC_GET_OPEN_END_DT,
'N',
--PKG_COMM_UTIL.FUNC_TO_DATE(MIS_DATE,'YYYY-MM-DD') AS MIS_DATE,
NULL AS MIS_DATE,
REGEXP_REPLACE(V_EYA_CTRL1, '^[(]*null[)]*$', NULL, 1, 1, 'i') AS V_EYA_CTRL1,
REGEXP_REPLACE(V_EYA_CTRL2, '^[(]*null[)]*$', NULL, 1, 1, 'i') AS V_EYA_CTRL2,
REGEXP_REPLACE(V_EYA_CTRL3, '^[(]*null[)]*$', NULL, 1, 1, 'i') AS V_EYA_CTRL3,
REGEXP_REPLACE(V_EYA_CTRL4, '^[(]*null[)]*$', NULL, 1, 1, 'i') AS V_EYA_CTRL4,
REGEXP_REPLACE(V_EYA_ACCT_NUMB,'^[(]*null[)]*$',NULL,1, 1,'i') AS V_EYA_ACCT_NUMB,
REGEXP_REPLACE(V_EYA_PRODCODE, '^[(]*null[)]*$', NULL, 1, 1, 'i') AS V_EYA_PRODCODE,
REGEXP_REPLACE(V_EYA_PROD_KEY, '^[(]*null[)]*$', NULL, 1, 1, 'i') AS V_EYA_PROD_KEY,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_POTENTIAL_PAY),'NULL',NULL,N_EYA_POTENTIAL_PAY)) AS N_EYA_POTENTIAL_PAY,
REGEXP_REPLACE(V_EYA_ALT_CCY, '^[(]*null[)]*$', NULL, 1, 1, 'i') AS V_EYA_ALT_CCY,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_STRIKE_PRICE),'NULL',NULL,N_EYA_STRIKE_PRICE)) AS N_EYA_STRIKE_PRICE,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_SPOT_REF_FX1),'NULL',NULL,N_EYA_SPOT_REF_FX1)) AS N_EYA_SPOT_REF_FX1,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_OTM), 'NULL', NULL, N_EYA_OTM)) AS N_EYA_OTM,
PKG_COMM_UTIL.FUNC_TO_DATE(DECODE(UPPER(D_EYA_MLD_EXPIRY),'NULL',NULL,D_EYA_MLD_EXPIRY),'YYYY-MM-DD') AS D_EYA_MLD_EXPIRY,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_EY_AMT_INV),'NULL',NULL,N_EYA_EY_AMT_INV)) AS N_EYA_EY_AMT_INV,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_TD_RATE),'NULL',NULL,N_EYA_TD_RATE)) AS N_EYA_TD_RATE,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_MLD_INT_RATE),'NULL',NULL,N_EYA_MLD_INT_RATE)) AS N_EYA_MLD_INT_RATE,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_EY_PRM_AMT),'NULL',NULL,N_EYA_EY_PRM_AMT)) AS N_EYA_EY_PRM_AMT,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_EY_RATE_BM),'NULL',NULL,N_EYA_EY_RATE_BM)) AS N_EYA_EY_RATE_BM,
REGEXP_REPLACE(V_EYA_DOM_AO, '^[(]*null[)]*$', NULL, 1, 1, 'i') AS V_EYA_DOM_AO,
REGEXP_REPLACE(V_EYA_MAKER_ID, '^[(]*null[)]*$', NULL, 1, 1, 'i') AS V_EYA_MAKER_ID,
REGEXP_REPLACE(V_EYA_SUB_STAT, '^[(]*null[)]*$', NULL, 1, 1, 'i') AS V_EYA_SUB_STAT,
REGEXP_REPLACE(V_EYA_INV_CCY, '^[(]*null[)]*$', NULL, 1, 1, 'i') AS V_EYA_INV_CCY,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_BOOK_DATE),'NULL',NULL,N_EYA_BOOK_DATE)) AS N_EYA_BOOK_DATE,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_EY_SPREAD1),'NULL',NULL,N_EYA_EY_SPREAD1)) AS N_EYA_EY_SPREAD1,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_SPOT_RATE_EXPIRY),'NULL',NULL,N_EYA_SPOT_RATE_EXPIRY)) AS N_EYA_SPOT_RATE_EXPIRY,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_MIS_AMT_INV1),'NULL',NULL,N_EYA_MIS_AMT_INV1)) AS N_EYA_MIS_AMT_INV1,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_DELTA), 'NULL', NULL, N_EYA_DELTA)) AS N_EYA_DELTA,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_EY_SPREAD2),'NULL',NULL,N_EYA_EY_SPREAD2)) AS N_EYA_EY_SPREAD2,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_BAS_CCY),'NULL',NULL,N_EYA_BAS_CCY)) AS N_EYA_BAS_CCY,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_FWD_FX_RATE),'NULL',NULL,N_EYA_FWD_FX_RATE)) AS N_EYA_FWD_FX_RATE,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_CINT_TOT_PAID),'NULL',NULL,N_EYA_CINT_TOT_PAID)) AS N_EYA_CINT_TOT_PAID,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_OPT_PRM_INV),'NULL',NULL,N_EYA_OPT_PRM_INV)) AS N_EYA_OPT_PRM_INV,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_OPT_PRM_USD),'NULL',NULL,N_EYA_OPT_PRM_USD)) AS N_EYA_OPT_PRM_USD,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_MIS_AMT_INV2),'NULL',NULL,N_EYA_MIS_AMT_INV2)) AS N_EYA_MIS_AMT_INV2,
REGEXP_REPLACE(V_EYA_OPT_TYPE, '^[(]*null[)]*$', NULL, 1, 1, 'i') AS V_EYA_OPT_TYPE,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_SPOT_REF_FX2),'NULL',NULL,N_EYA_SPOT_REF_FX2)) AS N_EYA_SPOT_REF_FX2,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_OPT_PRFT_LOSS),'NULL',NULL,N_EYA_OPT_PRFT_LOSS)) AS N_EYA_OPT_PRFT_LOSS,
PKG_COMM_UTIL.FUNC_TO_NUMBER(DECODE(UPPER(N_EYA_OPT_COST),'NULL',NULL,N_EYA_OPT_COST)) AS N_EYA_OPT_COST,
REGEXP_REPLACE(V_EYA_CHRG_TO_BR_IND,'^[(]*null[)]*$',NULL,1,1,'i') AS V_EYA_CHRG_TO_BR_IND,
REGEXP_REPLACE(V_EYA_EY_DEAL, '^[(]*null[)]*$', NULL, 1, 1, 'i') AS V_EYA_EY_DEAL,
0 AS DATA_DATE
FROM ODS_IN_DWH_test_orig T
WHERE /*BUSI_DT = IN_PARAM.IN_BUSI_DT*/MIS_DATE = TO_CHAR(IN_PARAM.IN_BUSI_DT,'YYYY-MM-DD')
MINUS
SELECT
/*DATA_ID,
BUSI_DT,
BRANCH_CD,
BRANCH_CD_ORG,
SRC_SYS_NO,
INPUT_DT, */
IN_PARAM.IN_BUSI_DT,
PKG_COMM_UTIL.FUNC_GET_OPEN_END_DT,
'N',
MIS_DATE,
V_EYA_CTRL1,
V_EYA_CTRL2,
V_EYA_CTRL3,
V_EYA_CTRL4,
V_EYA_ACCT_NUMB,
V_EYA_PRODCODE,
V_EYA_PROD_KEY,
N_EYA_POTENTIAL_PAY,
V_EYA_ALT_CCY,
N_EYA_STRIKE_PRICE,
N_EYA_SPOT_REF_FX1,
N_EYA_OTM,
D_EYA_MLD_EXPIRY,
N_EYA_EY_AMT_INV,
N_EYA_TD_RATE,
N_EYA_MLD_INT_RATE,
N_EYA_EY_PRM_AMT,
N_EYA_EY_RATE_BM,
V_EYA_DOM_AO,
V_EYA_MAKER_ID,
V_EYA_SUB_STAT,
V_EYA_INV_CCY,
N_EYA_BOOK_DATE,
N_EYA_EY_SPREAD1,
N_EYA_SPOT_RATE_EXPIRY,
N_EYA_MIS_AMT_INV1,
N_EYA_DELTA,
N_EYA_EY_SPREAD2,
N_EYA_BAS_CCY,
N_EYA_FWD_FX_RATE,
N_EYA_CINT_TOT_PAID,
N_EYA_OPT_PRM_INV,
N_EYA_OPT_PRM_USD,
N_EYA_MIS_AMT_INV2,
V_EYA_OPT_TYPE,
N_EYA_SPOT_REF_FX2,
N_EYA_OPT_PRFT_LOSS,
N_EYA_OPT_COST,
V_EYA_CHRG_TO_BR_IND,
V_EYA_EY_DEAL,
DATA_DATE
FROM Test20181012
WHERE ETL_DATE_2 = PKG_COMM_UTIL.FUNC_GET_OPEN_END_DT;
--批量插入增量数据
LOOP
FETCH V_CURSOR BULK COLLECT
INTO V_MAIN LIMIT 10000;
IF V_MAIN.COUNT > 0 THEN
FOR K IN V_MAIN.FIRST .. V_MAIN.LAST LOOP
INSERT INTO Test20181012(ETL_DATE_1,
ETL_DATE_2,
LINK_TYPE,
MIS_DATE,
V_EYA_CTRL1,
V_EYA_CTRL2,
V_EYA_CTRL3,
V_EYA_CTRL4,
V_EYA_ACCT_NUMB,
V_EYA_PRODCODE,
V_EYA_PROD_KEY,
N_EYA_POTENTIAL_PAY,
V_EYA_ALT_CCY,
N_EYA_STRIKE_PRICE,
N_EYA_SPOT_REF_FX1,
N_EYA_OTM,
D_EYA_MLD_EXPIRY,
N_EYA_EY_AMT_INV,
N_EYA_TD_RATE,
N_EYA_MLD_INT_RATE,
N_EYA_EY_PRM_AMT,
N_EYA_EY_RATE_BM,
V_EYA_DOM_AO,
V_EYA_MAKER_ID,
V_EYA_SUB_STAT,
V_EYA_INV_CCY,
N_EYA_BOOK_DATE,
N_EYA_EY_SPREAD1,
N_EYA_SPOT_RATE_EXPIRY,
N_EYA_MIS_AMT_INV1,
N_EYA_DELTA,
N_EYA_EY_SPREAD2,
N_EYA_BAS_CCY,
N_EYA_FWD_FX_RATE,
N_EYA_CINT_TOT_PAID,
N_EYA_OPT_PRM_INV,
N_EYA_OPT_PRM_USD,
N_EYA_MIS_AMT_INV2,
V_EYA_OPT_TYPE,
N_EYA_SPOT_REF_FX2,
N_EYA_OPT_PRFT_LOSS,
N_EYA_OPT_COST,
V_EYA_CHRG_TO_BR_IND,
V_EYA_EY_DEAL,
DATA_DATE )
VALUES (V_MAIN(K).ETL_DATE_1,
V_MAIN(K).ETL_DATE_2,
V_MAIN(K).LINK_TYPE,
V_MAIN(K).MIS_DATE,
V_MAIN(K).V_EYA_CTRL1,
V_MAIN(K).V_EYA_CTRL2,
V_MAIN(K).V_EYA_CTRL3,
V_MAIN(K).V_EYA_CTRL4,
V_MAIN(K).V_EYA_ACCT_NUMB,
V_MAIN(K).V_EYA_PRODCODE,
V_MAIN(K).V_EYA_PROD_KEY,
V_MAIN(K).N_EYA_POTENTIAL_PAY,
V_MAIN(K).V_EYA_ALT_CCY,
V_MAIN(K).N_EYA_STRIKE_PRICE,
V_MAIN(K).N_EYA_SPOT_REF_FX1,
V_MAIN(K).N_EYA_OTM,
V_MAIN(K).D_EYA_MLD_EXPIRY,
V_MAIN(K).N_EYA_EY_AMT_INV,
V_MAIN(K).N_EYA_TD_RATE,
V_MAIN(K).N_EYA_MLD_INT_RATE,
V_MAIN(K).N_EYA_EY_PRM_AMT,
V_MAIN(K).N_EYA_EY_RATE_BM,
V_MAIN(K).V_EYA_DOM_AO,
V_MAIN(K).V_EYA_MAKER_ID,
V_MAIN(K).V_EYA_SUB_STAT,
V_MAIN(K).V_EYA_INV_CCY,
V_MAIN(K).N_EYA_BOOK_DATE,
V_MAIN(K).N_EYA_EY_SPREAD1,
V_MAIN(K).N_EYA_SPOT_RATE_EXPIRY,
V_MAIN(K).N_EYA_MIS_AMT_INV1,
V_MAIN(K).N_EYA_DELTA,
V_MAIN(K).N_EYA_EY_SPREAD2,
V_MAIN(K).N_EYA_BAS_CCY,
V_MAIN(K).N_EYA_FWD_FX_RATE,
V_MAIN(K).N_EYA_CINT_TOT_PAID,
V_MAIN(K).N_EYA_OPT_PRM_INV,
V_MAIN(K).N_EYA_OPT_PRM_USD,
V_MAIN(K).N_EYA_MIS_AMT_INV2,
V_MAIN(K).V_EYA_OPT_TYPE,
V_MAIN(K).N_EYA_SPOT_REF_FX2,
V_MAIN(K).N_EYA_OPT_PRFT_LOSS,
V_MAIN(K).N_EYA_OPT_COST,
V_MAIN(K).V_EYA_CHRG_TO_BR_IND,
V_MAIN(K).V_EYA_EY_DEAL,
V_MAIN(K).DATA_DATE);
END LOOP;
/*
--FORALL写法(不能罗列出个字段名称)
FORALL I IN V_MAIN.FIRST .. V_MAIN.LAST
INSERT INTO Test20181012\*(ETL_DATE_1,
ETL_DATE_2,
LINK_TYPE,
MIS_DATE,
V_EYA_CTRL1,
V_EYA_CTRL2,
V_EYA_CTRL3,
V_EYA_CTRL4,
V_EYA_ACCT_NUMB,
V_EYA_PRODCODE,
V_EYA_PROD_KEY,
N_EYA_POTENTIAL_PAY,
V_EYA_ALT_CCY,
N_EYA_STRIKE_PRICE,
N_EYA_SPOT_REF_FX1,
N_EYA_OTM,
D_EYA_MLD_EXPIRY,
N_EYA_EY_AMT_INV,
N_EYA_TD_RATE,
N_EYA_MLD_INT_RATE,
N_EYA_EY_PRM_AMT,
N_EYA_EY_RATE_BM,
V_EYA_DOM_AO,
V_EYA_MAKER_ID,
V_EYA_SUB_STAT,
V_EYA_INV_CCY,
N_EYA_BOOK_DATE,
N_EYA_EY_SPREAD1,
N_EYA_SPOT_RATE_EXPIRY,
N_EYA_MIS_AMT_INV1,
N_EYA_DELTA,
N_EYA_EY_SPREAD2,
N_EYA_BAS_CCY,
N_EYA_FWD_FX_RATE,
N_EYA_CINT_TOT_PAID,
N_EYA_OPT_PRM_INV,
N_EYA_OPT_PRM_USD,
N_EYA_MIS_AMT_INV2,
V_EYA_OPT_TYPE,
N_EYA_SPOT_REF_FX2,
N_EYA_OPT_PRFT_LOSS,
N_EYA_OPT_COST,
V_EYA_CHRG_TO_BR_IND,
V_EYA_EY_DEAL,
DATA_DATE)*\
VALUES V_MAIN(I);
*/
--COMMIT;
END IF;
EXIT WHEN V_CURSOR%NOTFOUND;
END LOOP;
CLOSE V_CURSOR;
--UPDATE新增的字段
UPDATE Test20181012 T
SET DATA_ID = PKG_COMM_UTIL.FUNC_GET_DATA_ID(),
BUSI_DT = IN_PARAM.IN_BUSI_DT,
BRANCH_CD ='0000',
BRANCH_CD_ORG='0000',
SRC_SYS_NO = 'DWH',
INPUT_DT = SYSDATE
WHERE T.DATA_ID IS NULL;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
IN_PARAM.OUT_RET := 1;
IN_PARAM.OUT_RET_MSG := SQLERRM;
END;
All for u
