String getDBUSERByUserIdSql = "{call " + ParameterValueProvider.getInstance().getDefaultSchema() + ".SP_TFB_CUST_INFO_CRT(?,?,?,?)}";
callableStatement = conn.prepareCall(getDBUSERByUserIdSql);
callableStatement.setString(1, session_date);
callableStatement.setInt(2,1);
callableStatement.setInt(3,200);
callableStatement.setInt(4,0 );
callableStatement.executeUpdate();
return start = start + eveone;
===================================================================
create or replace
PROCEDURE SP_TFB_CUST_INFO_CRT(
SESSION_DATE_IN CIF_ORG_CUST_INFO.SESSION_DATE%TYPE ,
START_ROW_IN INTEGER,
END_ROW_IN INTEGER,
IS_FIRST_IN INTEGER
)
AS
StoO_error INTEGER;
StoO_rowcnt INTEGER;
/*========== variable ==========*/
ORG_SEQ INTEGER;
CUST_SEQ INTEGER;
newOrgSeq INTEGER;
CRT_CUST_CHT_NAME VARCHAR2(255);
CRT_CUST_ENG_NAME VARCHAR2(255);
CUSTOMER_ID VARCHAR2(255);
FB_ID VARCHAR2(255);
--SESSION_DATE_IN VARCHAR2(255):= '201303';
CUST_ID VARCHAR2(255);
START_ROW INTEGER := START_ROW_IN;
END_ROW INTEGER := END_ROW_IN;
IS_FIRST INTEGER := IS_FIRST_IN;
/*========================CURSOR=================================*/
CURSOR c2 IS
SELECT ROWNUM,TEMP.* FROM(
SELECT distinct CUSTOMER_ID,CUST_CHT_NAME,CUST_ENG_NAME,ORG_BU_CODE,SESSION_DATE,FB_ID FROM CCR.DPS_CRT_TXN_DTL x
WHERE NOT exists (
SELECT 1 from CCR.CIF_ORG_CUST_INFO
WHERE
NVL(CUST_CHT_NAME,' ') = NVL(x.CUST_CHT_NAME,' ')
AND NVL(CUST_ENG_NAME,' ') = NVL(x.CUST_ENG_NAME,' ')
AND CUST_ID= x.CUSTOMER_ID
AND bu_code = 'FB002' AND session_date = SESSION_DATE_IN)
AND x.session_date = SESSION_DATE_IN and org_bu_code = 'FB002'
AND NVL(x.CUST_CHT_NAME||x.CUST_ENG_NAME,' ') <> ' '
and NVL(x.CUSTOMER_ID,' ') <> ' '
ORDER BY CUSTOMER_ID ) TEMP
WHERE ROWNUM BETWEEN START_ROW AND END_ROW;
CURSOR c1 IS
SELECT * FROM(
SELECT ROWNUM R,TEMP.* FROM(
SELECT distinct CUSTOMER_ID,CUST_CHT_NAME,CUST_ENG_NAME,ORG_BU_CODE,SESSION_DATE,FB_ID FROM CCR.DPS_CRT_TXN_DTL x
WHERE exists (
SELECT 1 from CCR.CIF_ORG_CUST_INFO
WHERE
NVL(CUST_CHT_NAME,' ') = NVL(x.CUST_CHT_NAME,' ')
AND NVL(CUST_ENG_NAME,' ') = NVL(x.CUST_ENG_NAME,' ')
AND CUST_ID= x.CUSTOMER_ID
AND bu_code = 'FB002' AND session_date = SESSION_DATE_IN)
AND x.session_date = SESSION_DATE_IN and org_bu_code = 'FB002'
ORDER BY CUSTOMER_ID ) TEMP
) WHERE R >= START_ROW AND R <= END_ROW
;
r1 c1%rowtype;
r2 c2%rowtype;
/*======================== FUNCTION ==================================================*/
FUNCTION get_org_seq(CUSTOMER_ID IN VARCHAR2)
return INTEGER
is
ORG_SEQ_OUT CIF_ORG_CUST_INFO.CIF_ORG_CUST_INFO_SEQ%TYPE;
begin
BEGIN
SELECT CIF_ORG_CUST_INFO_SEQ into ORG_SEQ_OUT FROM CIF_ORG_CUST_INFO
WHERE CUST_ID = CUSTOMER_ID
AND NVL(CUST_CHT_NAME,' ') = NVL(CRT_CUST_CHT_NAME,' ')
AND NVL(CUST_ENG_NAME,' ') = NVL(CRT_CUST_ENG_NAME,' ')
AND BU_CODE = 'FB002'
AND SESSION_DATE = SESSION_DATE_IN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
END;
RETURN ORG_SEQ_OUT;
end;
----------------------------------------------------------------------------------
PROCEDURE updateCRT
(
ORG_SEQ in NUMBER,
CUSTOMER_ID_IN IN VARCHAR2
)
is
BEGIN
UPDATE DPS_CRT_TXN_DTL SET CIF_ORG_CUST_INFO_SEQ = ORG_SEQ
WHERE CUSTOMER_ID = CUSTOMER_ID_IN
AND NVL(CUST_CHT_NAME,' ') = NVL(CRT_CUST_CHT_NAME,' ')
AND NVL(CUST_ENG_NAME,' ') = NVL(CRT_CUST_ENG_NAME,' ')
AND BU_CODE = 'FB002'
AND SESSION_DATE = SESSION_DATE_IN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
END;
----------------------------------------------------------------------------------------------------------
PROCEDURE updateORG
(
CUST_SEQ_IN in varchar2,
CUSTOMER_ID_IN IN VARCHAR2
)
is
BEGIN
UPDATE CIF_ORG_CUST_INFO SET CIF_CUST_INFO_SEQ = CUST_SEQ_IN
WHERE CUST_ID = CUSTOMER_ID_IN
AND NVL(CUST_CHT_NAME,' ') = NVL(CRT_CUST_CHT_NAME,' ')
AND NVL(CUST_ENG_NAME,' ') = NVL(CRT_CUST_ENG_NAME,' ')
AND BU_CODE = 'FB002'
AND SESSION_DATE = SESSION_DATE_IN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
END;
---------------------------------------------------------------------------------------------
FUNCTION get_cust_seq
(
FB_ID_IN in varchar2
)
RETURN INTEGER
is
CUST_SEQ_IN CIF_CUST_INFO.CIF_CUST_INFO_SEQ%TYPE;
begin
BEGIN
SELECT CIF_CUST_INFO_SEQ into CUST_SEQ_IN FROM CIF_CUST_INFO
WHERE FB_ID = FB_ID_IN
AND SESSION_DATE = SESSION_DATE_IN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
END;
return CUST_SEQ_IN;
end;
----------------------------------------------------------------------------------------------
PROCEDURE InsertOrg(
CRT_CUST_CHT_NAME IN varchar2,
CUST_ENG_NAME IN varchar2
)
is
begin
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
INSERT INTO ccr.CIF_ORG_CUST_INFO ( CIF_ORG_CUST_INFO_SEQ ,CIF_CUST_INFO_SEQ,CUST_ID,CUST_CHT_NAME, CUST_ENG_NAME, SESSION_DATE,BU_CODE,AGENT_ID,DATA_SOURCE )
VALUES
( newOrgSeq,CUST_SEQ,CUSTOMER_ID,CRT_CUST_CHT_NAME,CUST_ENG_NAME,SESSION_DATE_IN,'FB002','AUTO','TA01');
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
raise_application_error( SQLCODE, SQLERRM, true );
END;
end;
---------------------------------------------------------------------------------------------------
PROCEDURE get_Auto_Org_SEQ(
newOrgSeq OUT INTEGER
)
is
begin
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
select CIF_ORG_CUST_INFO_SEQ.nextval INTO newOrgSeq from dual;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
END;
end;
----------------------------------------------------------------------------------------------
------------------------------- Main (BEGIN)--------------------------------------------------
BEGIN
IF IS_FIRST = 0 THEN
/**--重複**/
OPEN c1;
LOOP
FETCH c1 INTO r1;
EXIT WHEN c1%NOTFOUND;
CRT_CUST_CHT_NAME:= r1.CUST_CHT_NAME;
CRT_CUST_ENG_NAME:= r1.CUST_ENG_NAME;
CUSTOMER_ID:= r1.CUSTOMER_ID;
FB_ID:= r1.FB_ID;
--拿ORG_SEQ
ORG_SEQ := get_org_seq(CUSTOMER_ID);
--DBMS_OUTPUT.PUT_LINE(ORG_SEQ||'----');
--更新來源
updateCRT(ORG_SEQ,CUSTOMER_ID);
CUST_SEQ := get_cust_seq(FB_ID);
--更新org
updateORG(CUST_SEQ,CUSTOMER_ID);
END LOOP ;
CLOSE c1;
ELSE
/**--不重複**/
OPEN c2;
LOOP
FETCH c2 INTO r2;
EXIT WHEN c2%NOTFOUND;
CRT_CUST_CHT_NAME:= r2.CUST_CHT_NAME;
CRT_CUST_ENG_NAME:= r2.CUST_ENG_NAME;
CUSTOMER_ID:= r2.CUSTOMER_ID;
FB_ID:= r2.FB_ID;
--取INSERT SEQ
get_Auto_Org_SEQ(newOrgSeq);
--更新org
CUST_SEQ := get_cust_seq(FB_ID);
--新增
InsertOrg(CRT_CUST_CHT_NAME,CRT_CUST_ENG_NAME);
--更新來源
updateCRT(newOrgSeq,CUSTOMER_ID);
-- DBMS_OUTPUT.PUT_LINE(CRT_CUST_CHT_NAME||'----'||CRT_CUST_ENG_NAME||'----'||CUST_SEQ||'--'||FB_ID);
END LOOP ;
CLOSE c2;
END IF;
exception
when others then
RAISE_APPLICATION_ERROR( SQLCODE, SQLERRM, TRUE );
---==============================================================================---
END SP_TFB_CUST_INFO_CRT;
沒有留言:
張貼留言