Oracle中连接输出结果

一个偶然的机会，看到ASK TOM上的一个问题

(http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:229614022562)，是关于查询结果字符串连接的。下图是对一个视图(视图代码附后，全部操作是在Oracle 10g中的HR schema中完成的)进行查询得到的常见的结果。

department ename

marketing  hartstein

marketing  fay

purchasing raphaely

purchasing khoo

...

it         hunold

it         ernst

...

但是也可能遇到需要下面这样的结果。

department ename

purchasing raphely,khoo,baida,tobias,himuro,colmenares

it         hunold,ernst,austin,pataballa,lorentz

marketing  hartstein,fay

 

CREATE OR REPLACE VIEW d_employee AS

SELECT d.department_name department,

       e.last_name ename

  FROM departments d,

       employees e

 WHERE d.department_id = e.department_id AND

       d.department_name IN ('IT', 'Marketing', 'Purchasing');

为此，Tom大师提供了两种解决方案，如下：

方法一：

使用静态的SQL从表中选出需要进行连接的所有数据。

CREATE OR REPLACE FUNCTION CONCAT(P_DEP IN VARCHAR2) RETURN VARCHAR2 IS

  L_STR VARCHAR2(200) DEFAULT NULL;

  L_SEP VARCHAR2(200) DEFAULT NULL;

BEGIN

  FOR RC IN (SELECT ENAME FROM D_EMPLOYEE WHERE DEPARTMENT = P_DEP) LOOP

    L_STR := L_STR || L_SEP || RC.ENAME;

    L_SEP := ',';

  END LOOP;

  RETURN L_STR;

END;

使用下列查询语句查询：

SELECT d.department, hr.concat(d.department) employees

  FROM d_employee d

 GROUP BY department;

 

方法二：

使用动态SQL，传入关键列(需求中的department)和那一列的一个值，以及需要实际进行连接的列(需求中的ename)以及表。

CREATE OR REPLACE FUNCTION CONCAT2(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(200);

  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;

使用下列查询语句查询：

SELECT d.department, concat2('department', d.department, 'ename', 'd_employee') FROM d_employee d

  GROUP BY d.department;

 

Tom大师还提到，如果你已经确切知道了需要连接显示的字段所包含的值的列表（比如，只包含A、T、L三个值），那么建议使用DECOD()方法。可以查看下面这个链接: http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:124812348063

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14969361/viewspace-591823/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14969361/viewspace-591823/