Oracle功能:复制wm_concat [英] Oracle Function: Replicate wm_concat
问题描述
我目前正在Crystal Reports中开发一个项目,该项目拒绝使用Oracle 10g中允许的未公开的函数WM_CONCAT。
这里是WM_CONCAT头信息
WM_CONCAT(p1 IN VARCHAR2)RETURN VARCHAR2
$ b要使用WM_CONCAT,我将它传递给它:WM_CONCAT(column1);该函数似乎接受一个varchar2类型的列,并返回列中值的逗号分隔列表。我目前有这个功能的自定义版本(在我的工作计算机上),但它不是最佳的,并且缺乏可重用性。任何人都可以提供像WM_CONCAT这样的好用的可重用函数吗?
使用wm_concat?
与to_char之类的函数不同,它由wmsys拥有,您可能需要使用wmsys.wm_concat来使用它。 (除非你创建必要的同义词)。
现在对于实际问题,
这个技巧是称为字符串聚合。
你可以在这里找到很多其他的选择。
http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php a>
对于其他方法,请在 http://asktom.oracle上搜索stragg。 com
另一个有用的链接: http://www.orafaq.com/node / 2290
这可能是最常用的一种。
很多团队编写自己的自定义函数,或多或少都是这样。创建或替换函数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;
/
显示错误
此解决方案适用于varchar2和数字,最好的通用解决方案可以使用Oracle ODCIAggregate接口来构建。
http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10800/dciaggfns.htm#sthref462
相同的实施方式位于www.oracle-base.com上面的第一个链接。
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
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?
解决方案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).
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.php For other methods, Search for "stragg" on http://asktom.oracle.com Another useful link : http://www.orafaq.com/node/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.
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
这篇关于Oracle功能:复制wm_concat的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!