将表格的某一列转换成逗号分隔的字符串的另类方法

将表格的某一列转换成逗号分隔的字符串的另类方法

A：dbms_utility.table_to_comma的使用
t1V1wt]9x^En0一：首先建立测试表ITPUB个人空间"_([F^WK
create table table1ITPUB个人空间 ]$o wds3X
(ITPUB个人空间7lU ~eH0y,a
dz varchar2(100)ITPUB个人空间4q\ zm8AD%E,Z
)ITPUB个人空间8B&Sd]:Zm'b:Y
insert into table1 values('v');ITPUB个人空间'q m+j(vS4k'u
insert into table1 values('dywm');ITPUB个人空间]Z2iE(Zk
commit;

SQL> select * from table1;

DZITPUB个人空间!? |-Yv,u4J
--------------------------------------------------------------------------------
5br7Rq y
Z0v
]]&SPO0dywm

二：建立存储过程
,b1f%r'R'l0create or replace procedure test_column_row asITPUB个人空间-P7eb;p7AG'X5btM
qq1 dbms_utility.uncl_array;ITPUB个人空间4R2g6iX^
l_tablen number;
$e%SOU$g,EH0 l_tab varchar2(1000);
gp!H9@*BCC;N0begin
[ \!Q#Qc,^0 select dz bulk collect into qq1 from table1;
*t:\k wn
Bht&L3jyH0 dbms_utility.table_to_comma(qq1, l_tablen, l_tab);ITPUB个人空间4oE-};\2OL;K
dbms_output.put_line(l_tab);ITPUB个人空间'~/~&Q$r"O!{%Kx7z+n.W
end;

三：运行结果如下：ITPUB个人空间@7B}X$v4i7l{
SQL> set serveroutput onITPUB个人空间
Do N:T&k(gm.M+G!^z(Q
SQL> execute test_column_row;

v,dywm

PL/SQL procedure successfully completed

附：字符串<=>table相互转换的例子
5A-[9dj8~3J@Ux0create or replace procedure schema_table asITPUB个人空间bVx^#Uy+]
/*ITPUB个人空间v2Z
V,TM ]
把字符串变成tableITPUB个人空间&XwI-|+pZ$rJ
或者把table变成字符串ITPUB个人空间&[
L-H$d&Z
*/ITPUB个人空间6Z.]3A9uz
type vcArray is table of varchar2(4000);ITPUB个人空间u8xu2T/u
x;\
l_names vcArray := vcArray('sswe,oejic,wwer', 'a , b , c',ITPUB个人空间'X/[%x8o3z,v"~,]AU
'e123o,a688j9,d557i5', 'd334a,q225d,w67s7');
n[\M;?e*Li(G*yC0 l_tablen number;
4h
l.F{gf0 l_tab dbms_utility.uncl_array;ITPUB个人空间pFE(nQ8z
begin
1l%fN'?P0 for i in 1 .. l_names.count
@ f.`%H:]F$X0 loop
5zGbXs~"a0 dbms_output.put_line('i_names(' || i || ')=' || l_names(i));
u
\g4D8B0 begin
o)k$_0RGq+O0 dbms_utility.comma_to_table(l_names(i), l_tablen, l_tab);ITPUB个人空间k]+i/`*pTg
for j in 1 .. l_tablenITPUB个人空间#s&^4F+c6R2C
loop
GMX0CS3Ls@g+V0 dbms_output.put_line('l_tab(' || j || ')=' || l_tab(j));ITPUB个人空间
v
?m"pObb
end loop;
OV
R;W;R|.t$B0 l_names(i) := null;ITPUB个人空间idB:x4U3f#\&_
dbms_utility.table_to_comma(l_tab, l_tablen, l_names(i));
;`{'Z]:C/]@!F3Z0 dbms_output.put_line(l_names(i));
$Rn#{?9dn!b0 end;ITPUB个人空间
qOwWg'r
end loop;ITPUB个人空间 m
` z0uK!bP
end;