下面是我在周末寫成的存儲過程，它可以大大節(jié)省數(shù)據(jù)庫連接資源。
CREATE  PROCEDURE `test`(in user_id int)
begin
declare m_id int ;
declare stopFlag int DEFAULT 0;
DECLARE cur cursor for
select id from message where userid=user_id;
declare continue handler FOR NOT FOUND set stopFlag=1;
open CUR;
while stopFlag=0 do
FETCH CUR into m_id;
update message set title='updated' ;
end while;
end;
下面是與上面查詢相關的兩個表。
1.DROP TABLE IF EXISTS `message`;
CREATE TABLE `message` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(30) default NULL,
  `content` varchar(1000) default NULL,
  `creatDate` date default NULL,
  `userid` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 ROW_FORMAT=REDUNDANT;
2.DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(11) default NULL,
  `sex` varchar(1) default NULL,
  `age` int(2) default NULL,
  `headImage` varchar(100) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
3.DROP PROCEDURE IF EXISTS `test`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(in user_id int)
begin
declare m_id int ;
declare stopFlag int DEFAULT 0;
DECLARE cur cursor for
select id from message where userid=user_id;
declare continue handler FOR NOT FOUND set stopFlag=1;
open CUR;
while stopFlag=0 do
FETCH CUR into m_id;
update message set title='updated' ;
end while;
end;

//下面是查詢過去七天日報常用的sql、
/**
   * ?õ???????????||get next date from now
   * @param now
   * @return next Date
   * @author zhanglijun
   */
  public static Date getNextDate(Date now){
   if(now==null)return null;
   Calendar cal = Calendar.getInstance();
   cal.setTime(now);
   cal.add(Calendar.DATE, 1);
   return cal.getTime();
  }

  /**
   * ?õ?}??????????sql||get sql String between two days
   * @param start
   * @param end
   * @return String sql
   * @author zhanglijun
   */
  public static String betweenDateSql(String start,String end){
   Date startDate = DateUtil.getDate(start);
   Date tempDate = startDate;
   Date endDate   = DateUtil.getDate(end);
   String sql="select * from ( ";
            while(tempDate.before(endDate)){
             sql+="select '"+getDateStr(tempDate)+"' as diyCol union ";
             tempDate = DateUtil.getNextDate(tempDate);
            }
   sql+=" select '"+DateUtil.getDateStr(endDate)+"' ) diyDate";
   return sql;
  }
//oa_log_info是記錄員工工作日志的表。沒填寫的沒有那天的記錄。結構如下
//log_id emp_id log_date job proportion cause state
 public List queryLog(Integer eid,String start,String end,Integer xid ) {
    
    Session s = this.getSession();
    s.clear();
    String sql =DateUtil.betweenDateSql(start, end);
    sql +="  left outer join( select *  from oa_log_info " +
       "  where (emp_id="+eid+" or emp_id is null) ) b " +
       "  on (b.log_date=diyDate.diyCol)   " +
       "  order by diyCol ";
     Query query = s.createSQLQuery(sql);
     List list = query.list();
     return list;
  }