oracle数据集里某列存储了sql，如何通过select 直接查询数据集及数据集里sql执行后的值

Question

  select decode(t.AGENCY_CODE, null, '', t.AGENCY_CODE || '-' || f.name) ,
       decode(t.COLLECBANKGUID, null, '', d.code || '-' || d.name) ,
       b.WHERESQL 规则条件,
       regexp_count(b.WHERESQL, 'select'),
       instr(b.WHERESQL, 'select', 1, 1),
       instr(b.WHERESQL, 'select', 1, 2),
       substr(b.WHERESQL,
              instr(b.WHERESQL, 'select', 1, 1),
              (instr(b.WHERESQL, ')', instr(b.WHERESQL, 'select', 1, 1)) -
              instr(b.WHERESQL, 'select', 1, 1))),
       substr(b.WHERESQL,
              instr(b.WHERESQL, 'select', 1, 2),
              (instr(b.WHERESQL, ')', instr(b.WHERESQL, 'select', 1, 2)) -
              instr(b.WHERESQL, 'select', 1, 2)))
  from fasp_t_pubbankaccount t
  left join BUSFW_T_RUACCOUNTLINK a
    on a.BANKACCOUNTGUID = t.GUID
  left join FASP_T_BUSRURULE b
    on b.GUID = a.RULEID
  left join V_ELE_VD00006 c
on c.guid = t.BANKGUID
  left join V_ELE_VD00006 d
    on d.guid = t.COLLECBANKGUID
  left join FASP_T_PUPS_CS_0050 e
    on e.GUID = t.BANKACCOUNTTYPE
  left join V_ELE_VD00010 f
    on f.guid = t.AGENCY_CODE  

这个语句里substr(b.WHERESQL,
              instr(b.WHERESQL, 'select', 1, 1),
              (instr(b.WHERESQL, ')', instr(b.WHERESQL, 'select', 1, 1)) -
              instr(b.WHERESQL, 'select', 1, 1)))  列显示出来是一个独立的 select 查询语句  

我想直接在上面的结果集里显示这个独立sql运行后的值

Answer 1

首先,你得确定这个你处理出来的sql是只查询一个1行中的1列,即1个值。
由于动态sql不能直接在sql中查询出结果,必须用execute immediate或者dbms_sql去执行它,因此,可以先建立一个这样的函数

create or replace function get_sql_value(i_sql varchar2) return varchar2 is
  out_value varchar2(4000);
begin
  execute immediate i_sql
    into out_value;
  return out_value;
exception
  when no_data_found then
    out_value := '';
    return out_value;
  when others then
    Raise_application_error(-20000, sqlerrm);
end;
/

接下来,你就可以用这个函数把你处理的这个sql字符串包起来,它就能在你查询的时候返回对应sql的结果了,
例:

select 1 a,2 b,3 c,get_sql_value('select 1 from dual') from dual;