【IT168 服务器学院】我们在实际开发过程中, 经常需要将一个查询的结果连接成一个单行的字符串列表, 比如说将一个部门及其所有员工列在一行显示.
　　
　　本文介绍了从最简单到最通用的几种方法来达到这一目的.
　　
　　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