postgre函数返回游标
(2023-07-12 16:19:53)
标签:
jdbcpostgrerefcursor |
分类: J2EE笔记 |
1 函数示例
RETURNS
refcursor
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL
UNSAFE
OWNER TO ugadev;
IS '测试返回查询结果集';
return recordList;
CREATE OR REPLACE FUNCTION ga.fn_c_querytable(
--ref_name refcursor,
tab_name text)
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)
COMMENT ON FUNCTION ga.fn_c_querytable(text)
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);
}
});
}
@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));
}
}
}
后一篇:linux端口转发