Oracle LISTAGG()供查询使用 [英] Oracle LISTAGG() for querying use
问题描述
因此,我试图利用 LISTAGG()函数简单地构建一个逗号分隔列表,以在基础查询中使用.列表生成工作正常,我只是出于调试目的应用了输出,在这里我可以看到列表:
So I'm trying to make use of the LISTAGG() function to simply build a comma delimited list to use within an underlying query. The list generation works fine and I just applied an output for debug purposes where I can see my list as it should be:
值: 'AB','AZ','BC','CA','CT','DC','FL','FO','GA','IL','KS','MA','MB ','ME','MN','MS','MT','NB','NC','NL','NOVA SCOTIA,'NS','NT','NU','NY','ON','ONTARIO','OR','PE','QC','QUEBEC','QUÉBEC','SASKATCHEWAN' ,'SK','TX','VT','WA','YT'
VALUES: 'AB','AZ','BC','CA','CT','DC','FL','FO','GA','IL','KS','MA','MB','ME','MN','MS','MT','NB','NC','NL','NOVA SCOTIA','NS','NT','NU','NY','ON','ONTARIO','OR','PE','QC','QUEBEC','QUÉBEC','SASKATCHEWAN','SK','TX','VT','WA','YT'
当我尝试将此列表变量传递给查询时,只是想看看是否会返回任何内容,什么也不会返回,但是如果我从上方(按原样)复制/通过了省/州列表,而不是使用"v_Province" "在我的where子句中,我得到了结果.我在做什么错了?
When I try to pass this list variable to my query however just to see if anything will come back, nothing comes back, but if I copy / past the provinces / states list from above (as is) instead of using "v_Province" in my where clause, I get a result back. What am I doing wrong?
DECLARE
v_PROVINCE varchar2(500);
v_results varchar2(1000);
BEGIn
dbms_output.enable(1000000);
Select '''' || LISTAGG(STATE, ''',''') WITHIN GROUP (ORDER BY STATE) || '''' PROV
INTO v_PROVINCE
from (Select distinct STATE from ADDRDATA where STATE IS NOT NULL);
DBMS_OUTPUT.PUT_LINE('VALUES: ' || v_PROVINCE);
Select CITY
INTO v_results
from VWPERSONPRIMARYADDRESS
where state in (v_Province)
AND ROWNUM <= 1;
DBMS_OUTPUT.PUT_LINE(v_results);
END;
/
推荐答案
首先,如果可能的话,在单个语句中执行所有操作几乎总是效率更高.
Firstly, it is almost always more efficient to do everything in a single statement if at all possible.
您的第二个查询不起作用,因为您要将所有内容都返回到单个字符串中.这不是IN语句所要求的以逗号分隔的列表.
不是.
Your second query doesn't work as you are returning everything into a single string. This is not a comma delimited list as required by an IN statement.
有一个小技巧可以解决这个问题.假设您在两个SELECT语句之间使用字符串作为对象,则可以使用 regexp_substr()
将您的字符串转换为可用的字符串.
There is a little trick to get round this though. Assuming you are using the string for something between the two SELECT statements you can play around with regexp_substr()
to turn your string into something usable.
类似的事情会起作用;
Something like this would work;
select city
from vwpersonprimaryaddress
where state in (
select regexp_substr(v_province,'[^'',]+', 1, level)
from dual
connect by regexp_substr(v_province, '[^'',]+', 1, level) is not null
)
变量v_province
必须更改为用双引号引起,例如'''AB'',''AZ'',''BC'''
,这样才能起作用.
The variable v_province
would have to be changed to be quoted twice, for instance '''AB'',''AZ'',''BC'''
in order for this to work.
这是一个工作示例
这篇关于Oracle LISTAGG()供查询使用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!