加载中…
个人资料
iPan
iPan
  • 博客等级:
  • 博客积分:0
  • 博客访问:4,972
  • 关注人气:234
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
正文 字体大小:

postgre函数返回游标

(2023-07-12 16:19:53)
标签:

jdbc

postgre

refcursor

分类: J2EE笔记
函数示例
CREATE OR REPLACE FUNCTION ga.fn_c_querytable(
--ref_name refcursor,
tab_name text)
    RETURNS refcursor 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
declare
--返回值 代码名称
ref_name refcursor;
begin
if tab_name = '1' then
RAISE notice 'query 1' ;
open ref_name for select * from ga.table1;
elseif tab_name = '2' then
RAISE notice 'query 2' ;
open ref_name for select * from ga.table2;
elseif tab_name = '3' then 
RAISE notice 'query 3' ;
open ref_name for select * from ga.table3;
end if;
return ref_name;
end
$BODY$;

ALTER FUNCTION ga.fn_c_querytable(text)
    OWNER TO ugadev;

COMMENT ON FUNCTION ga.fn_c_querytable(text)
    IS '测试返回查询结果集';

2 pgadmin4测试
begin;
SELECT ga.fn_c_querytable();
fetch all in "《unnamed portal 1》";
COMMIT;

3 JDBC测试
public class QuerytableTest extends BaseServerTest {
public List findBigScreenQuery(String tabName) {
return (List) Db.execute((connection) -> {
CallableStatement cs = null;
List recordList = null;
ResultSet rs = null;
try {
connection.setAutoCommit(false);
cs = connection.prepareCall("{?=call ga.fn_c_querytable(?)}");
cs.registerOutParameter(1, Types.REF_CURSOR);
cs.setObject(2, tabName);
cs.execute();
rs = (ResultSet) cs.getObject(1);
if (rs != null) {
Config config = DbKit.getConfig();
recordList = config.getDialect().buildRecordList(config, rs);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtil.close(cs);
JdbcUtil.close(rs);
}
    return recordList;
});
}

@Test
public void test() {
// List list = findBigScreenQuery("1");
List list = findBigScreenQuery("2");
int size = (list != null) ? list.size() : 0;
System.out.println("size=" + size);
for (int i=0, len=Math.min(size, 3); i
System.out.println(list.get(i));
}
}
}

0

阅读 收藏 喜欢 打印举报/Report
  

新浪BLOG意见反馈留言板 欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 产品答疑

新浪公司 版权所有