讨论一个比较复杂的查询语优化问题

select distinct br.repeal_id,

                to_char(br.create_date, 'yyyy-MM-dd hh24:mi') as create_date,

                pbo.object_alias_name objectName,

                fun_get_Repeal_package(br.repeal_id) packageName,

                br.is_audit,

                fun_get_Repeal_CompanyName(br.repeal_id) companyName,

                (select user_name

                   from sm_t_user

                  where user_id = br.create_user_id) user_name,

                case br.is_group_report

                  when 2 then

                   '已修改'

                  when 1 then

                   '已导出'

                  when 0 then

                   '未导出'

                end as is_type,

                br.object_number,

                bpd.project_id,

                br.repeal_status,

                (select stu.user_name

                   from be_t_flow_log btf, sm_t_user stu

                  where btf.sub_user_id = stu.user_id

                    and br.repeal_status = btf.billstatus

                    and br.repeal_id = btf.repeal_id

                    and btf.flow_able <> -1

                    and rownum = 1) ruser_name,

                br.create_user_id,

                br.is_group_report,

                case

                  when ((select count(*)

                           from be_t_flow_log btw

                          where btw.flow_able = 0

                            and btw.repeal_id = br.repeal_id

                            and btw.billstatus = 3

                            and btw.audit_status is null

                            and ((select count(*)

                                    from be_t_flow_log btlg

                                   where btlg.flow_able = -1

                                     and btlg.repeal_id = br.repeal_id) > 0)) > 0) then

                   'red'

                  else

                   ''

                end as color,

                'a' indexNo,

                br.blank_out,

                fun_get_Repeal_Reason(br.repeal_id) repeal_reason

  from be_t_repeal         br,

       be_t_repeal_package brp,

       be_t_project_detail bpd,

       pa_t_bid_package    pbp,

       pa_t_bid_object     pbo,

       be_t_providers      bp,

       be_t_repeal_package btr,

       BE_T_FLOW_LOG       BTFL

 where br.repeal_id = brp.repeal_id

   and brp.project_device_id = bpd.project_device_id

   and bpd.bid_package_id = pbp.bid_package_id

   and pbp.bid_object_id = pbo.bid_object_id

   and brp.provider_id = bp.provider_id

   and br.repeal_id = btr.repeal_id

   and br.repeal_id = btfl.repeal_id

   and btfl.flow_type = 1

   and br.project_id = 990000000000003243;

create or replace function fun_get_Repeal_package(p_repeal_id varchar)
  return varchar as
  cursor cur is
    select distinct pbp.package_name, pbp.package_order, pbp.child_order
      from be_t_repeal         br,
           be_t_repeal_package brp,
           be_t_project_detail bpd,
           pa_t_bid_package    pbp pa_t_bid_object pbo,
     where br.repeal_id = brp.repeal_id
       and brp.project_device_id = bpd.project_device_id
       and bpd.bid_package_id = pbp.bid_package_id
       and pbp.bid_object_id = pbo.bid_object_id
       and brp.provider_id = bp.provider_id
       and br.repeal_id = p_repeal_id
     order by pbp.package_order || pbp.child_order,
              pbp.child_order,
              pbp.package_name;
  v_package varchar2(1000);
begin
  v_package := '';
  for cur_repeal in cur loop
    v_package := v_package || cur_repeal.package_name || '、';
  end loop;
  v_package := substr(v_package, 1, length(v_package) - 1);
  return v_package;
end;

create or replace function fun_get_Repeal_CompanyName(p_repeal_id varchar)

  return varchar as

  cursor cur is

    select distinct bp.company_fullname

      from be_t_repeal         br,

           be_t_repeal_package brp,

           be_t_project_detail bpd, 

           be_t_providers bp

     where br.repeal_id = brp.repeal_id

       and brp.project_device_id = bpd.project_device_id 

       and brp.provider_id = bp.provider_id

       and br.repeal_id = p_repeal_id

     order by nlssort(bp.company_fullname, 'NLS_SORT=SCHINESE_PINYIN_M');

  v_company_name varchar2(1000);

begin

  v_company_name := '';

  for cur_repeal in cur loop

    v_company_name := v_company_name || cur_repeal.company_fullname || '、';

  end loop;

  v_company_name := substr(v_company_name, 1, length(v_company_name) - 1);

  return v_company_name;

end;

create or replace function fun_get_Repeal_Reason(p_repeal_id varchar)

  return varchar as



  v_reason_conten varchar(1000);



  cursor cur is

    select distinct bt.reason_content

      from be_t_repeal_reson bt, be_t_repeal_package btrp

     where bt.repeal_reason_id = btrp.repeal_reason_id

       and btrp.repeal_id = p_repeal_id;

