Oracle 函数：复制 wm_concat
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1967796/
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 Function: Replicate wm_concat
提问by contactmatt
I currently am working on a project within Crystal Reports that refuses to use the undocumented function WM_CONCAT, which is allowable within Oracle 10g. Here is the WM_CONCAT header information
我目前正在从事 Crystal Reports 中的一个项目，该项目拒绝使用 Oracle 10g 中允许的未记录函数 WM_CONCAT。这里是 WM_CONCAT 头信息
WM_CONCAT(p1 IN VARCHAR2) RETURN VARCHAR2
To use WM_CONCAT I pass it the following: WM_CONCAT(column1); This function seems to accept a column of type varchar2, and returns a comma delimited list of values from the column. I currently have a custom version of this function that works (on my work computer), but it is not optimal and lacks re-usability. Could anyone provide a good, re-usable function like WM_CONCAT that I could use?
要使用 WM_CONCAT，我将其传递给以下内容：WM_CONCAT(column1); 此函数似乎接受 varchar2 类型的列，并从该列返回逗号分隔的值列表。我目前有这个功能的自定义版本（在我的工作计算机上），但它不是最佳的并且缺乏可重用性。谁能提供我可以使用的像 WM_CONCAT 这样好的、可重用的函数？
回答by Rajesh Chamarthi
Do you get an error message when you use wm_concat? Unlike functions like to_char, it is owned by wmsys and you might need to use wmsys.wm_concat to use it. (unless you create the necessary synonyms of course).
使用 wm_concat 时是否收到错误消息？与 to_char 等函数不同，它归 wmsys 所有，您可能需要使用 wmsys.wm_concat 才能使用它。（当然，除非您创建了必要的同义词）。
Now for the actual question,
现在对于实际问题，
This technique is called string aggregation.
这种技术称为字符串聚合。
You could find a lot of other alternatives here.
你可以在这里找到很多其他的选择。
http://www.oracle-base.com/articles/10g/StringAggregationTechniques.phpFor other methods, Search for "stragg" on http://asktom.oracle.comAnother useful link : http://www.orafaq.com/node/2290
http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php对于其他方法，在http://asktom.oracle.com上搜索“stragg” 另一个有用的链接：http://www.orafaq。 com/节点/2290
This is probably the most used one. A lot of teams write their own custom functions which more or less do the same.
这可能是最常用的一种。许多团队编写了自己的自定义函数，这些函数或多或少都是这样做的。
CREATE OR REPLACE FUNCTION get_employees (p_deptno in emp.deptno%TYPE)
RETURN VARCHAR2
IS
l_text VARCHAR2(32767) := NULL;
BEGIN
FOR cur_rec IN (SELECT ename FROM emp WHERE deptno = p_deptno) LOOP
l_text := l_text || ',' || cur_rec.ename;
END LOOP;
RETURN LTRIM(l_text, ',');
END;
/
SHOW ERRORS
while this solution works for varchar2 and number, the best generic solution can be built using Oracle ODCIAggregate interface.
虽然此解决方案适用于 varchar2 和 number，但可以使用 Oracle ODCIAggregate 接口构建最佳通用解决方案。
http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10800/dciaggfns.htm#sthref462
http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10800/dciaggfns.htm#sthref462
Implementation for the same is at the first link above at www.oracle-base.com
相同的实现位于 www.oracle-base.com 上的第一个链接
回答by Dan
I've solved this using a technique similar to the last one in the oracle-base article: define a custom TABLEtype and write a function to aggregate a value of that type into a string. I called my function joinstrand then you can call it as follows:
我已经使用与基于 oracle 的文章中的最后一个类似的技术解决了这个问题：定义一个自定义TABLE类型并编写一个函数来将该类型的值聚合到一个字符串中。我调用了我的函数joinstr，然后您可以按如下方式调用它：
SELECT joinstr(CAST(MULTISET(SELECT column1 FROM table1) AS my_string_table_type), ',')
FROM DUAL
Note: I was on 9i until recently and haven't looked into COLLECT yet.
注意：我直到最近才使用 9i，还没有研究 COLLECT。
