请各位大神帮忙优化一下下面的一段SQL谢谢了

各位大神，帮忙优化一下这段存储过程，最主要的是怎么去掉游标！CREATE OR REPLACE PROCEDURE test_efrule(p_sysdate in varchar2,p_data_source_cd in int) is
  V_SYSDATE DATE := TO_DATE(p_sysdate,'YYYY-MM-DD');
  V_INS_TXT VARCHAR2(3000);
  V_SEL_TXT VARCHAR2(3000);
  FUNCTION F_FILO_SCOM(F_TAB VARCHAR2)
    RETURN VARCHAR2 IS
    BEGIN
      IF F_TAB IN('PD_PP_FIXED','PD_PP_FLOAT') THEN
        RETURN ',OVERDUE_FLAG,OVERDUE_TERM,EARLY_REPAY,OVERDUE_TERM_UNIT';
      END IF;
      RETURN '';
    END F_FILO_SCOM;
  FUNCTION F_FILONIRS_SCOM(F_TAB VARCHAR2)
    RETURN VARCHAR2 IS
    BEGIN
      IF F_TAB IN('PD_PP_FIXED','PD_PP_FLOAT','PD_PP_NIRS') THEN
        RETURN ',ORG_TERM_UNIT,MAT_DATE,ORG_TERM';
      END IF;
      RETURN '';
    END F_FILONIRS_SCOM;
   FUNCTION F_FILONDD_SCOM(F_TAB VARCHAR2)
     RETURN VARCHAR2 IS
     BEGIN
       IF F_TAB IN('PD_PP_FIXED','PD_PP_FLOAT','PD_PP_NDD') THEN
         RETURN ',LAST_PAY_DATE,BUSINESS_LEVEL_CD,PAY_FREQ_UNIT,LAST_REPRICE_BAL,CHANNEL,PAY_BAL,'
             || 'PAY_FREQ,NEXT_PAY_DATE,INT_FLOAT_TYPE_CD,FLOAT_PROPORTION,BASE_FLT_VALUE,LAST_REPRICE_DATE,'
             || 'REPRICE_TYPE_CD,NEXT_REPRICE_DATE,PUBLISHER_CD,REPRICE_FREQ,REPRICE_FREQ_UNIT'
             ;
       END IF;
       RETURN '';
     END F_FILONDD_SCOM;
begin
   DELETE BP_INVALID_DATA T WHERE T.DATA_DATE = v_sysdate;
   COMMIT;
