子选择的并集结果 [英] Union results from subselect

问题描述

我有一个具有以下结构的SQL语句:

I have an SQL statement with following structure:

f(string parameter)=select * from table(parameter) ...
=> results in some table with rows.

f(字符串参数)用作更复杂的sql子查询的快捷方式.

f(string parameter) is used as shortcut for more complex sql subquery.

table parameters:

|---params----|
   param1
   param2
   param3
   ....

如何将f()与表parameters组合在一起，其中将为该表中的每个参数调用f().我的问题涉及SQL语句本身.结果，我期望

how to combine f() with the table parameters where f() will be called for each param in this table. My question refers to SQL statement itself. As result I expect

f(param1)
  union all
f(param2)
  union all
f(param3)
...

如果有人对f()中的内容感兴趣，请参阅我之前的问题答案 https://stackoverflow.com/a/27599637/2023524 .

If someone is interested what is staying in f() refer to my previous question answer https://stackoverflow.com/a/27599637/2023524.

推荐答案

您可以将f定义为如下函数

You can define f as a function like the below

create or replace function f(param IN VARCHAR2) return varchar2 is
  Result varchar2(32767);
begin
  with names as(
    SELECT REGEXP_SUBSTR(regexp_replace(replace(param,
                                                chr(13) || chr(10),
                                                ','),
                                        '[[:space:]]*',
                                        ''),
                         '[^=]+',
                         1,
                         level) as name
      FROM DUAL
    CONNECT BY LEVEL <= (SELECT REGEXP_COUNT(param, '=') FROM DUAL)

     )

      select LISTAGG(nvl(regexp_substr(name, '[^,]+', 1, 2),
                         regexp_substr(name, '[^,]+', 1)),
                     ',') WITHIN
       GROUP(
       ORDER BY name)
        INTO Result
        from names;

  return(Result);
end f;

然后您可以像下面那样调用参数

Then you can call your parameters like the below

with parameter(param) as (
    select 'aa = black' ||chr(13)||chr(10)||'bb = change'||chr(13)||chr(10)||'mmmm=no'  
    from dual union  all
   select 'aa = black' ||chr(13)||chr(10)||'bb = change'||chr(13)||chr(10)||'kkk=no' from dual
    )
    select distinct regexp_substr(f(param),'[^,]+',1,level) from parameter
    connect by level <=regexp_count(f(param),',')+1;

更新1:-仅供参考，您可以像下面这样在匿名块中调用函数

Update1:-Just for your information You can call a function within an Anonymous Block like the below

DECLARE
  function f(param IN VARCHAR2) return varchar2 is
    Result varchar2(32767);
  begin
    with names as(
      SELECT REGEXP_SUBSTR(regexp_replace(replace(param,
                                                  chr(13) || chr(10),
                                                  ','),
                                          '[[:space:]]*',
                                          ''),
                           '[^=]+',
                           1,
                           level) as name
        FROM DUAL
      CONNECT BY LEVEL <= (SELECT REGEXP_COUNT(param, '=') FROM DUAL)

       )

        select LISTAGG(nvl(regexp_substr(name, '[^,]+', 1, 2),
                           regexp_substr(name, '[^,]+', 1)),
                       ',') WITHIN
         GROUP(
         ORDER BY name)
          INTO Result
          from names;


    return(Result);
  end f;
begin
  for i in 1 .. (regexp_count(f('aa = black' || chr(13) || chr(10) ||
                                'bb = change' || chr(13) || chr(10) ||
                                'kkk=no'),
                              ',') + 1) loop
    dbms_output.put_line(regexp_substr(f('aa = black' || chr(13) ||
                                         chr(10) || 'bb = change' ||
                                         chr(13) || chr(10) || 'kkk=no'),
                                       '[^,]+',
                                       1,
                                       i));
  end loop;
end;

这篇关于子选择的并集结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！