begin



   v_reason_conten:='';

    for cur_re in cur loop

      v_reason_conten:=v_reason_conten||cur_re.reason_content||'、';

    end loop;



     v_reason_conten := substr(v_reason_conten, 1, length(v_reason_conten) - 1);



  return   v_reason_conten;

end;

select fun_get_repeal_companyname(990000000000001761) from dual;



select fun_get_repeal_package(990000000000001761) from dual;

SELECT DISTINCT 
    BR.REPEAL_ID,
    TO_CHAR(BR.CREATE_DATE,'yyyy-MM-dd hh24:mi') AS CREATE_DATE,
    PBO.OBJECT_ALIAS_NAME OBJECTNAME,
    FUN_GET_REPEAL_PACKAGE(BR.REPEAL_ID) PACKAGENAME,
    BR.IS_AUDIT,
    FUN_GET_REPEAL_COMPANYNAME(BR.REPEAL_ID) COMPANYNAME,
    (
        SELECT USER_NAME
        FROM SM_T_USER
        WHERE USER_ID = BR.CREATE_USER_ID
    ) USER_NAME,
    CASE BR.IS_GROUP_REPORT 
        WHEN 2 THEN 'AAA' 
        WHEN 1 THEN 'BBB' 
        WHEN 0 THEN 'CCC' 
    END AS IS_TYPE,
    BR.OBJECT_NUMBER,
    BPD.PROJECT_ID,
    BR.REPEAL_STATUS,
    (
        SELECT
            STU.USER_NAME
        FROM
        BE_T_FLOW_LOG BTF,
        SM_T_USER STU
        WHERE BTF.SUB_USER_ID = STU.USER_ID
        AND BR.REPEAL_STATUS = BTF.BILLSTATUS
        AND BR.REPEAL_ID = BTF.REPEAL_ID
        AND BTF.FLOW_ABLE <> -1
        AND ROWNUM = 1
    ) RUSER_NAME,
    BR.CREATE_USER_ID,
    BR.IS_GROUP_REPORT,
    CASE 
        WHEN
        (
            (
                SELECT COUNT(*)
                FROM
                BE_T_FLOW_LOG BTW
                WHERE BTW.FLOW_ABLE = 0
                    AND BTW.REPEAL_ID = BR.REPEAL_ID
                    AND BTW.BILLSTATUS = 3
                    AND BTW.AUDIT_STATUS IS NULL
                    AND 
                    (
                        (
                            SELECT COUNT(*)
                            FROM BE_T_FLOW_LOG BTLG
                            WHERE BTLG.FLOW_ABLE = -1
                                AND BTLG.REPEAL_ID = BR.REPEAL_ID
                        ) > 0
                    )
            ) > 0
        ) THEN 'red' 
        ELSE '' 
    END AS COLOR,
    'a' INDEXNO,
    BR.BLANK_OUT,
    FUN_GET_REPEAL_REASON(BR.REPEAL_ID) REPEAL_REASON
FROM
    BE_T_REPEAL BR,
    BE_T_REPEAL_PACKAGE BRP,
    BE_T_PROJECT_DETAIL BPD,
    PA_T_BID_PACKAGE PBP,
    PA_T_BID_OBJECT PBO,
    BE_T_PROVIDERS BP,
    BE_T_REPEAL_PACKAGE BTR,
    BE_T_FLOW_LOG BTFL
WHERE BR.REPEAL_ID = BRP.REPEAL_ID
    AND BRP.PROJECT_DEVICE_ID = BPD.PROJECT_DEVICE_ID
    AND BPD.BID_PACKAGE_ID = PBP.BID_PACKAGE_ID
    AND PBP.BID_OBJECT_ID = PBO.BID_OBJECT_ID
    AND BRP.PROVIDER_ID = BP.PROVIDER_ID
    AND BR.REPEAL_ID = BTR.REPEAL_ID
    AND BR.REPEAL_ID = BTFL.REPEAL_ID
    AND BTFL.FLOW_TYPE = 1
    AND BR.PROJECT_ID = 990000000000003243