FOR J IN (SELECT TRIM(DATA_SYSTEM_SOURCE1) t1,TRIM(DATA_SYSTEM_SOURCE2) t2,TRIM(DATA_SYSTEM_SOURCE3) t3,TRIM(DATA_SYSTEM_SOURCE4) t4,CHECK_SQL,ERROR_ID
            FROM CP_VALID_CHECK_PARA
           )
    LOOP
      CASE J.t1 WHEN 1 THEN
        V_INS_TXT := 'INSERT INTO BP_INVALID_DATA'
           || '(PROJECT_ID,ACCOUNT_NUM,DATA_DATE,ORG_ID,INDUSTRY_TYPE_CD,CUST_ID,PRODUCT_ID,SUBJECT_ID,SUBJECT_TYPE_CD,'
           || 'DATA_SOURCE_CD,CURRENCY_ID,CUR_BAL,'
           || 'ORG_BAL,ORG_DATE,INT_RATE,ACCRU_BASIS_CD,INT_RATE_CD,PMT_FREQ,PMT_FREQ_UNIT,RATE_TYPE_CD,PAY_TYPE_CD,'
           || 'CUST_CATEGORY_CD,BUSINESS_NUM,DATA_SYSTEM_SOURCE,ERROR_ID'
           || F_FILO_SCOM('PD_PP_FIXED') || F_FILONIRS_SCOM('PD_PP_FIXED') || F_FILONDD_SCOM('PD_PP_FIXED')
           || ')'
           ;
      V_SEL_TXT := 'SELECT PROJECT_ID,ACCOUNT_NUM,DATA_DATE,ORG_ID,INDUSTRY_TYPE_CD,CUST_ID,PRODUCT_ID,SUBJECT_ID,SUBJECT_TYPE_CD,'
           || 'DATA_SOURCE_CD,CURRENCY_ID,CUR_BAL,'
           || 'ORG_BAL,ORG_DATE,INT_RATE,ACCRU_BASIS_CD,INT_RATE_CD,PMT_FREQ,PMT_FREQ_UNIT,RATE_TYPE_CD,PAY_TYPE_CD,'
           || 'CUST_CATEGORY_CD,BUSINESS_NUM,DATA_SYSTEM_SOURCE,''' || J.ERROR_ID  || ''''
           || F_FILO_SCOM('PD_PP_FIXED') || F_FILONIRS_SCOM('PD_PP_FIXED') || F_FILONDD_SCOM('PD_PP_FIXED')
           || ' FROM PD_PP_FIXED'
           || ' ' || J.CHECK_SQL
           || ' AND DATA_SOURCE_CD = ''' || P_DATA_SOURCE_CD || ''''
           ;
           EXECUTE IMMEDIATE V_INS_TXT || V_SEL_TXT;
      COMMIT;
      else null ;
           END CASE;
    CASE J.t2 WHEN 1 THEN
          V_INS_TXT := 'INSERT INTO BP_INVALID_DATA'
           || '(PROJECT_ID,ACCOUNT_NUM,DATA_DATE,ORG_ID,INDUSTRY_TYPE_CD,CUST_ID,PRODUCT_ID,SUBJECT_ID,SUBJECT_TYPE_CD,'
           || 'DATA_SOURCE_CD,CURRENCY_ID,CUR_BAL,'
           || 'ORG_BAL,ORG_DATE,INT_RATE,ACCRU_BASIS_CD,INT_RATE_CD,PMT_FREQ,PMT_FREQ_UNIT,RATE_TYPE_CD,PAY_TYPE_CD,'
           || 'CUST_CATEGORY_CD,BUSINESS_NUM,DATA_SYSTEM_SOURCE,ERROR_ID'
           || F_FILO_SCOM('PD_PP_FLOAT') || F_FILONIRS_SCOM('PD_PP_FLOAT') || F_FILONDD_SCOM('PD_PP_FLOAT')
           || ')'
           ;
         V_SEL_TXT := 'SELECT PROJECT_ID,ACCOUNT_NUM,DATA_DATE,ORG_ID,INDUSTRY_TYPE_CD,CUST_ID,PRODUCT_ID,SUBJECT_ID,SUBJECT_TYPE_CD,'
           || 'DATA_SOURCE_CD,CURRENCY_ID,CUR_BAL,'
           || 'ORG_BAL,ORG_DATE,INT_RATE,ACCRU_BASIS_CD,INT_RATE_CD,PMT_FREQ,PMT_FREQ_UNIT,RATE_TYPE_CD,PAY_TYPE_CD,'
           || 'CUST_CATEGORY_CD,BUSINESS_NUM,DATA_SYSTEM_SOURCE,''' || J.ERROR_ID  || ''''
           || F_FILO_SCOM('PD_PP_FLOAT') || F_FILONIRS_SCOM('PD_PP_FLOAT') || F_FILONDD_SCOM('PD_PP_FLOAT')
           || ' FROM PD_PP_FLOAT'
           || ' ' || J.CHECK_SQL
           || ' AND DATA_SOURCE_CD = ''' || P_DATA_SOURCE_CD || ''''
           ;
           EXECUTE IMMEDIATE V_INS_TXT || V_SEL_TXT;
      COMMIT;
      ELSE Null ;
        END CASE;
        CASE J.t3 when 1 THEN
           V_INS_TXT := 'INSERT INTO BP_INVALID_DATA'
           || '(PROJECT_ID,ACCOUNT_NUM,DATA_DATE,ORG_ID,INDUSTRY_TYPE_CD,CUST_ID,PRODUCT_ID,SUBJECT_ID,SUBJECT_TYPE_CD,'
           || 'DATA_SOURCE_CD,CURRENCY_ID,CUR_BAL,'
           || 'ORG_BAL,ORG_DATE,INT_RATE,ACCRU_BASIS_CD,INT_RATE_CD,PMT_FREQ,PMT_FREQ_UNIT,RATE_TYPE_CD,PAY_TYPE_CD,'
           || 'CUST_CATEGORY_CD,BUSINESS_NUM,DATA_SYSTEM_SOURCE,ERROR_ID'
           || F_FILONDD_SCOM('PD_PP_NDD')
           || ')'
           ;
         V_SEL_TXT := 'SELECT PROJECT_ID,ACCOUNT_NUM,DATA_DATE,ORG_ID,INDUSTRY_TYPE_CD,CUST_ID,PRODUCT_ID,SUBJECT_ID,SUBJECT_TYPE_CD,'
           || 'DATA_SOURCE_CD,CURRENCY_ID,CUR_BAL,'
           || 'ORG_BAL,ORG_DATE,INT_RATE,ACCRU_BASIS_CD,INT_RATE_CD,PMT_FREQ,PMT_FREQ_UNIT,RATE_TYPE_CD,PAY_TYPE_CD,'
           || 'CUST_CATEGORY_CD,BUSINESS_NUM,DATA_SYSTEM_SOURCE,''' || J.ERROR_ID  || ''''
           || F_FILONDD_SCOM('PD_PP_NDD')
           || ' FROM PD_PP_NDD'
           || ' ' || J.CHECK_SQL
           || ' AND DATA_SOURCE_CD = ''' || P_DATA_SOURCE_CD || ''''
           ;
      EXECUTE IMMEDIATE V_INS_TXT || V_SEL_TXT;
      COMMIT;
      ELSE null ;
        END CASE;
        CASE J.t4 WHEN 1 THEN
          V_INS_TXT := 'INSERT INTO BP_INVALID_DATA'
           || '(PROJECT_ID,ACCOUNT_NUM,DATA_DATE,ORG_ID,INDUSTRY_TYPE_CD,CUST_ID,PRODUCT_ID,SUBJECT_ID,SUBJECT_TYPE_CD,'
           || 'DATA_SOURCE_CD,CURRENCY_ID,CUR_BAL,'
           || 'ORG_BAL,ORG_DATE,INT_RATE,ACCRU_BASIS_CD,INT_RATE_CD,PMT_FREQ,PMT_FREQ_UNIT,RATE_TYPE_CD,PAY_TYPE_CD,'
           || 'CUST_CATEGORY_CD,BUSINESS_NUM,DATA_SYSTEM_SOURCE,ERROR_ID'
           || F_FILONIRS_SCOM('PD_PP_NIRS')
           || ')'
           ;
         V_SEL_TXT := 'SELECT PROJECT_ID,ACCOUNT_NUM,DATA_DATE,ORG_ID,INDUSTRY_TYPE_CD,CUST_ID,PRODUCT_ID,SUBJECT_ID,SUBJECT_TYPE_CD,'
           || 'DATA_SOURCE_CD,CURRENCY_ID,CUR_BAL,'
           || 'ORG_BAL,ORG_DATE,INT_RATE,ACCRU_BASIS_CD,INT_RATE_CD,PMT_FREQ,PMT_FREQ_UNIT,RATE_TYPE_CD,PAY_TYPE_CD,'
           || 'CUST_CATEGORY_CD,BUSINESS_NUM,DATA_SYSTEM_SOURCE,''' || J.ERROR_ID  || ''''
           || F_FILONIRS_SCOM('PD_PP_NIRS')
           || ' FROM PD_PP_NIRS'
           || ' ' || J.CHECK_SQL
           || ' AND DATA_SOURCE_CD = ''' || P_DATA_SOURCE_CD || ''''
           ;
      EXECUTE IMMEDIATE V_INS_TXT || V_SEL_TXT;
      COMMIT;
      ELSE Null ;
        END CASE;
    END LOOP;
end;
性能优化SQLoracle

解决方案 »

  1.   

    大神这是隐式游标哦
    FOR J IN (SELECT TRIM(DATA_SYSTEM_SOURCE1) t1,TRIM(DATA_SYSTEM_SOURCE2) t2,TRIM(DATA_SYSTEM_SOURCE3) t3,TRIM(DATA_SYSTEM_SOURCE4) t4,CHECK_SQL,ERROR_ID
                FROM CP_VALID_CHECK_PARA
               )
      

  2.   

    晕，没细看，只是搜索了一下cursor. 可以用下面的方式替换一下。para_count int := 0;
    select count(1) into para_count from CP_VALID_CHECK_PARA;for j in 1..para_count loop
    SELECT TRIM(DATA_SYSTEM_SOURCE1) into t1,TRIM(DATA_SYSTEM_SOURCE2) into t2,TRIM(DATA_SYSTEM_SOURCE3) into t3,TRIM(DATA_SYSTEM_SOURCE4) into t4,
    CHECK_SQL into t5, ERROR_ID into t6
    FROM CP_VALID_CHECK_PARA;
    if.....
       insert into ....end loop
      

  3.   

    SELECT ...INTO t1,...INTO t2好像没有这种写法吧？