表: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 3Second table:
//Position A# POSITION ------------------ 1 GM 1 DIRECTOR 2 DOCTOR 3 HELLO 3 GMwhen 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,GMis 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.
更多推荐
发布评论