sql server 存储过程示例
如下:
if object_id('index_proc') is not nulldrop proc index_procgocreate procedure index_proc@postAmount int output,@replyAmount int output,@todayAmount int output,@userAmount int output,@newUser varchar(20) outputasbegindeclare @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=0begininsert 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 =@mid)) as replyAmount,mod.mnamefrom 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;endclose curdeallocate 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 = @todayPost+@todayReply;select @userAmount = count(1) from bbs_user;select top 1 @newUser = username from bbs_user order by registerTime descend---游标调用godeclare @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 @postAmountprint @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;}?