create or replace function fun_get_Repeal_package(
    p_repeal_id varchar
)  return varchar
as
    cursor cur 
    is
        SELECT
            DISTINCT PBP.PACKAGE_NAME,
            PBP.PACKAGE_ORDER,
            PBP.CHILD_ORDER
        FROM
            BE_T_REPEAL BR,
            BE_T_REPEAL_PACKAGE BRP,
            BE_T_PROJECT_DETAIL BPD,
            PA_T_BID_PACKAGE PBP,
            PA_T_BID_OBJECT PBO,
            BE_T_PROVIDERS BP
        WHERE BR.REPEAL_ID = BRP.REPEAL_ID
            AND BRP.PROJECT_DEVICE_ID = BPD.PROJECT_DEVICE_ID
            AND BPD.BID_PACKAGE_ID = PBP.BID_PACKAGE_ID
            AND PBP.BID_OBJECT_ID = PBO.BID_OBJECT_ID
            AND BRP.PROVIDER_ID = BP.PROVIDER_ID
            AND BR.REPEAL_ID = P_REPEAL_ID
        ORDER BY 
            PBP.PACKAGE_ORDER||PBP.CHILD_ORDER,
            PBP.CHILD_ORDER,
            PBP.PACKAGE_NAME
    ;

    v_package varchar2(1000);
    begin  v_package := '';
    for cur_repeal in cur
    loop
        v_package := v_package || cur_repeal.package_name || '、';
    end loop;
    v_package := substr(v_package, 1, length(v_package) - 1);
    return v_package;
end;

--自定義集約関数→SORTED

CREATE OR REPLACE TYPE VcArrayType IS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE TYPE STR_CONN_TYPE AS OBJECT
(
    --vStr VARCHAR2(4000),
    vStr VcArrayType,

    STATIC FUNCTION
    ODCIAggregateInitialize(sctx IN OUT STR_CONN_TYPE )
    RETURN NUMBER,

    MEMBER FUNCTION
    ODCIAggregateIterate(SELF IN OUT STR_CONN_TYPE ,
        VALUE IN VARCHAR2 )
    RETURN NUMBER,

    MEMBER FUNCTION
    ODCIAggregateMerge(SELF IN OUT STR_CONN_TYPE,
        ctx2 IN STR_CONN_TYPE)
    RETURN NUMBER,

    MEMBER FUNCTION
    ODCIAggregateTerminate(SELF IN STR_CONN_TYPE,
        returnValue OUT VARCHAR2,
        flags IN NUMBER)
    RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY STR_CONN_TYPE
IS
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT STR_CONN_TYPE)
    RETURN NUMBER
    IS
    BEGIN
        --sctx := STR_CONN_TYPE(NULL);
        sctx := STR_CONN_TYPE(VcArrayType());
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateIterate(SELF IN OUT STR_CONN_TYPE,
    VALUE IN VARCHAR2 )
    RETURN NUMBER
    IS
    BEGIN
        --SELF.vStr := SELF.vStr || ';' || VALUE;
        vStr.EXTEND;
        vStr(vStr.COUNT) := VALUE;
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT STR_CONN_TYPE,
        ctx2 IN STR_CONN_TYPE)
    RETURN NUMBER
    IS
    BEGIN
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateTerminate(SELF IN STR_CONN_TYPE,
        returnValue OUT VARCHAR2,
        flags IN NUMBER)
    RETURN NUMBER
    IS
        tmp_vStr VARCHAR2(4000);
    BEGIN
        --returnValue := LTRIM(SELF.vStr,';');
        FOR rec_Value IN (
            SELECT column_value 
            FROM TABLE(vStr) 
            --ORDER BY to_number(column_value)
            ORDER BY column_value
        ) 
        LOOP
            --tmp_vStr := tmp_vStr || '_' || rec_Value.column_value;
            tmp_vStr := tmp_vStr || '、' || rec_Value.column_value;
        END LOOP;
        --returnValue := LTRIM(tmp_vStr,'_');
        returnValue := LTRIM(tmp_vStr,'、');
        RETURN ODCIConst.Success;
    END;
END;
/

CREATE OR REPLACE FUNCTION ConnStr(input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING STR_CONN_TYPE;
/

create or replace function fun_get_Repeal_package(p_repeal_id varchar)
  return varchar as
  cursor cur is
    select distinct pbp.package_name, pbp.package_order, pbp.child_order
      from be_t_repeal         br,
           be_t_repeal_package brp,
           be_t_project_detail bpd,
           pa_t_bid_package    pbp pa_t_bid_object pbo,
    --be_t_providers      bp
     where br.repeal_id = brp.repeal_id
       and brp.project_device_id = bpd.project_device_id
       and bpd.bid_package_id = pbp.bid_package_id
       and pbp.bid_object_id = pbo.bid_object_id
       and brp.provider_id = bp.provider_id
       and br.repeal_id = p_repeal_id
     order by pbp.package_order || pbp.child_order,
              pbp.child_order,
              pbp.package_name;
  v_package varchar2(1000);
begin
  v_package := '';
  dbms_output.put_line(to_char(new_time(sysdate, 'PDT', 'GMT'),
                               'yyyy.mm.dd hh24:mi:ss'));
  for cur_repeal in cur loop
    v_package := v_package || cur_repeal.package_name || '、';
  end loop;
--输出两这函数的执行的时间
  dbms_output.put_line(to_char(new_time(sysdate, 'PDT', 'GMT'),
                               'yyyy.mm.dd hh24:mi:ss'));
  v_package := substr(v_package, 1, length(v_package) - 1);
  return v_package;
end;

 select l.*,fun_get_repeal_package(l.repeal_id),fun_get_repeal_companyname(l.repeal_id) from be_t_repeal l where l.repeal_id in (990000000000001761,990000000000001741)

  select tt.* ,fun_get_repeal_package(tt.repeal_id),fun_get_repeal_companyname(tt.repeal_id),fun_get_repeal_reason(tt.repeal_id) from　上面复杂的sql去掉里面几个函数
) tt

