mysql procedure返回多数据集

浏览数：34 / 时间：2015年06月12日

返回多数据集写法：
第一种

DROP PROCEDURE IF EXISTS `p_query_user` ; 
DELIMITER //  
    CREATE PROCEDURE p_query_user(  p_inout_user_id  INT   )  
    begin
    declare c varchar(100) default ‘0‘;
         SELECT * FROM T_SD_USER WHERE USER_ID = p_inout_user_id ;
         SET p_inout_user_id =2222;
end //
 DELIMITER ; 
set @uId = 3000 ;
CALL p_query_user(@uId );
SELECT @uId ;

第二种

mysql>delimiter //
mysql>drop procedure if exists proc;
mysql>create procedure proc()
begin
create temporary table tmp1 like t;
insert into tmp1 select * from t;
create temporary table tmp2 like t;
insert into tmp2 select * from t;
end;
//
mysql>delimiter ;
mysql>select * from tmp1;
mysql>select * from tmp2;

注：使用临时表在session级一直可用，再次调用存储过程需要先DROP临时表。

hibernate call

@Test
    public void test0() {
        try {

            String procedureSql = "CALL p_query_user( :userId)";
            SQLQuery sqlQuery = t.getSession().createSQLQuery(procedureSql)
                    .addEntity(TSdUser.class);
            Object userId = 3000;
            sqlQuery.setParameter("userId", userId);
            List<TSdUser> result = sqlQuery.list();
            for (TSdUser tSdUser : result) {
                System.out.println(tSdUser);
            }
            System.out.println(userId);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

结果如下:

Hibernate: CALL p_query_user( ?)
TSdUser [userSysMlb=100.0, userUseredMlb=633137.0, userDoDiscoverCount=0, userDoCommonsSayCount=0, createTime=2014-05-08 12:20:35.0, lastActivateSystemMsgTime=2014-07-17 12:10:00.0, lastActivateSusCommentMsgTime=2014-05-08 12:20:35.0, lastActivateNegativeMsgTime=2014-06-17 10:27:37.0, lastActivatePraiseMsgTime=2014-05-08 12:20:35.0, lastActiveTime=2014-08-11 18:45:52.0, priMsgTime=2014-08-09 14:30:27.0, lockTime=null, userMark=null, userSubMark=null, userRemark=null, userInfo=null, userLoginIp=127.0.0.1, userLogginPhoneCode=13724770797, userRegisterInfo=null, userRealName=null, userRegisterPhoneCode=13724770797, userRegistrationCity=null, userPhoneCity=湛江, userDeviceType=Android, userLoginQqId=1, userLoginWeiXinId=null, userLoginSinaBlogId=null, userWeiXinOpenId=111, cteateType=1, restaurantWeiXinOpenId=222, weiXinSubscribeTime=2014-05-17 13:29:28.0, weiXinSubscribeStatus=SUBSCRIBE, weiXinUnSubscribeTime=null, userId=3000, userName=MJorcen, userTotalMlb=1.1111111E7, userDoAttentionCount=0, userReAttentionCount=0, userDoPraiseCount=0, userRePraiseCount=11, userDoNegativeCount=0, userReNegativeCount=6, userDoShowDetailCount=38, userDoGetVoucherCount=5, userDoUsedVoucherCount=2, userPhone=13724770797, userGender=G, userGrade=0, userHeader=null]

或者:

    @Test
    public void test1() {
        try {

            String procedureSql = "CALL p_query_user(?)";
            Connection connection = t.getSession().connection();
            Object userId = 3000;
            java.sql.CallableStatement cs = connection
                    .prepareCall(procedureSql);
            cs.setInt(1, 3000);
            
            cs.registerOutParameter("p_inout_user_id", Types.INTEGER);
            cs.execute();
            int patams1 = cs.getInt(1);
            ResultSet resultSet = cs.getResultSet();
            while (resultSet.next()) {
                System.out.println(resultSet.getInt("USER_ID"));
            }
            System.out.println(patams1);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

一个网上的例子;

public String callFirstProcedures(final long qyid, final int pdys) {
    String procedureSql = "{call get_qyaqdcpdjb(?,?,?)}";
    Object pj = jdbcTemplate.execute(procedureSql,
            new CallableStatementCallback() {
                public Object doInCallableStatement(CallableStatement cs)
                        throws SQLException, DataAccessException {
                    cs.setLong(1, qyid);
                    cs.setInt(2, pdys);
                    //你需要注册一个输出参数
                    cs.registerOutParameter(3, Types.VARCHAR);
                    cs.execute();
                    return cs.getString(3);
                }
            });
    return HtmsUtils.formatObject(pj);
}