在Oracle（11.2之前版）中：当使用强制转换（收集（...））时，如何订购结果？

When using cast(collect(...)), how do I order the result?

使用演员表（收集（...））时，如何订购结果？

I have a function called GetStringForTable, defined like this:

我有一个名为GetStringForTable的函数，定义如下：

FUNCTION GetStringForTable(vTable in TVarCharTable, vDelimeter in varchar default ',') return VarChar2 is
    aResult varchar2(32767);
    i int;
  begin
    if vTable.count = 0 then 
      return '';
    end if;

    for i in 1 .. vTable.Count loop
      if i > 1 then
        aResult := aResult || vDelimeter;
      end if;
      aResult := aResult || vTable(i);
    end loop;
    return aResult;
  end GetStringForTable;

And I use it like this:

我这样使用它：

select
  name,
  rep.GetStringForTable
      ((
          Select 
            cast(collect(name) as TVarCharTable)  
          from 
            contacts
          where 
            debtoraccount = dt.accountnumber
      )
      ,', ' --Delimiter
      ) "Contacts" 
from debtable dt
where name like '%Svein%';

The problem is that the result is not ordered. I get "Dave, Bob, Carol, Alice" when I would like "Alice, Bob, Carol, Dave". How do I order the result? If tried the following, but none works:

问题是结果没有排序。当我想要“爱丽丝，鲍勃，卡罗尔，戴夫”时，我会得到“戴夫，鲍勃，卡罗尔，爱丽丝”。我如何订购结果？如果尝试以下，但没有工作：

select
  name,
  rep.GetStringForTable
      ((
          Select 
            cast(collect(name) as TVarCharTable)  
          from 
            contacts
          where 
            debtoraccount = dt.accountnumber
          order by name  <= ORA-00907: missing right parenthesis
      )
      ,', ' --Skilletegn
      ) "Contacts"
from debtable dt
where name like '%Svein%';

and

和

select
  name,
  rep.GetStringForTable
      ((
        select * from
        (
          Select 
            cast(collect(name) as TVarCharTable)  
          from 
            contacts
          where 
            debtoraccount = dt.accountnumber  <= ORA-00904: string: invalid identifier
          order by name
        )
      )
      ,', ' --Skilletegn
      ) "Contacts"
from debtable dt
where name like '%Svein%';

Ideally,I would like to do the ordering without changing the GetStringForTable-function.

理想情况下，我想在不更改GetStringForTable函数的情况下进行排序。

Can anyone help?

有人可以帮忙吗？

2 个解决方案

#1

one method would be to modify GetStringForTable so that the output comes ordered (you could have two functions: one ordered and the other not)

一种方法是修改GetStringForTable以便输出有序（你可以有两个函数：一个是有序的，另一个不是）

SQL> CREATE OR REPLACE TYPE TVarCharTable AS TABLE OF VARCHAR2(30);
  2  /
Type created

SQL> CREATE OR REPLACE FUNCTION GetStringForTable(
  2        vTable IN TVarCharTable,
  3        vDelimeter IN VARCHAR DEFAULT ','
  4     ) RETURN VARCHAR2 IS
  5     aResult VARCHAR2(32767);
  6     i       INT := 1;
  7  BEGIN
  8     IF vTable.COUNT = 0 THEN
  9        RETURN '';
 10     END IF;
 11     FOR cc IN (SELECT COLUMN_VALUE cv
 12                  FROM TABLE(CAST(vtable AS TVarCharTable))
 13                 ORDER BY COLUMN_VALUE) LOOP
 14        IF i > 1 THEN
 15           aResult := aResult || vDelimeter;
 16        END IF;
 17        aResult := aResult || cc.cv;
 18        i := i+1;
 19     END LOOP;
 20     RETURN aResult;
 21  END GetStringForTable;
 22  /
Function created

SQL> SELECT GetStringForTable(TVarCharTable('B', 'A', 'D', 'C')) FROM dual;

GETSTRINGFORTABLE(TVARCHARTABL
---------------------------------------------------------------------
A,B,C,D

Update

I've found a workaround, but unfortunately upon additional tests the ORDER is not guaranteed. It will depend upon the access path chosen. Still, it may work in your case :

我找到了一个解决方法，但不幸的是，经过额外的测试，ORDER无法保证。这取决于所选择的访问路径。不过，它可能适用于您的情况：

SQL> SELECT dNAME,
  2         GetStringForTable((SELECT CAST(COLLECT(eNAME) AS TVarCharTable)
  3                              FROM (SELECT *
  4                                      FROM scott.emp
  5                                     ORDER BY ename) e
  6                                   /* ^^^^^^^^ */
  7                              WHERE e.deptno = dt.deptno),
  8                             ', ' --Delimiter
  9                             ) "Contacts"
 10    FROM scott.dept dt;

DNAME          Contacts
-------------- ----------------------------------------------------
ACCOUNTING     CLARK, KING, MILLER
RESEARCH       ADAMS, FORD, JONES, SCOTT, SMITH
SALES          ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD
OPERATIONS     

You can sort of force the ORDER with a trick to materialize the subquery, but this would prevent the optimizer from using most of the efficient paths, for example:

您可以使用技巧强制ORDER来实现子查询，但这会阻止优化器使用大多数有效路径，例如：

SQL> WITH employee AS (
  2     SELECT *
  3       FROM scott.emp
  4      WHERE ROWNUM > 0 /* will materialize the subquery */
  5      ORDER BY ename
  6  )
  7  SELECT dNAME,
  8         GetStringForTable((SELECT CAST(COLLECT(eNAME) AS TVarCharTable)
  9                              FROM employee e
 10                              WHERE e.deptno = dt.deptno),
 11                             ', ' --Delimiter
 12                             ) "Contacts"
 13    FROM scott.dept dt;

DNAME          Contacts
-------------- -----------------------------------------------------
ACCOUNTING     CLARK, KING, MILLER
RESEARCH       ADAMS, FORD, JONES, SCOTT, SMITH
SALES          ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD
OPERATIONS

#2

    cast(collect(name order by name) as TVarCharTable)  

This syntax is first mentioned in the 11gR1 manual. But it seems to work fine with 10g even though it is not documented.

11gR1手册中首次提到了此语法。但即使没有记录，它似乎与10g一起正常工作。