sql server 存储过程示例

225025

　　如下:

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;
}