存储过程返回两个结果集(游标)。
CREATE OR REPLACE FUNCTION t_user_func(id varchar(20))
RETURNS SETOF refcursor AS
$BODY$
Declare
code refcursor;
result refcursor;
BEGIN
--返回错误码
OPEN code FOR select 1010 as code;
RETURN NEXT code;
--返回查询结果集
OPEN result FOR EXECUTE 'select * from t_user where user_id =''' ||id||'''' ;
RETURN NEXT result;
RETURN;
End;
$BODY$
LANGUAGE plpgsql VOLATILE;
Mybatis映射文件是这样写的:
<select id="getUserByID" parameterType="map" statementType="CALLABLE">
{call t_user_func(
#{id,jdbcType=INTEGER,mode=IN},
#{refcursor, mode=OUT,
jdbcType=OTHER, javaType=ResultSet, resultMap=userResultMap}
)
}
</select>
<resultMap id="userResultMap" type="com.cet.ngp.model.UserStruct">
<collection property="codes" ofType="com.cet.ngp.model.ResultCode">
<id column="code" property="code" jdbcType="INTEGER" />
</collection>
<collection property="users" ofType="com.cet.ngp.model.User">
<id column="user_id" property="userId" jdbcType="VARCHAR" />
<result column="user_name" property="userName" jdbcType="VARCHAR" />
<result column="user_birthday" property="userBirthday"
jdbcType="DATE" />
<result column="user_salary" property="userSalary" jdbcType="INTEGER" />
</collection>
</resultMap>
只能获取到,code结果集,无法获取user结果集。
要怎么改,才能获取到user结果集。