/*
 * @(#) SQLStringFormatter.java 2005-09-14
 */
package org.ibatis.helper.common.util;

import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import java.util.Stack;

import org.ibatis.helper.common.util.ConstantMark;
import org.ibatis.helper.common.util.StringCheckUtil;


/**
 * SQL文の書式変換用
 * @author LIANGZHI ZHU
 * @since   JDK1.4
 * $Date: 2005-09-14 13:01:33 $
 */
public class SQLStringFormatter 
{

    private static Class clazz = SQLStringFormatter.class;

    public static String formatSQLString(String sqlString)
    {
        String strSrc = toUpperCaseExceptConst(sqlString);

        strSrc = strSrc.replaceAll("\t"         , "    ");
        strSrc = strSrc.replaceAll("SELECT "    , "SELECT \n");
        strSrc = strSrc.replaceAll(" \\("       , " \n\\(\n ");
        strSrc = strSrc.replaceAll(" \\) "      , " \n\\) \n");
        strSrc = strSrc.replaceAll(","          , ", \n");
        strSrc = strSrc.replaceAll(" VALUES "   , "\nVALUES \n");
        strSrc = strSrc.replaceAll(" FROM "     , "\nFROM \n");
        strSrc = strSrc.replaceAll(" SET "      , "\nSET \n");
        strSrc = strSrc.replaceAll(" LEFT "     , "\nLEFT ");
        strSrc = strSrc.replaceAll(" INNER "    , "\nINNER ");
        strSrc = strSrc.replaceAll(" ON "       , " ON\n");
        strSrc = strSrc.replaceAll(" WHERE "    , "\nWHERE ");
        strSrc = strSrc.replaceAll(" AND "      , "\nAND ");
        strSrc = strSrc.replaceAll(" OR "       , "\nOR ");
        strSrc = strSrc.replaceAll(" UNION "    , "\nUNION\n");
        strSrc = strSrc.replaceAll(" ORDER "    , "\nORDER ");
        strSrc = strSrc.replaceAll(" GROUP "    , "\nGROUP ");
        //strSrc = strSrc.replaceAll(" BY "     , " BY \n");
        strSrc = strSrc.replaceAll("\n\n"       , "\n");

//StepTimeStampUtil.logTimeStamp(clazz);

        Stack stack = new Stack();

        boolean additionalIndentFlg = false;
        StringBuffer sb = new StringBuffer(strSrc.length());
        String[] lineArray = strSrc.split("\n");
        for (int i = 0 ; i < lineArray.length ; i++)
        {
            String line = lineArray[i];
            if (line == null
            ){
                continue;
            }
            line = line.trim();
            if (line.length() == 0
            ){
                continue;
            }

            int level = stack.size();

            if (line.indexOf("(") != -1
            ){
                stack.push("(");
            }

            int indexRight = line.indexOf(")");
            if (indexRight != -1
            ){
                stack.pop();
                if (indexRight == 0
                ){
                    level--;
                }
            }

            if (containsKeyword(line, "FROM")
            ){
                additionalIndentFlg = false;
            }

            if (additionalIndentFlg
            ){
                level++;
            }

            sb.append(createIndentByLevel(level));
            sb.append(line).append("\n");

            if (containsKeyword(line, "SELECT")
            ){
                additionalIndentFlg = true;
            }

        }

//StepTimeStampUtil.logTimeStamp(clazz);

        return sb.toString();
    }


    //Set＜String 分割マーク＞
    private static Set DIVISION_MARK_SET;

    static 
    {
        DIVISION_MARK_SET = new HashSet();
        DIVISION_MARK_SET.add(" ");
        DIVISION_MARK_SET.add("(");
        DIVISION_MARK_SET.add(")");
        DIVISION_MARK_SET.add("\t");
        DIVISION_MARK_SET.add("\r");
        DIVISION_MARK_SET.add("\n");
    }

