PostgreSQL 中存储过程中使用多游标(refcursor)

最新推荐文章于 2024-01-11 08:39:21 发布

原创最新推荐文章于 2024-01-11 08:39:21 发布 · 7.6k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#postgresql #存储 #null #character #integer #sql

java 同时被 2 个专栏收录

5 篇文章

订阅专栏

sql

2 篇文章

订阅专栏

本文介绍了一个在PostgreSQL中使用存储过程和游标的示例，该过程涉及多游标`_ref`和`_member`的使用。在Java中调用此存储过程并处理返回的游标结果集时，需要注意设置`connection.setAutoCommit(false)`以避免错误，以及正确注册和获取游标输出。

当时在使用时有点迷惑，多次测试后找到了一些方法,不说了直接上代码：

存储过程:

CREATE OR REPLACE FUNCTION select_team(IN _roleuuid character varying, OUT _code integer, OUT _ref refcursor, OUT _member refcursor) RETURNS record AS $BODY$ declare message_row team_member%ROWTYPE; team_row team%ROWTYPE; begin _code=0; perform * from role where role_uuid=_roleuuid; IF FOUND THEN select * into message_row from team_member where team_member_role_uuid=_roleuuid; IF FOUND THEN open _ref for SELECT * FROM team WHERE team_uuid=message_row.team_member_team_uuid; open _member for SELECT * FROM team_member JOIN role ON team_member.team_member_role_uuid = role.role_uuid and team_member.team_member_team_uuid=message_row.team_member_team_uuid; ELSE _code=121; END IF; ELSE _code=108; END IF; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100;

说下上面的存储过程，

message_row team_member%ROWTYPE;

team_row team%ROWTYPE;

定义两个行变量message_row 和team_row

open _ref for ```` 打开游标记录下结果

下面的代码是演示在java中如何调用存储过程：

public TeamData getTeam(String roleuuid) { int cord = 0; CallableStatement cs = null; Connection connection = null; ResultSet teamrs = null; ResultSet number = null; TeamData team = null; try { connection = JDBServer.instance().getConnection(); cs = connection.prepareCall("{call select_team(?,?,?,?)}"); connection.setAutoCommit(false); cs.setString(1, roleuuid); cs.registerOutParameter(2, Types.INTEGER); cs.registerOutParameter(3, Types.OTHER); cs.registerOutParameter(4, Types.OTHER); cs.executeUpdate(); cord = cs.getInt(2); teamrs = (ResultSet) cs.getObject(3); number = (ResultSet) cs.getObject(4); if (teamrs != null && teamrs.next()) { team = getE(teamrs); team.setCode(cord); } if (number != null) { TeamMemberSQL sql=new TeamMemberSQL(); team.setNumberList(sql.getGoodArrayList(number)); } } catch (SQLException e) { team = null; e.printStackTrace(); } finally { try { if (teamrs != null) { teamrs.close(); } if (number != null) { number.close(); } if (connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } return team; }

代码没什么特别难的地方，我不细解析了，说下要注意的地方

connection.setAutoCommit(false);一定要设置，要不会报错

cs.registerOutParameter(3, Types.OTHER);

cs.registerOutParameter(4, Types.OTHER);这两个是注册游标输出的

teamrs = (ResultSet) cs.getObject(3); //获得游标输出结果集 ResultSet

number = (ResultSet) cs.getObject(4);

剩下的就和平时的sql操作一样了！