一。存储过程如下。
CREATE PROCEDURE CURSOR_BOOK @count int
AS
BEGIN
SET NOCOUNT ON --设置为ON,不返回计数
declare @error int
declare @temp varchar(50)--临时变量,用来保存游标值
set @error=0
BEGIN TRAN --申明事务
--申明游标
declare book_cursor CURSOR FOR select bookid from book
--打开游标
open book_cursor
WHILE @@FETCH_STATUS = 0 --返回被 FETCH 语句执行的最后游标的状态,而不是任何当前被连接打开的游标的状态。
begin
-- 开始循环游标变量
FETCH NEXT FROM book_cursor INTO @temp
--执行sql操作
if(@count > 5)
select null as bookid,
null as bookname,
null as bookclassno,
null as author from book
else
begin
update book set content='test context' where bookid=@temp
set @error=@error+@@error --记录每次运行sql后 是否正确 0正确
end
end
if @error=0--没有错误 统一提交事务
begin
commit tran--提交
end
else
begin
rollback tran--回滚
end
select * from book where bookid >100005
CLOSE book_cursor--关闭游标
DEALLOCATE book_cursor--释放游标
SET NOCOUNT OFF --返回计数
END
二。JAVA的JDBC调用如下:
ResultSet rs = null ;
CallableStatement stmt = null;
String sp = "{call CURSOR_BOOK(?) }";
try {
stmt = conn.prepareCall(sp);
stmt.setInt(1, 3);
rs = stmt.executeQuery();
if(rs!=null){
while(rs.next()){
String bookid = rs.getString("bookid");
System.out.println("bookid:"+bookid);
String bookname = rs.getString("bookname");
System.out.println("bookname:"+bookname);
String author = rs.getString("author");
System.out.println("author:"+author);
}
}
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
注:多个SELECT存在一于一个存储过程时,返回第一个SELECT的结果集,有写操作时,如果不能正确返回结果集。问题原因有下。
1。JDBC调用用stmt.executeQuery();
2。存储过程中设置SET NOCOUNT ON ,影响行数