如何从Oracle中的PL/SQL表类型返回CSV字符串 [英] How can I return a CSV string from PL/SQL table type in Oracle
问题描述
我已经定义了一个表类型PL/SQL变量,并在其中添加了一些数据.
I have defined a table type PL/SQL variable and added some data there.
create or replace type varTableType as table of varchar2(32767);
my_table varTableType := varTableType()
...
my_table := some_function();
现在,我有了这个具有数以千计的记录的my_table
表类型变量.
我只需要选择那些以特定字符结尾的记录(例如'a'),并以逗号分隔的字符串形式获取结果.
我认为COLLECT函数可以做到这一点,但我不完全了解.
我正在使用Oracle 10g.
Now I have this my_table
table type variable with several thousands of records.
I have to select only those records ending with specific character, say 'a' and get results in a comma separated string.
I think that COLLECT function could do this, but I do not understand exactly how.
I am using Oracle 10g.
推荐答案
不用担心问题-为什么要使用表类型而不是表(或临时表),您可以这样做:
Without getting into the question- why are you using a table type and not a table (or temporary table), you can do it like this:
declare
my_table varTableType;
i varchar2(32767);
begin
my_table := new
varTableType('bbbb', 'ccca', 'ddda', 'eee', 'fffa', 'gggg');
select trim(xmlagg(xmlelement(e, column_value || ','))
.extract('//text()'))
into i
from table(my_table)
where column_value like '%a';
dbms_output.put_line(i);
end;
还有更多方法可以合并行- WM_CONCAT (如果启用)或 LISTAGG (自11g R2起),但是
There are more ways to concat rows- WM_CONCAT (if enabled) or LISTAGG (since 11g R2) but the basic idea of
select column_value
from table(my_table)
where column_value like '%a';
住宿
还有另一种没有sql的方法:
There is another way without sql:
declare
my_table varTableType;
i varchar2(32767);
begin
my_table := new
varTableType('bbbb', 'ccca', 'ddda', 'eee', 'fffa', 'gggg');
FOR j IN my_table.first .. my_table.last LOOP
IF my_table(j) like '%a' THEN
i := i || my_table(j);
END IF;
END LOOP;
dbms_output.put_line(i);
end;
这篇关于如何从Oracle中的PL/SQL表类型返回CSV字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!