sql server 存储过程示例

如下:

	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 =@mid)) 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  = @todayPost+@todayReply;
	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;
	}