詳解如何在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
CREATEORREPLACEVIEWd_employeeAS
SELECTd.department_name department,
e.last_name ename
FROMdepartments d,
employees e
WHEREd.department_id = e.department_idAND
d.department_nameIN('IT','Marketing','Purchasing');

為此，Tom大師提供了兩種解決方案，如下：

方法一：

使用靜態的SQL從表中選出需要進行連接的所有數據。

CREATEORREPLACEFUNCTIONCONCAT(P_DEPINVARCHAR2)RETURNVARCHAR2IS
L_STRVARCHAR2(200)DEFAULTNULL;
L_SEPVARCHAR2(200)DEFAULTNULL;
BEGIN
FORRCIN(SELECTENAMEFROMD_EMPLOYEEWHEREDEPARTMENT = P_DEP)LOOP
L_STR := L_STR || L_SEP || RC.ENAME;
L_SEP :=',';
ENDLOOP;
RETURNL_STR;
END;

使用下列查詢語句查詢：

SELECTd.department, hr.concat(d.department) employees
FROMd_employee d
GROUPBYdepartment;

方法二：

使用動態SQL，傳入關鍵列(需求中的department)和那一列的一個值，以及需要實際進行連接的列(需求中的ename)以及表。

CREATEORREPLACEFUNCTIONCONCAT2(P_KEY_NAME      INVARCHAR2,

P_KEY_VAL       INVARCHAR2,
P_OTHER_COL_NAMEINVARCHAR2,
P_TNAME         INVARCHAR2)
RETURNVARCHAR2AS
TYPERCISREFCURSOR;
L_STRVARCHAR2(4000);
L_SEPVARCHAR2(200);
L_VALVARCHAR2(4000);
L_CUR RC;
BEGIN
OPENL_CURFOR'select '|| P_OTHER_COL_NAME ||' from '|| P_TNAME ||' where '|| P_KEY_NAME ||' = :x '
USINGP_KEY_VAL;
LOOP
FETCHL_CUR
INTOL_VAL;
EXITWHENL_CUR%NOTFOUND;
L_STR := L_STR || L_SEP || L_VAL;
L_SEP :=',';
ENDLOOP;
CLOSEL_CUR;
RETURNL_STR;
END;

使用下列查詢語句查詢：

SELECTd.department, concat2('department', d.department,'ename','d_employee')FROMd_employee d
GROUPBYd.department;

Tom大師還提到，如果你已經確切知道了需要連接顯示的字段所包含的值的列表（比如，只包含A、T、L三個值），那麼建議使用DECOD()方法。可以查看下面這個鏈接:http://asktom.Oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:124812348063