    private static boolean containsKeyword(String str, String keyword)
    {
        if (StringCheckUtil.isEmpty(keyword)
            || StringCheckUtil.isEmpty(str)
        ){
            return false;
        }

        int index = str.indexOf(keyword);
        if (index == -1
        ){
            return false;
        }

        if (index > 0
            && !DIVISION_MARK_SET.contains(String.valueOf(str.charAt(index - 1)))
        ){
            return false;
        }

        int keywordLength = keyword.length();

        if (index < str.length() - 1 - keywordLength
            && !DIVISION_MARK_SET.contains(String.valueOf(str.charAt(index + keywordLength + 1)))
        ){
            return false;
        }

        return true;
    }


    //Map＜Integer level, String indentStr＞
    private static Map INDENT_MAP = new HashMap();
    private static String createIndentByLevel(int levelValue)
    {
        Integer level = Integer.valueOf(String.valueOf(levelValue));
        if (INDENT_MAP.containsKey(level)
        ){
            return (String)INDENT_MAP.get(level);
        }

        //String indentStr = StringUtils.leftPad("", level.intValue() * 4, ConstantMark.CHAR_SPACE);
        int len = level.intValue() * 4;
        StringBuffer sb = new StringBuffer(len);
        for (int i = 0 ; i < len ; i++)
        {
            sb.append(ConstantMark.SPACE_CHAR);
        }
        String indentStr = sb.toString();

        synchronized(clazz)
        {
            INDENT_MAP.put(level, indentStr);
        }

        return indentStr;
    }

    private static String toUpperCaseExceptConst(String str)
    {
        if (StringCheckUtil.isTrimedEmpty(str)
        ){
            return str;
        }

        StringBuffer sb = new StringBuffer();
        String[] strArray = str.split(ConstantMark.SINGLE_QUOTAION);
        for (int i = 0 ; i < strArray.length ; i++)
        {
            sb.append((i > 0) ? ConstantMark.SINGLE_QUOTAION : ConstantMark.BLANK);
            String line = StringCheckUtil.nvl(strArray[i]);
            if ((i % 2 == 0)
            ){
                line = trimSpace2One(line.toUpperCase());
            }
            sb.append(line);
        }
        return sb.toString();
    }

    private static String trimSpace2One(String str)
    {
        if (StringCheckUtil.isEmpty(str)
        ){
            return str;
        }

        StringBuffer sb = new StringBuffer();
        char[] charArray = str.toCharArray();
        boolean spaceStartFlg = false;
        for (int i = 0 ; i < charArray.length ; i++)
        {
            if (charArray[i] == ConstantMark.SPACE_CHAR
                || charArray[i] == ConstantMark.FULL_SPACE_CHAR
                || charArray[i] == ConstantMark.TAB_CHAR
            ){
                spaceStartFlg = true;
                continue;
            }
            if (spaceStartFlg
            ){
                sb.append(ConstantMark.SPACE_CHAR);
                spaceStartFlg = false;
            }
            sb.append(charArray[i]);
        }
        if (spaceStartFlg
        ){
            sb.append(ConstantMark.SPACE_CHAR);
        }
        return sb.toString();
    }

    public static void main(String[] args) 
    {
        String sqlString = "select distinct pbp.package_name,pbp.package_order,pbp.child_order      from be_t_repeal         br,           be_t_repeal_package brp,           be_t_project_detail bpd,           pa_t_bid_package    pbp,           pa_t_bid_object     pbo,           be_t_providers      bp     where br.repeal_id = brp.repeal_id       and brp.project_device_id = bpd.project_device_id       and bpd.bid_package_id = pbp.bid_package_id       and pbp.bid_object_id = pbo.bid_object_id       and brp.provider_id = bp.provider_id       and br.repeal_id = p_repeal_id     order by pbp.package_order||pbp.child_order,pbp.child_order,pbp.package_name";
        System.out.println(formatSQLString(sqlString));
    }
}

package org.ibatis.helper.common.util;

/**
 * @author zhu liangzhi
 * @date   2007/03/07
 *
 * Mark Contants
 */
public class ConstantMark 
{
    private ConstantMark(){} //private contructor

    public static final String BLANK                = "";

    public static final String PERIOD               = ".";

    public static final String COLON                = ":";

    public static final String SEMICOLON            = ";";

    public static final String SHARP                = "#";

    public static final String DOLLAR               = "$";

    public static final String UNDER_BAR            = "_";

    public static final String TAB                  = "\t";

    public static final String LINE_FEED            = "\n";

    public static final String SLASH                = "/";

    public static final String SPACE                = " ";

    public static final String SINGLE_QUOTAION      = "'";


    //丸括弧
    public static final String ROUND_BRACKET_LEFT   = "(";
    public static final String ROUND_BRACKET_RIGHT  = ")";

    //山形括弧
    public static final String ANGLE_BRACKET_LEFT   = "<";
    public static final String ANGLE_BRACKET_RIGHT  = ">";

    //中括弧
    public static final String BRACE_LEFT           = "{";
    public static final String BRACE_RIGHT          = "}";


