问题描述
我有一个具有以下结构的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 answerhttps://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;
这篇关于子选择的并集结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!