存储过程清库

MyBatis调用存储过程清库

清库存储过程（清库注意使用EXISTS）

1.接收入参的存储过程（一）

CREATE OR REPLACE 
PROCEDURE CLEAR_PLATFORM_DATA(P_platformId IN NUMBER)
  IS
  v_platformStatus VARCHAR2(100);
  BEGIN
    SELECT ONLINE_STATUS INTO v_platformStatus FROM T_PLATFORM_BUSI WHERE PLATFORM_ID = P_platformId;
    IF v_platformStatus='ONLINE' THEN
      RETURN;
        ELSIF v_platformStatus='OUTLINE' THEN

            DELETE FROM T_USER_PERSONAL t WHERE EXISTS (SELECT 1 FROM T_USER WHERE T_USER.ID = t.ID AND T_USER.PLATFORM_ID = P_platformId);
            DELETE FROM T_USER_ORGANIZATION t WHERE EXISTS (SELECT 1 FROM T_USER WHERE T_USER.ID = t.ID AND T_USER.PLATFORM_ID = P_platformId);
            DELETE FROM T_USER WHERE PLATFORM_ID = P_platformId;
            DELETE FROM T_REGISTER_ORDER WHERE PLATFORM_ID = P_platformId;

            DELETE FROM T_TRANSACTION_RECORD WHERE SOURCE_PLATFORM_ID = P_platformId;
            DELETE FROM T_TRANSACTION_ORDER WHERE PLATFORM_ID = P_platformId;

            DELETE FROM T_PROJECT_DEBENTURE_REC WHERE PLATFORM_ID = P_platformId;
            DELETE FROM T_PROJECT_DAILY_REPORT WHERE PLATFORM_ID = P_platformId;
            DELETE FROM T_PROJECT_EXTEND t WHERE EXISTS (SELECT 1 FROM T_PROJECT WHERE T_PROJECT.ID = t.ID AND T_PROJECT.BORROWER_PLATFORM_ID = P_platformId);
            DELETE FROM T_PROJECT WHERE BORROWER_PLATFORM_ID = P_platformId;
            DELETE FROM T_FREEZE_ORDER WHERE PLATFORM_ID = P_platformId;
      DELETE FROM T_USER_AUTHORIZATION WHERE PLATFORM_ID = P_platformId;

      COMMIT;
        ELSE
            NULL;
        END IF;
  END;

2.接收入参的存储过程（二）

CREATE OR REPLACE 
PROCEDURE CLEAR_ACCOUNT_PLATFORM_DATA(P_platformId IN VARCHAR) AS
BEGIN
    DELETE FROM T_ACCOUNT_FREEZE t WHERE EXISTS 
     (SELECT 1 FROM T_ACCOUNT t1 WHERE t1.ID = t.ACCOUNT_ID  AND t1.MERCHANT_NO = P_platformId);

    DELETE FROM T_ACCOUNT_HISTORY t WHERE EXISTS 
      (SELECT 1 FROM T_ACCOUNT t1 WHERE t1.ID = t.ACCOUNT_ID  AND t1.MERCHANT_NO = P_platformId);

    DELETE FROM T_ACCOUNT_SNAPSHOT t WHERE EXISTS 
      (SELECT 1 FROM T_ACCOUNT t1 WHERE t1.ID = t.ACCOUNT_ID  AND t1.MERCHANT_NO = P_platformId);

  DELETE FROM T_ACCOUNT WHERE MERCHANT_NO = P_platformId;
COMMIT;
END;

####　1.mapperxml文件

2.mapper

void callClearPlatformData(@Param(“platformId”) long platformId);

这样就可以直接使用了！！！