    public static final char SLASH_CHAR             = '/';

    public static final char SPACE_CHAR             = ' ';

    public static final char FULL_SPACE_CHAR        = '　';

    public static final char TAB_CHAR               = '\t';

    //シングルクォーテーション
    public static final char SINGLE_QUOTAION_CHAR   = '\'';

}

/*
 * 作成日: 2007/03/23
 */
package org.ibatis.helper.common.util;

import java.util.HashSet;
import java.util.Iterator;
import java.util.Set;

/**
 * @author zhu liangzhi
 *
 * Stringに関するチェック処理用
 */
public class StringCheckUtil 
{
    //////////////////////////////////////////////////////////
    //Properties

    private static Set BOOLEAN_VALUE_SET;


    //////////////////////////////////////////////////////////
    //static

    static 
    {
        BOOLEAN_VALUE_SET = new HashSet();
        BOOLEAN_VALUE_SET.add("true");
        BOOLEAN_VALUE_SET.add("false");
    }


    //////////////////////////////////////////////////////////
    //public static method

    public static boolean isNull(String str)
    {
        return str == null;
    }

    public static boolean isEmpty(String str)
    {
        return isNull(str) || str.length() == 0;
    }

    public static boolean isTrimedEmpty(String str)
    {
        return isNull(str) || str.trim().length() == 0;
    }

    public static String nvl(String str)
    {
        return isNull(str) ? "" : str;
    }

    public static boolean isBooleanType(String str)
    {
        return !isEmpty(str) 
            && BOOLEAN_VALUE_SET.contains(str.toLowerCase())
        ;
    }

    public static boolean isPermitted(Set permittedValueSet, String value)
    {
        if (value == null
            || value.length() == 0
            || permittedValueSet == null
            || permittedValueSet.size() == 0
        ){
            return true;
        }

        int limit = 0;
        for (Iterator i = permittedValueSet.iterator() ; i.hasNext() ; )
        {
            String permittedValue = (String)i.next();
            int permittedValueLength = (permittedValue == null) 
                ? 0 : permittedValue.length();
            limit = (permittedValueLength > limit) 
                ? permittedValueLength : limit;
        }

        if (limit == 0
        ){
            return true;
        }

        value = value.toUpperCase();
        int valueLength = value.length();

        StringBuffer sb = new StringBuffer();
        for (int i = 0 ; i < valueLength ; i++)
        {
            sb.append(value.charAt(i));
            if (permittedValueSet.contains(sb.toString())
            ){
                sb = new StringBuffer();
                continue;
            }
            if (sb.length() >= limit
                || i == valueLength - 1
            ){
                return false;
            }
        }
        return true;

    }
}

select distinct br.repeal_id, 
           ConnStr(pbp.package_name) 
          from be_t_repeal         br,
               be_t_repeal_package brp,
               be_t_project_detail bpd,
               pa_t_bid_package    pbp,
               pa_t_bid_object     pbo,
               be_t_providers      bp,
               be_t_repeal_package btr,
               BE_T_FLOW_LOG       BTFL
         where br.repeal_id = brp.repeal_id
           and brp.project_device_id = bpd.project_device_id
           and bpd.bid_package_id = pbp.bid_package_id
           and pbp.bid_object_id = pbo.bid_object_id
           and brp.provider_id = bp.provider_id
           and br.repeal_id = btr.repeal_id
           and br.repeal_id = btfl.repeal_id
           and btfl.flow_type = 1
           and br.project_id = 990000000000003243           
       group by br.repeal_id  

      tmp_vStr := tmp_vStr || '、' || rec_Value.column_value;

      tmp_vStr := tmp_vStr || '、' || rec_Value.column_value;

      tmp_vStr := tmp_vStr || '、' || rec_Value.column_value;

--自定義集約関数→SORTED
DROP FUNCTION ConnStr
/
DROP TYPE BODY STR_CONN_TYPE
/
DROP TYPE STR_CONN_TYPE
/
DROP TYPE VcArrayType
/

CREATE OR REPLACE TYPE VcArrayType IS TABLE OF VARCHAR2(32767);
/

