2013年10月2日 星期三

PL/SQL 範例

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;

沒有留言:

張貼留言