PostgreSQL 返回多个结果集
一个直译的方法就是用游标来做,不过也可以把结果集放到数组里面或者一个大的临时表或者是XML等的格式来输出。
比如在MySQL里面可以这样简单的写一个存储过程。
delimiter || create procedure sp_get_multiple_set1() begin select * from j1 limit 20; select * from tmp_2 limit 20; end; || delimiter ; 执行下会得到两个结果集。 t_girl--call sp_get_multiple_set1(); +------+------+ | id | str1 | +------+------+ | 1 | love | | 2 | hate | +------+------+ 2 rows in set (0.00 sec) +------+---------------+ | id | register_date | +------+---------------+ | 1 | 2014-01-18 | | 2 | 2014-01-03 | | 3 | 2014-01-16 | +------+---------------+ 3 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) t_girl--
在PostgreSQL环境下,来看下下面的函数代码,只能用游标来实现,而且还不能在函数里面返回。
create or replace function sp_get_multiple_set1( IN f_id int, refcursor, refcursor ) returns setof refcursor as $ytt$ declare r1 alias for $2; r2 alias for $3; begin open r1 for select * from j1 where id =f_id; return next r1; open r2 for select * from tmp_2 where id = f_id; return next r2; end; $ytt$ language plpgsql;
接下来,在一个事务里面来获取对应的游标结果集。
t_girl=# begin; BEGIN t_girl=# select * from sp_get_multiple_set1(1,‘a‘::refcursor,‘b‘::refcursor); sp_get_multiple_set1 ---------------------- a b (2 rows) t_girl=# fetch all from a; id | str1 ----+------ 1 | love (1 row) t_girl=# fetch all from b; id | register_date ----+--------------- 1 | 2013-01-20 (1 row) t_girl=# close a; CLOSE CURSOR t_girl=# close b; CLOSE CURSOR t_girl=# commit; COMMIT t_girl=#
如果是是多个结果集,但是每个结果集里面只有一行数据的话,也可以用OUT参数来实现。
create or replace function sp_get_single_set2( f_id int, IN refcursor, IN refcursor, OUT r3 ytt_j1, OUT r4 ytt_tmp_2 ) returns record as $ytt$ declare r1 alias for $2; r2 alias for $3; begin open r1 for select * from j1 where id =f_id; open r2 for select * from tmp_2 where id = f_id; loop fetch r1 into r3; fetch r2 into r4; exit when not found; return; end loop; close r1; close r2; end; $ytt$ language plpgsql;
t_girl=# select * from sp_get_multiple_set2(1,‘a‘::refcursor,‘b‘::refcursor); r3 | r4 ----------+---------------- (1,love) | (1,2013-01-20) (1 row) t_girl=#
如果返回的多个结果集的字段和类型都一致的话,那就跟单个结果集一样了。
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。