CREATE OR REPLACE TYPE STR_CONN_TYPE AS OBJECT
(
    --vStr VARCHAR2(4000),
    vStr VcArrayType,

    STATIC FUNCTION
    ODCIAggregateInitialize(sctx IN OUT STR_CONN_TYPE )
    RETURN NUMBER,

    MEMBER FUNCTION
    ODCIAggregateIterate(SELF IN OUT STR_CONN_TYPE ,
        VALUE IN VARCHAR2 )
    RETURN NUMBER,

    MEMBER FUNCTION
    ODCIAggregateMerge(SELF IN OUT STR_CONN_TYPE,
        ctx2 IN STR_CONN_TYPE)
    RETURN NUMBER,

    MEMBER FUNCTION
    ODCIAggregateTerminate(SELF IN STR_CONN_TYPE,
        returnValue OUT VARCHAR2,
        flags IN NUMBER)
    RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY STR_CONN_TYPE
IS
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT STR_CONN_TYPE)
    RETURN NUMBER
    IS
    BEGIN
        --sctx := STR_CONN_TYPE(NULL);
        sctx := STR_CONN_TYPE(VcArrayType());
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateIterate(SELF IN OUT STR_CONN_TYPE,
    VALUE IN VARCHAR2 )
    RETURN NUMBER
    IS
    BEGIN
        --SELF.vStr := SELF.vStr || ';' || VALUE;
        vStr.EXTEND;
        vStr(vStr.COUNT) := VALUE;
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT STR_CONN_TYPE,
        ctx2 IN STR_CONN_TYPE)
    RETURN NUMBER
    IS
    BEGIN
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateTerminate(SELF IN STR_CONN_TYPE,
        returnValue OUT VARCHAR2,
        flags IN NUMBER)
    RETURN NUMBER
    IS
        tmp_vStr VARCHAR2(32767);
    BEGIN
        --returnValue := LTRIM(SELF.vStr,';');
        FOR rec_Value IN (
            SELECT column_value 
            FROM TABLE(vStr) 
            --ORDER BY to_number(column_value)
            ORDER BY column_value
        ) 
        LOOP
            --tmp_vStr := tmp_vStr || '_' || rec_Value.column_value;
            tmp_vStr := tmp_vStr || '、' || rec_Value.column_value;
        END LOOP;
        --returnValue := LTRIM(tmp_vStr,'_');
        --returnValue := LTRIM(tmp_vStr,'、');
        returnValue := SUBSTRB(LTRIM(tmp_vStr, '、'), 1, 4000);
        RETURN ODCIConst.Success;
    END;
END;
/

CREATE OR REPLACE FUNCTION ConnStr(input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING STR_CONN_TYPE;
/

--自定義集約関数→SORTED
DROP FUNCTION CONNSTR_TO_VARCHAR
/
DROP TYPE BODY VARCHAR_STR_CONN_TYPE
/
DROP TYPE VARCHAR_STR_CONN_TYPE
/
DROP FUNCTION CONNSTR_TO_CLOB
/
DROP TYPE BODY CLOB_STR_CONN_TYPE
/
DROP TYPE CLOB_STR_CONN_TYPE
/
DROP TYPE VcArrayType
/

CREATE OR REPLACE TYPE VcArrayType IS TABLE OF VARCHAR2(4000);
/

-------------------------------------------
--1.CONNSTR_TO_VARCHAR

CREATE OR REPLACE TYPE VARCHAR_STR_CONN_TYPE AS OBJECT
(
    --vStr VARCHAR2(4000),
    vStr VcArrayType,

    STATIC FUNCTION
    ODCIAggregateInitialize(sctx IN OUT VARCHAR_STR_CONN_TYPE )
    RETURN NUMBER,

    MEMBER FUNCTION
    ODCIAggregateIterate(SELF IN OUT VARCHAR_STR_CONN_TYPE ,
        VALUE IN VARCHAR2 )
    RETURN NUMBER,

    MEMBER FUNCTION
    ODCIAggregateMerge(SELF IN OUT VARCHAR_STR_CONN_TYPE,
        ctx2 IN VARCHAR_STR_CONN_TYPE)
    RETURN NUMBER,

    MEMBER FUNCTION
    ODCIAggregateTerminate(SELF IN VARCHAR_STR_CONN_TYPE,
        returnValue OUT VARCHAR2,
        flags IN NUMBER)
    RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY VARCHAR_STR_CONN_TYPE
IS
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT VARCHAR_STR_CONN_TYPE)
    RETURN NUMBER
    IS
    BEGIN
        sctx := VARCHAR_STR_CONN_TYPE(VcArrayType());
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateIterate(SELF IN OUT VARCHAR_STR_CONN_TYPE,
    VALUE IN VARCHAR2 )
    RETURN NUMBER
    IS
    BEGIN
        vStr.EXTEND;
        vStr(vStr.COUNT) := VALUE;
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT VARCHAR_STR_CONN_TYPE,
        ctx2 IN VARCHAR_STR_CONN_TYPE)
    RETURN NUMBER
    IS
    BEGIN
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateTerminate(SELF IN VARCHAR_STR_CONN_TYPE,
        returnValue OUT VARCHAR2,
        flags IN NUMBER)
    RETURN NUMBER
    IS
        tmp_vStr VARCHAR2(4000);
        loop_vstr VARCHAR2(4000);
        sperator VARCHAR2(255);
        sperator_size NUMBER;
        current_size NUMBER;
    BEGIN
        sperator := '、';
        sperator_size := LENGTHB(sperator);
        current_size := 0;

        FOR rec_Value IN (
            SELECT column_value 
            FROM TABLE(vStr) 
            ORDER BY column_value
        ) 
        LOOP
            loop_vstr := rec_Value.column_value;
            current_size := current_size + sperator_size + LENGTHB(loop_vstr);
            IF (current_size <= 4000)
            THEN
                tmp_vStr := tmp_vStr || sperator || loop_vstr;
            END IF;
        END LOOP;

        returnValue := LTRIM(tmp_vStr, sperator);
        RETURN ODCIConst.Success;
    END;
