存储过程学习笔记
1.首先mysql 中的 '分隔符' 是分号(;)
所以在创建存储过程的时候,要先重新定义一下分隔符。这样,存储过程中的语句才不会被当成正常的语句执行。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
DELIMITER // DROP PROCEDURE IF EXISTS test_cursor // create procedure getAllTypesOfCards(in cardId long, in userId long, in classId long, in cardType int, in isNew boolean) begin declare userIdTmp long; declare DONE int; declare userIdCur cursor for select user1_id as userid from user_relation_table where relation = 'FRIEND' and user2_id = userId union select user2_id as userid from user_relation_table where relation = 'FRIEND' and user1_id = userId; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET DONE = 1; open userIdCur; REPEAT fetch userIdCur into userIdTmp; if isNew = true then select * from card_table where cardId < id and state = 0 and (`type` = cardType or class_id = classId or user_id = userIdTmp) order by id desc limit 10; else select * from card_table where cardId > id and state = 0 and (`type` = cardType or class_id = classId or user_id = userIdTmp) order by id desc limit 10; end if; UNTIL DONE END REPEAT; close userIdCur; end;// DELIMITER ; call getAllTypesOfCards(129427,163,1,1,false);
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
delimiter // create procedure getSelfOrFrdsPicVoice(in userId long, in picVoiceId long) begin declare userIdTmp long; declare DONE int; declare userIdCur cursor for select user1_id as userid from user_relation_table where relation = 'FRIEND' and user2_id = userId union select user2_id as userid from user_relation_table where relation = 'FRIEND' and user1_id = userId; declare continue handler for SQLSTATE '02000' set DONE = 1; open userIdCur; repeat fetch userIdCur into userIdTmp; select id,pic_id,publish_time,user_id,update_time from pic_voice_table where id < picVoiceId and (user_id = userId or user_id = userIdTmp) order by id desc limit 10; until done end repeat; close userIdCur; end;//
存储过程定义的参数有三种,in,out,inout。in的话,在过程内部是不能被改动的,out的话是能被改动的,并且外部也是可以获得的,inout是内部可以改动,外部也可以获得。
我这里用到了游标(cursor),我的理解是类似于结果集,里面存了查询语句查询出来的多条结果(我这里说的肯定很不专业),通过fetch 去遍历,fetch cursor_name into xxx,类似于for(xxx : cursor_name)
即可以用cursor这个游标,去遍历每一条结果集,再进行另外一个操作。
结果集必须打开(open cursor_name),再循环(repeat),也得定义一个让循环结束的变量,符合结果end repeat,并close cursor
//删除存储过程
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
DROP PROCEDURE IF EXISTS 'procedure_name'
//查看存储过程
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SHOW CREATE PROCEDURE procedure_name
JDBC调用存储过程代码如下
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Connection conn = null; CallableStatement callStmt = null; ResultSet rs = null; List<Card> cards = new ArrayList<Card>(); //cardId,userId,classId,cardType,isNew String sql = "call getAllTypesOfCards(?,?,?,?,?)"; try { conn = JdbcUtils.getConnection(); callStmt = conn.prepareCall(sql); callStmt.setLong(1, cardId); callStmt.setLong(2, userId); callStmt.setLong(3, classId); callStmt.setInt(4, 1);//card type callStmt.setBoolean(5, isNew); rs = callStmt.executeQuery(); while(rs.next()){ Card card = new Card(); long id = rs.getLong("id"); String title = rs.getString("title"); long createTime = rs.getLong("create_time"); // long classId = rs.getLong("class_id"); long updateTime = rs.getLong("update_time"); int scope = rs.getInt("scope"); int discuss_count = rs.getInt("discuss_count"); String userName = rs.getString("user_name"); int type = rs.getInt("type"); card.setId(id); card.setTitle(title); card.setCreateTime(createTime); card.setClassId(classId); card.setUserId(userId); card.setUpdateTime(updateTime); card.setScope(scope); card.setDiscussCount(discuss_count); card.setUserName(userName); card.setType(type); cards.add(card); } } catch (SQLException e) { throw new SQLRunTimeException(e); }finally{ JdbcUtils.free(rs, callStmt, conn); }