PL/SQLのプロシージャ間でカーソル?の受渡

質問

PL/SQLのプロシージャ間でカーソル?の受渡

お世話になります。
親でループ対象とするカーソルを、子プロシージャで生成し
親に戻すということは可能でしょうか。

うまく伝わりにくいかと思いますので、下記にVBで行った例を記します。

親-----------------------
Sub aaa()

　　Dim rs As New ADODB.Recordset

　　　Call bbb(rs)

　　　Do Until rs.EOF
　　　　　Debug.Print rs!COLUMN1
　　　　　rs.MoveNext
　　　Loop

End Sub

子-----------------------
Function bbb(ByRef rs As ADODB.Recordset)

　rs.Open "SELECT * FROM TABLE1", CurrentProject.Connection

End Function

以上、お願い致します。

回答No.2

yamada_g · ベストアンサー

エラーの原因は、カーソル変数の場合はforループを利用できないためだと思います。
なので、loop内fetchするようにします。
こんな感じでしょうか

--REF CUSOR宣言
CREATE OR REPLACE PACKAGE PKG_TEST_CURSOR
IS
--TYPE CUR_TEST IS REF CURSOR;
--レコード全体を取得するなら型を指定した方が楽だと思います
TYPE CUR_TEST IS REF CURSOR RETURN TEST%ROWTYPE;
END;
/

--親プロシージャ
CREATE OR REPLACE PROCEDURE PC_TEST親(PI_HIKISU1 IN TEST.TEST_COLUMN%TYPE)
IS
　TEST_REC PKG_TEST_CURSOR.CUR_TEST;
--　l_test_rec PKG_TEST_CURSOR.CUR_TEST;
　--CUR_TEST を型指定したのでrowtypeが使えます
　l_test_rec TEST_REC%ROWTYPE;
BEGIN
　PD_TEST子(PI_HIKISU1,TEST_REC);

--　FOR l_test_rec IN TEST_REC LOOP
--　　dbms_output.put_line(l_test_rec.TEST_COLUMN99);
--　END LOOP;
　--ref cursorの場合はforループはできないので
　loop
　　fetch TEST_REC into l_test_Rec;
　　exit when TEST_REC%notfound;
　　dbms_output.put_line(l_test_rec.TEST_COLUMN99);
　end loop;
　close TEST_REC;
END;
/

--子プロシージャ
CREATE OR REPLACE PROCEDURE PD_TEST子
　(
　　PI_HIKISU1 IN TEST.TEST_COLUMN%TYPE,
　　PI_TEST_RECIN OUT PKG_TEST_CURSOR.CUR_TEST)
IS
　l_test_rec PKG_TEST_CURSOR.CUR_TEST;
BEGIN
　OPEN l_test_rec FOR SELECT * FROM TEST WHERE TEST_COLUMN = PI_HIKISU1;
　--カーソルを返すならopenするだけでいいのでは？
--　LOOP
--　　EXIT WHEN l_test_rec%NOTFOUND;
--　　　　FETCH lc_test_rec INTO PI_TEST_REC;
--　　END LOOP;
END;
/

お礼

yamada_g 様

細かく教えて頂きありがとうございました。
無事に動作する事を確認できました。

ただ一部誤りがあるようでした。

--子プロシージャ
CREATE OR REPLACE PROCEDURE PD_TEST子
　(
　　PI_HIKISU1 IN TEST.TEST_COLUMN%TYPE,
　　PI_TEST_RECIN OUT PKG_TEST_CURSOR.CUR_TEST)
IS
　--l_test_rec PKG_TEST_CURSOR.CUR_TEST;--←直接引数に返すので必要ない
BEGIN
　--OPEN l_test_rec FOR SELECT * FROM TEST WHERE TEST_COLUMN = PI_HIKISU1;
　OPEN PI_TEST_REC FOR SELECT * FROM TEST WHERE TEST_COLUMN = PI_HIKISU1; --←引数に返してあげる

以上です、本当に助かりました。

回答No.1

yamada_g

ref cursor を使えばいいのではないかと思います。

http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/appdev.102/B19257-01/sqloperations.html#9160

補足

yamada_g様

ご回答ありがとうございます。
教えて頂いたとおりref cursorでいけそうですが
実際コーディングしたところ、うまくいきませんでした。
どこか考え方が根本的に間違ってるかもしれません。
エラーは親プロシージャで
[TEST_RECがプロシージャではないか、未定義です」と出ます。
ご指摘いただければ幸いです。

--REF CUSOR宣言
CREATE OR REPLACE PACKAGE PKG_TEST_CURSOR
IS
TYPE CUR_TEST IS REF CURSOR;
END;

--親プロシージャ
CREATE OR REPLACE PROCEDURE PC_TEST親(PI_HIKISU1 IN TEST.TEST_COLUMN%TYPE)
IS
　TEST_REC　PKG_TEST_CURSOR.CUR_TEST;
　l_test_rec　PKG_TEST_CURSOR.CUR_TEST;
BEGIN
　PD_TEST子(PI_HIKISU1,TEST_REC);

　FOR l_test_rec IN TEST_REC LOOP
　　dbms_output.put_line(l_test_rec.TEST_COLUMN99);
　END LOOP;
END;

--子プロシージャ
CREATE OR REPLACE PROCEDURE PD_TEST子
　(
　　PI_HIKISU1　IN　TEST.TEST_COLUMN%TYPE,
　　PI_TEST_REC IN OUT PKG_TEST_CURSOR.CUR_TEST)
IS
　l_test_rec　PKG_TEST_CURSOR.CUR_TEST;
BEGIN
　OPEN l_test_rec FOR SELECT * FROM TEST WHERE TEST_COLUMN = PI_HIKISU1;
　LOOP
　　EXIT WHEN l_test_rec%NOTFOUND;
　　　　FETCH lc_test_rec INTO PI_TEST_REC;
　　END LOOP;
END;