END;
/

CREATE OR REPLACE FUNCTION CONNSTR_TO_VARCHAR(input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING VARCHAR_STR_CONN_TYPE;
/


-------------------------------------------
--2.CONNSTR_TO_CLOB

CREATE OR REPLACE TYPE CLOB_STR_CONN_TYPE AS OBJECT
(
    --vStr VARCHAR2(4000),
    vStr VcArrayType,

    STATIC FUNCTION
    ODCIAggregateInitialize(sctx IN OUT CLOB_STR_CONN_TYPE )
    RETURN NUMBER,

    MEMBER FUNCTION
    ODCIAggregateIterate(SELF IN OUT CLOB_STR_CONN_TYPE ,
        VALUE IN VARCHAR2 )
    RETURN NUMBER,

    MEMBER FUNCTION
    ODCIAggregateMerge(SELF IN OUT CLOB_STR_CONN_TYPE,
        ctx2 IN CLOB_STR_CONN_TYPE)
    RETURN NUMBER,

    MEMBER FUNCTION
    ODCIAggregateTerminate(SELF IN CLOB_STR_CONN_TYPE,
        returnValue OUT CLOB,
        flags IN NUMBER)
    RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY CLOB_STR_CONN_TYPE
IS
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT CLOB_STR_CONN_TYPE)
    RETURN NUMBER
    IS
    BEGIN
        sctx := CLOB_STR_CONN_TYPE(VcArrayType());
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateIterate(SELF IN OUT CLOB_STR_CONN_TYPE,
    VALUE IN VARCHAR2 )
    RETURN NUMBER
    IS
    BEGIN
        vStr.EXTEND;
        vStr(vStr.COUNT) := VALUE;
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT CLOB_STR_CONN_TYPE,
        ctx2 IN CLOB_STR_CONN_TYPE)
    RETURN NUMBER
    IS
    BEGIN
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateTerminate(SELF IN CLOB_STR_CONN_TYPE,
        returnValue OUT CLOB,
        flags IN NUMBER)
    RETURN NUMBER
    IS
        tmp_vStr VARCHAR2(32767);
        loop_vstr VARCHAR2(4000);
        sperator VARCHAR2(255);
        sperator_size NUMBER;
        is_null_flg NUMBER;
        current_size NUMBER;
    BEGIN
        sperator := '、';
        sperator_size := LENGTHB(sperator);
        is_null_flg := 0;
        current_size := 0;

        FOR rec_Value IN (
            SELECT column_value 
            FROM TABLE(vStr) 
            ORDER BY column_value
        ) 
        LOOP
            loop_vstr := rec_Value.column_value;
            current_size := current_size + sperator_size + LENGTHB(loop_vstr);
            IF (current_size > 32767)
            THEN
                IF (is_null_flg = 1)
                THEN 
                    DBMS_LOB.APPEND(returnValue, TO_CLOB(tmp_vStr));
                END IF;

                IF (is_null_flg = 0)
                THEN 
                    tmp_vStr := LTRIM(tmp_vStr, sperator);
                    returnValue := TO_CLOB(tmp_vStr);
                    is_null_flg := 1;
                END IF;

                tmp_vStr := '';
                current_size := 0;
            END IF;
            tmp_vStr := tmp_vStr || sperator || loop_vstr;
        END LOOP;

        IF (is_null_flg = 1)
        THEN 
            DBMS_LOB.APPEND(returnValue, TO_CLOB(tmp_vStr));
        END IF;

        IF (is_null_flg = 0)
        THEN 
            tmp_vStr := LTRIM(tmp_vStr, sperator);
            returnValue := TO_CLOB(tmp_vStr);
        END IF;

        RETURN ODCIConst.Success;
    END;
END;
/

CREATE OR REPLACE FUNCTION CONNSTR_TO_CLOB(input VARCHAR2)
RETURN CLOB
PARALLEL_ENABLE AGGREGATE USING CLOB_STR_CONN_TYPE;
/