不使用动态解析包处理动态拼接的sql

create or replace procedure p_get_trans_group_type ( I_CALL_ID   IN NUMBER,
    I_PARAM1  IN  T_BACKGROUND_CALL.PARA_VALUE_1%TYPE,  --ORGAN ID
    I_PARAM2  IN  T_BACKGROUND_CALL.PARA_VALUE_1%TYPE,  --BANK CODE
    I_PARAM3  IN  T_BACKGROUND_CALL.PARA_VALUE_1%TYPE,  --POLICY TYPE
    I_PARAM4  IN  T_BACKGROUND_CALL.PARA_VALUE_1%TYPE,  --ACCO TYPE
    I_PARAM5  IN  T_BACKGROUND_CALL.PARA_VALUE_1%TYPE,  --COLLECT PAY 0:收费 1:付费
    I_PARAM6  IN  T_BACKGROUND_CALL.PARA_VALUE_1%TYPE  --OPERATOR ID
    )IS
    M_PARAM1    T_BACKGROUND_CALL.PARA_VALUE_1%TYPE;  --ORGAN ID
    M_PARAM2    T_BACKGROUND_CALL.PARA_VALUE_1%TYPE;  --BANK CODE
    M_PARAM3    T_BACKGROUND_CALL.PARA_VALUE_1%TYPE;  --POLICY TYPE
    M_PARAM4    T_BACKGROUND_CALL.PARA_VALUE_1%TYPE;  --ACCO TYPE
    M_PARAM5    T_BACKGROUND_CALL.PARA_VALUE_1%TYPE;  --COLLECT PAY 0:收费 1:付费
    M_PARAM6    T_BACKGROUND_CALL.PARA_VALUE_1%TYPE;  --OPERATOR ID
    --M_PARAM7    T_BACKGROUND_CALL.PARA_VALUE_1%TYPE;  --OPERATOR ID
   -- M_PARAM8    T_BACKGROUND_CALL.PARA_VALUE_1%TYPE;  --OPERATOR ID
   -- M_SEND_ID   NUMBER;
    M_ORGAN_TYPE     NUMBER:=0;
    M_BANK_TYPE      NUMBER:=0;
    M_NEST_TYPE      NUMBER:=0;
    V_SQL            VARCHAR2(300);
    type            cur_type   is ref cursor; --定义cusor类型
    cur_fee          cur_type;  --具体游标变量定义
   
BEGIN 
   
    SELECT BC.PARA_VALUE_1, BC.PARA_VALUE_2, BC.PARA_VALUE_3,
           BC.PARA_VALUE_4, BC.PARA_VALUE_5, BC.PARA_VALUE_6--,BC.PARA_VALUE_7,BC.PARA_VALUE_8
      INTO M_PARAM1, M_PARAM2, M_PARAM3, M_PARAM4, M_PARAM5, M_PARAM6--,M_PARAM7,M_PARAM8
      FROM T_BACKGROUND_CALL BC
     WHERE BC.CALL_ID = I_CALL_ID;
     SELECT i_PARAM1, i_PARAM2, i_PARAM3, i_PARAM4, i_PARAM5, i_PARAM6--,M_PARAM7,M_PARAM8
     INTO M_PARAM1, M_PARAM2, M_PARAM3, M_PARAM4, M_PARAM5, M_PARAM6
     FROM dual WHERE (i_call_id IS NULL OR I_CALL_ID <1 );
    --判断是否为机构组
    select count(distinct(a.group_num)) into m_organ_type
      from t_trans_fmt_group a,t_trans_fmt_organ_bank b
      where a.organ_id=b.organ_id
      and a.group_num=b.group_num
      and a.group_type=1
      and b.bank_code=M_PARAM2
      and a.organ_id=M_PARAM1
      and (a.collect_pay=DECODE(M_PARAM5,0,1,1,2,9) or a.collect_pay=9)
      and (a.account_type=DECODE(M_PARAM4,7,7,8,8,9) or a.account_type=9)
      and a.IS_VALID='Y';
    --判断是否为银行组
    select count(distinct(a.group_num)) into m_bank_type
      from t_trans_fmt_group a,t_trans_fmt_organ_bank b
      where a.bank_code=b.bank_code
      and a.group_num=b.group_num
      and a.bank_code=M_PARAM2
      and b.organ_id=M_PARAM1
      and (a.collect_pay=DECODE(M_PARAM5,0,1,1,2,9) or a.collect_pay=9)
      and (a.account_type=DECODE(M_PARAM4,7,7,8,8,9) or a.account_type=9)
      and a.group_type=2
      and a.IS_VALID='Y';
    --判断是否为嵌套组
    select count(distinct(a.group_num)) into m_nest_type
      from t_trans_fmt_group a,t_trans_fmt_organ_bank b
      where a.organ_id=b.organ_id
      and a.group_num=b.group_num
      and a.group_type=3
      and b.bank_code=M_PARAM2
      and a.organ_id=M_PARAM1
      and (a.collect_pay=DECODE(M_PARAM5,0,1,1,2,9) or a.collect_pay=9)
      and (a.account_type=DECODE(M_PARAM4,7,7,8,8,9) or a.account_type=9)
      and a.IS_VALID='Y';
      dbms_output.put_line('m_organ_type:'||m_organ_type||',m_bank_type:'||m_bank_type||',m_nest_type:'||m_nest_type);
     
      if m_organ_type>0 and m_bank_type=0 and m_nest_type=0 THEN     --机构组
         v_sql := 'select a.child_organ_id from t_trans_fmt_group a,t_trans_fmt_organ_bank b ';
         v_sql := v_sql || '       where a.organ_id=b.organ_id and a.group_num=b.group_num ';
         v_sql := v_sql || '        and b.bank_code='''||M_PARAM2||''' and a.organ_id='''||M_PARAM1 ||'''';
         v_sql := v_sql || '        and a.is_valid=''Y'' ';
      elsif m_organ_type=0 and m_bank_type>0 and m_nest_type=0 then   --银行组
         v_sql := '';
      elsif m_organ_type=0 and m_bank_type=0 and m_nest_type>0 then   --银行组
         v_sql := '';
      else
         v_sql := '';
      end if;
      OPEN cur_fee FOR  v_sql ;--打开cursor
      LOOP
      FETCH cur_fee INTO M_PARAM2;--取出一条数据
      EXIT WHEN cur_fee%NOTFOUND;
            dbms_output.put_line(M_PARAM2);--处理数据
      END LOOP;
      CLOSE cur_fee;

end