如下:
if object_id('index_proc') is not null drop proc index_proc go create procedure index_proc @postAmount int output, @replyAmount int output, @todayAmount int output, @userAmount int output, @newUser varchar(20) output as begin declare @todayPost int; declare @todayReply int; ---新建一个临时表,通过下面的游标循环把数据插入到此临时表中 if exists( select * from tempdb..sysobjects where id=OBJECT_ID('tempdb..#new_post') ) drop table #new_post; create table #new_post( mid int , pid int , theme varchar(100), username varchar(30), pdatetime smalldatetime, today_post int, postAmount int, replyAmount int, mname varchar(20) ) ----游标 declare cur cursor for select mid from module; declare @mid int; open cur; fetch next from cur into @mid; while @@fetch_status=0 begin insert into #new_post(mod.mid,p.pid,theme,username,pdatetime,today_post,postAmount,replyAmount,mname) select top 1 mod.mid,pid,theme,username,pdatetime, (select count(p1.pid) from post as p1 where convert(char(10),p1.pdatetime,126)=convert(char(10),getdate(),126) and p1.mid = @mid) as today_post, (select count(p2.pid) from post as p2 where p2.mid = @mid) as postAmount, (select count(r1.rid) from reply as r1 where r1.pid in(select p3.pid from post as p3 where p3.mid [email protected])) as replyAmount, mod.mname from module as mod left join post as p on mod.mid = p.mid where mod.mid = @mid order by p.pdatetime desc; fetch next from cur into @mid; end close cur deallocate cur select * from #new_post; select @postAmount = count(1) from post; select @replyAmount = count(1) from reply; select @todayPost = count(1) from post where convert(char(10),pdatetime,126)=convert(char(10),getdate(),126); select @todayReply = count(1) from reply where convert(char(10),rdatetime,126)=convert(char(10),getdate(),126); set @todayAmount = @[email protected]; select @userAmount = count(1) from bbs_user; select top 1 @newUser = username from bbs_user order by registerTime desc end ---游标调用 go declare @postAmount int; declare @replyAmount int; declare @todayAmount int; declare @userAmount int; declare @newUser varchar(20); execute index_proc @postAmount output,@replyAmount output,@todayAmount output,@userAmount output,@newUser output; print @postAmount print @todayAmount??
jdcb调用存储过程代码:
public BbsIndex callIndexProc(){ Connection conn = getConnection(); try { CallableStatement cs = conn.prepareCall("{call dbo.index_proc(?,?,?,?,?)}"); cs.registerOutParameter(1,Types.INTEGER); cs.registerOutParameter(2,Types.INTEGER); cs.registerOutParameter(3,Types.INTEGER); cs.registerOutParameter(4,Types.INTEGER); cs.registerOutParameter(5,Types.VARCHAR); ResultSet rs = cs.executeQuery(); BbsIndex bbs = new BbsIndex(); while(rs.next()){ //ModuleEntity me = new ModuleEntity(); IndexTempEntity ite = new IndexTempEntity(); ite.setMid(rs.getInt(1)); ite.setPid(rs.getInt(2)); ite.setTheme(rs.getString(3)); ite.setUsername(rs.getString(4)); ite.setPdatetime(rs.getDate(5)); ite.setToday_post(rs.getInt(6)); ite.setPostAmount(rs.getInt(7)); ite.setReplyAmount(rs.getInt(8)); ite.setMname(rs.getString(9)); bbs.getIndexTempList().add(ite); } bbs.setPostAmount(cs.getInt(1)); bbs.setReplyAmount(cs.getInt(2)); bbs.setTodayAmount(cs.getInt(3)); bbs.setUserAmount(cs.getInt(4)); bbs.setNewUser(cs.getString(5)); return bbs; } catch (SQLException e) { e.printStackTrace(); }finally{ DbUtils.closeQuietly(conn); } return null; }?