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

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

存储过程:

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操作一样了！

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!