将查询结果连接成列表的几个方法

　　我们在实际开发过程中, 经常需要将一个查询的结果连接成一个单行的字符串列表, 比如说将一个部门及其所有员工列在一行显示.
　　
　　本文介绍了从最简单到最通用的几种方法来达到这一目的.
　　
　　1.　　已知列的取值范围
　　
　　比如说, 有以下产品表, 每种产品只有3种颜色(R-RED, B-BLACK, W-WHITE):
　　
　　scott@ORCL> DESC PROD
　　
　　名称　　　　　　　　　　　　　　　　　　　空?　　　类型
　　
　　----------------------------------------- -------- --------------
　　
　　PRODNAME　　　　　　　　　　　　　　　　　　　　　 VARCHAR2(20)
　　
　　COLOR　　　　　　　　　　　　　　　　　　　　　　　VARCHAR2(1)
　　
　　scott@ORCL> SELECT * FROM PROD;
　　
　　PRODNAME　　　　　　 C
　　
　　-------------------- -
　　
　　CAR　　　　　　　　　R
　　
　　CAR　　　　　　　　　B
　　
　　CAR　　　　　　　　　W
　　
　　MOTOR　　　　　　　　W
　　
　　MOTOR　　　　　　　　B
　　
　　我们希望列出每种产品都有哪些颜色.
　　
　　这种情况下, 只要简单的使用decode()函数即可.
　　
　　下面是script代码及相应的运行结果.
　　scott@ORCL> COLUMN COLORS FORMAT A10
　　
　　scott@ORCL >select PRODNAME,
　　
　　2　　　　 max(decode( COLOR, 'B', COLOR, null ))　
　　
　　3　　　　 max(decode( COLOR, 'W', COLOR, null ))　
　　
　　4　　　　 max(decode( COLOR, 'R', COLOR, null )) COLORS
　　
　　5　from PROD
　　
　　6　group by PRODNAME
　　
　　7　/
　　
　　PRODNAME　　　　　　 COL
　　
　　-------------------- ---
　　
　　CAR　　　　　　　　　BWR
　　
　　MOTOR　　　　　　　　BW
　　
　　2. 静态SQL
　　
　　这种方法适合于列的取值不能预知, 由于使用静态sql, 相对方法3来说比较简单. 不过该函数只能用于特定的表.
　　
　　示例所用的表及数据使用Oracle随机demo表emp.
　　
　　注重: 受varchar2的长度限制, 列的取值的总长度是有限制的, 在方法3中也有同样的问题.
　　
　　下面是函数及其运行结果
　　
　　create or replace
　　
　　function list_func1( p_dept in varchar2 )
　　
　　return varchar2
　　
　　is
　　
　　l_str　varchar2(4000) default null;
　　
　　l_sep　varchar2(2) default null;
　　
　　begin
　　
　　for x in ( select empno from emp where deptno = p_dept ) loop
　　
　　l_str := l_str l_sep to_char(x.empno);
　　
　　l_sep := ', ';
　　
　　end loop;
　　
　　return l_str;
　　
　　end;
　　
　　/
　　
　　scott@ORCL> column emplist format a60
　　
　　scott@ORCL> select deptno, emplist1(deptno) emplist
　　
　　2　from emp
　　
　　3　group by deptno
　　
　　4　/
　　
　　DEPTNO EMPLIST
　　
　　---------- ------------------------------------------------------------
　　
　　10 7782, 7839, 7934
　　
　　20 7369, 7566, 7788, 7876, 7902
　　
　　30 7499, 7521, 7654, 7698, 7844, 7900
　　
　　scott@ORCL>
　　
　　3.　　　动态SQL
　　
　　这种方法由于使用动态sql, 相对来说比较复杂, 但与此同时, 它比其方法2来说功能大大增强: 它可以用于任意表, 并可以连接表中的任意列.
　　
　　函数代码:
　　
　　create or replace
　　
　　function list_func2( p_key_name in varchar2,
　　
　　p_key_val　in varchar2,
　　
　　p_other_col_name in varchar2,
　　
　　p_tname　　 in varchar2 )
　　
　　return varchar2
　　
　　as
　　
　　type rc is ref cursor;

　　
　　l_str　　varchar2(4000);
　　
　　l_sep　　varchar2(2);
　　
　　l_val　　varchar2(4000);
　　
　　l_cur　　rc;
　　
　　begin
　　
　　open l_cur for 'select 'p_other_col_name'
　　
　　from ' p_tname '
　　
　　where ' p_key_name ' = :x '
　　
　　using p_key_val;
　　
　　loop
　　
　　fetch l_cur into l_val;
　　
　　exit when l_cur%notfound;
　　
　　l_str := l_str l_sep l_val;
　　
　　l_sep := ', ';
　　
　　end loop;
　　
　　close l_cur;
　　
　　return l_str;
　　
　　end;
　　
　　/
　　
　　参数说明:
　　
　　p_key_name -　　　　　　　查询依据的列名
　　
　　p_key_val -　　　　　　　查询依据的列名所对应的列值
　　
　　p_other_col_name -　　　要连接成列表的列名
　　
　　p_tname -　　　　　　　　表名
　　
　　下面是运行结果:
　　
　　scott@ORCL> column emplist format a60
　　
　　scott@ORCL> select DEPTNO,
　　
　　2　　 list_func2('DEPTNO', DEPTNO, 'EMPNO', 'EMP') emplist
　　
　　3　from emp
　　
　　4　group by deptno
　　
　　5　/
　　
　　DEPTNO EMPLIST
　　
　　---------- --------------------------------------------------
　　
　　10 7782, 7839, 7934
　　
　　20 7369, 7566, 7788, 7876, 7902
　　
　　30 7499, 7521, 7654, 7698, 7844, 7900