P处理的中有大量判断条件是的sql写法

本文链接：https://blog.csdn.net/cdz1022/article/details/7199536

在执行P处理时需要根据传经来的参数来写where条件的时候可能因为，参数太多，或者传值的范围太多而导致sql的where条件不好写。嵌套什么的都out。

最好的方法是，定义一个变量如： v_strSQL VARCHAR2(20000);然后执行的时候写： v_strSQL := 'SELECT FTBL.NENDO, FTBL.NENDO_MEISHO, FTBL.FUKUSHIKI_RENBAN, FTBL.SHIKKO_NO || ''_'' || FTBL.SHIKKO_NO_EDABAN AS SHISHUTSUNO, ' ||
        'CASE FTBL.DATA_SHUBETSU WHEN ''0'' THEN ''歳入'' WHEN ''1'' THEN ''歳出'' WHEN ''3'' THEN ''資産'' WHEN ''4'' THEN ''決算整理'' WHEN ''5'' THEN ''マッチング'' WHEN ''7'' THEN ''連結'' ELSE '''' END AS KBN, ' ||
        'TO_CHAR(TO_DATE(FTBL.SHIKKO_DATE, ''YYYY/MM/DD''), ''YYYY/MM/DD'') AS SHIKKO_DATE, ' ||
        'FTBL.SHUKANKA, FTBL.SHUKANKA_REF.BUMON_MEISHO, ' ||
        'FTBL.SHIKKO_KIKAN, FTBL.SHIKKO_KIKAN_REF.BUMON_MEISHO, ' ||
        'FTBL.KAIKEI, FTBL.KAIKEI_REF.KAIKEI_MEISHO, ' ||
        'NVL(FTBL.TANSHIKI_KAMOKU.KANKOMOKU.KAN, '''') AS KAN, NVL(FTBL.TANSHIKI_KAMOKU.KANKOMOKU.KOU, '''') AS KOU, NVL(FTBL.TANSHIKI_KAMOKU.KANKOMOKU.MOKU, '''') AS MOKU, ' ||
        'NVL(FTBL.TANSHIKI_KAMOKU.JIGYO.JIGYO_DAI, '''') AS DAI, NVL(FTBL.TANSHIKI_KAMOKU.JIGYO.JIGYO_CHU, '''') AS CHU, ' ||
        'NVL(FTBL.TANSHIKI_KAMOKU.JIGYO.JIGYO_SHO, '''') AS SHO, NVL(FTBL.TANSHIKI_KAMOKU.JIGYO.JIGYO_SAI, '''') AS SAI, ' ||
        'NVL(FTBL.TANSHIKI_KAMOKU.SETSU.SETSU, '''') AS SETSU, NVL(FTBL.TANSHIKI_KAMOKU.SETSU.SAISETSU, '''') AS SAISETSU, NVL(FTBL.TANSHIKI_KAMOKU.SETSU.SAISAI, '''') AS SAISAI, ' ||
        'CASE DATA_SHUBETSU WHEN ''0'' THEN NVL(FTBL.SAINYU_KAMOKU_REF.TANSHIKI_MEISHO,'''') WHEN ''1'' THEN NVL(FTBL.SAISHTSU_KAMOKU_REF.TANSHIKI_MEISHO,'''') ELSE '''' END AS KAMOKUMEI, ' ||
        'SWK_NT.KARIKATA.KINGAKU, SWK_NT.REMARKS ' ||
        'FROM FUKUSHIKI_TAB FTBL, TABLE(FTBL.SHIWAKE_DATA) SWK_NT ' ||
        'WHERE FTBL.DATA_SHUBETSU = ''' || wkDataType || ''' AND FTBL.NENDO = ' || wkNendo ||
        ' AND FTBL.SHIKKO_DATE >= ''' || wkShikkoDateFrom || ''' AND FTBL.SHIKKO_DATE <= ''' || wkShikkoDateTo || ''' ';在需要根据参数来写where的时候：

IF wkDataType = '1' THEN    /* ﾃﾞｰﾀ種別が歳出の場合のみ */
    IF wkDaiJigyo != '00' THEN    /* 大事業 */
      v_strSQL := v_strSQL || 'AND FTBL.SAISHTSU_DAI_JIGYO_REF.HYOJI_TANSHIKI.JIGYO.JIGYO_DAI = ''' || wkDaiJigyo || ''' ';
    END IF;
    IF wkChuJigyo != '00' THEN    /* 中事業 */
      v_strSQL := v_strSQL || 'AND FTBL.SAISHTSU_CHU_JIGYO_REF.HYOJI_TANSHIKI.JIGYO.JIGYO_CHU = ''' || wkChuJigyo || ''' ';
    END IF;
    IF wkShoJigyo != '00' THEN    /* 小事業 */
      v_strSQL := v_strSQL || 'AND FTBL.SAISHTSU_SHO_JIGYO_REF.HYOJI_TANSHIKI.JIGYO.JIGYO_SHO = ''' || wkShoJigyo || ''' ';
    END IF;
    IF wkSaiJigyo != '00' THEN    /* 細事業 */
      v_strSQL := v_strSQL || 'AND FTBL.SAISHTSU_SAI_JIGYO_REF.HYOJI_TANSHIKI.JIGYO.JIGYO_SAI = ''' || wkSaiJigyo || ''' ';
    END IF;
END IF;