PL / SQL使用LOOPING中的数据获取(PL/SQL using fetching data in LOOPING)

表：1）测试2）位置

第一张桌子

//TEST A# --------------- 1 2 3

第二张桌子：

//Position A# POSITION ------------------ 1 GM 1 DIRECTOR 2 DOCTOR 3 HELLO 3 GM

当我在我的sqlplus中使用以下pl / sql时

DECLARE c_a# test.A#%TYPE; c_pos position.position%TYPE; CURSOR c_app IS SELECT t.a#,p.position from test t INNER JOIN position p ON t.a#=p.p#; BEGIN OPEN c_app LOOP FETCH c_app into c_a# , c_pos; DBMS_OUTPUT.PUT_LINE( c_a# || ':' || c_pos ); END LOOP; CLOSE c_app; END; /

这里是输出：

1:GM 1:Director 2:Doctor ... ...

预期产出：

1:GM,Director 2:Doctor 3:HELLO,GM

我的循环有什么问题吗？

Table : 1.)Test 2.)Position

First table

//TEST A# --------------- 1 2 3

Second table:

//Position A# POSITION ------------------ 1 GM 1 DIRECTOR 2 DOCTOR 3 HELLO 3 GM

when i use the following pl/sql in my sqlplus

DECLARE c_a# test.A#%TYPE; c_pos position.position%TYPE; CURSOR c_app IS SELECT t.a#,p.position from test t INNER JOIN position p ON t.a#=p.p#; BEGIN OPEN c_app LOOP FETCH c_app into c_a# , c_pos; DBMS_OUTPUT.PUT_LINE( c_a# || ':' || c_pos ); END LOOP; CLOSE c_app; END; /

here is the output:

1:GM 1:Director 2:Doctor ... ...

Expected output:

1:GM,Director 2:Doctor 3:HELLO,GM

is there anything wrong in my looping?

最满意答案

我不确定您使用的是哪种环境，因为Oracle对10G和11G版本具有不同的字符串聚合功能。

对于10G，你应该考虑使用WM_CONCAT函数。以下是您试图通过cursor实现的示例代码

DECLARE CURSOR C_APP IS SELECT T.A#, WM_CONCAT (P.POSITION) FROM TEST T INNER JOIN POSITION P ON T.A# = P.P# GROUP BY T.A#; C_A# TEST.A#%TYPE; C_POS POSITION.POSITION%TYPE; BEGIN OPEN C_APP; LOOP FETCH C_APP INTO C_A#, C_POS; EXIT WHEN C_APP%NOTFOUND; DBMS_OUTPUT.PUT_LINE (C_A# || ':' || C_POS); END LOOP; CLOSE C_APP; END;

对于11G您可以使用listagg功能。以下是示例代码

DECLARE CURSOR C_APP IS SELECT T.A#, LISTAGG(P.POSITION,',') WITHIN GROUP (ORDER BY P.POSITION) FROM TEST T INNER JOIN POSITION P ON T.A# = P.P# GROUP BY T.A#; C_A# TEST.A#%TYPE; C_POS POSITION.POSITION%TYPE; BEGIN OPEN C_APP; LOOP FETCH C_APP INTO C_A#, C_POS; EXIT WHEN C_APP%NOTFOUND; DBMS_OUTPUT.PUT_LINE (C_A# || ':' || C_POS); END LOOP; CLOSE C_APP; END;

确保你已经set serveroutput on来显示结果。

I'm not sure which environment you're using because Oracle have different string aggregation function for 10G and 11G release.

For 10G you should consider using WM_CONCAT function. Below is the sample code which you're trying to achieve through cursor

DECLARE CURSOR C_APP IS SELECT T.A#, WM_CONCAT (P.POSITION) FROM TEST T INNER JOIN POSITION P ON T.A# = P.P# GROUP BY T.A#; C_A# TEST.A#%TYPE; C_POS POSITION.POSITION%TYPE; BEGIN OPEN C_APP; LOOP FETCH C_APP INTO C_A#, C_POS; EXIT WHEN C_APP%NOTFOUND; DBMS_OUTPUT.PUT_LINE (C_A# || ':' || C_POS); END LOOP; CLOSE C_APP; END;

For 11G you can use listagg function. Below is the sample code

DECLARE CURSOR C_APP IS SELECT T.A#, LISTAGG(P.POSITION,',') WITHIN GROUP (ORDER BY P.POSITION) FROM TEST T INNER JOIN POSITION P ON T.A# = P.P# GROUP BY T.A#; C_A# TEST.A#%TYPE; C_POS POSITION.POSITION%TYPE; BEGIN OPEN C_APP; LOOP FETCH C_APP INTO C_A#, C_POS; EXIT WHEN C_APP%NOTFOUND; DBMS_OUTPUT.PUT_LINE (C_A# || ':' || C_POS); END LOOP; CLOSE C_APP; END;

Make sure you have set serveroutput on in order to display the result.

更多推荐