oracle pl/sql 结果成一个字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10954511/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
oracle pl/sql results into one string
提问by sayhaha
I'm trying to create a simple stored procedure that stores queried result into one string.
我正在尝试创建一个简单的存储过程，将查询结果存储到一个字符串中。
v_string1 varchar2(100);
Select column1
From dual;
Will return
将返回
column 1
--------
aaaa
bbbb
cccc
I want to store "aaaa, bbbb, cccc' into v_string1. And all I can think of is a Cursor... Is there a better way to handle this?
我想将“aaaa，bbbb，cccc”存储到 v_string1 中。我能想到的只是一个 Cursor ......有没有更好的方法来处理这个问题？
回答by Kyra
Using SQL Fiddle:
使用SQL 小提琴：
select LISTAGG(name, ',') WITHIN GROUP (ORDER BY 1) AS names
from temp_table
回答by Mike Meyers
Another option using pure SQL that will work before Oracle 11G, although is still limited to 4000 characters for the string.
另一个使用纯 SQL 的选项可以在 Oracle 11G 之前使用，但字符串仍然限制为 4000 个字符。
Select ltrim(max(names), ', ') as names
From (
Select sys_connect_by_path(name, ' ,') as names
From (
Select name, row_number() over (order by name) as rown
From temp_table
)
Start with rown = 1
Connect by rown = prior rown + 1
)
