PL/SQL と PL/pgSQL のout引数が refcursor のプロシージャ を実行する

out引数がカーソルの時のファンクションの実行について

記事が少なかったのでメモしておく。

とりあえずテーブルを作る 

>||
-- create
CREATE TABLE Test(
 id numeric,
 name VARCHAR(255) 
);

とりあえずレコードも作る 

--レコードを作る
insert into Test values(1,'aaa');
insert into Test values(2,'bbb');
insert into Test values(3,'ccc');
insert into Test values(4,'ddd');
insert into Test values(5,'eee');
insert into Test values(6,'fff');

 Oracleの場合

PL/SQLの作成

create or replace procedure get_test(
 in_id number
 ,full_cur out sys_refcursor
,id_cur out sys_refcursor
 ) 
is
begin
 open full_cur for
 select * from test;

 open id_cur for 
 select * from test where id =in_id ;

END;
/

oracleではプロシージャで作成。

Postgresではプロシージャという概念がないのでファンクションで作成します。

PL/SQLの実行

var full_cur refcursor;
var id_cur refcursor;
exec get_test(1,:full_cur ,:id_cur );

print full_cur ;
print id_cur ;

callでも行けるはずですが、確認してません。

print でカーソルの中身を確認できます。

PostgreSQLの場合

PL/pgSQLの作成

create or replace function get_test(
 in in_id numeric
 ,out full_cur refcursor
,out id_cur refcursor)
returns record 
as $$
declare
begin

 open full_cur for 
   select * from test;
 --closeは書かない

 open id_cur for 
 select * from test where id =in_id ;
 --closeは書かない
end;
$$ language plpgsql;

closeをかくのはReturnで明示的に戻している場合で、（たぶん）

out引数の時にcloseしちゃうと返った時にカーソルが閉じてしまって見れなくなります。

PL/pgSQLの実行

begin;
select * from get_test(1);
fetch all in "<unnamed portal 3>";
fetch all in "<unnamed portal 4>";
rollback;

unnamed portal (無名ポータル）にカーソルが返ってくるので

fetchで中身を見てあげる感じです。

※3とか4なのは1回ミスったからです。