Oracle LISTAGG() 查询使用

2021-12-30 00:00:00 oracle11g oracle plsql

所以我试图利用 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斯科舍省','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.

这样的东西会起作用；

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.

这是一个工作示例

相关文章