在SQL中,select语句可以得到一个记录集;而游标可以存储一个结果集,并通过循环将这个结果集里的数据一条一条
的取出来进行处理。由于游标一次只能作用一行,它的速度会明显慢于相应的集操作。
1、 编写存储过程
Create procedure PAcademyReader
AS
DECLARE @academy VARCHAR(10)
DECLARE @readerNo CHAR(3)
DECLARE @readerName NVARCHAR(10)
DECLARE @sex CHAR(4)
DECLARE @bookName VARCHAR(10)
DECLARE @price MONEY
DECLARE @count INT
DECLARE @allPrice MONEY
BEGIN TRAN Tran_aa --开始一个事物Tran_aa
DECLARE cur_Academy SCROLL CURSOR FOR -- 查询结果为游标(cur_Academy )的数据集
SELECT DISTINCT Academy
FROM BL_TBL_Reader,BL_TBL_Borrow
WHERE BL_TBL_Borrow.ReaderNo = BL_TBL_Reader.ReaderNo
OPEN cur_Academy --打开游标(cur_Academy )
FETCH FIRST FROM cur_Academy INTO @academy --读取游标中的数据,也可以是 Fetch cur_Academy。
WHILE (@@FETCH_STATUS = 0) --对游标中的数据进行处理
BEGIN
PRINT @academy
DECLARE cur_Reader SCROLL CURSOR FOR
SELECT BL_TBL_Reader.ReaderNo
,ReaderName
,case Sex WHEN 0 THEN '男' WHEN 1 THEN '女' WHEN NULL THEN '不清楚' END AS Sex
,BookName
,Price
FROM BL_TBL_Book,BL_TBL_Reader,BL_TBL_Borrow
WHERE BL_TBL_Book.BookNo =BL_TBL_Borrow.BookNo
AND BL_TBL_Borrow.ReaderNo = BL_TBL_Reader.ReaderNo
AND Academy = @academy
OPEN cur_Reader
print @@CURSOR_ROWS
FETCH FIRST FROM cur_Reader INTO @readerNo,@readerName,@sex,@bookName,@price
WHILE(@@FETCH_STATUS=0)
BEGIN
PRINT ' '+@readerNo +' '+ @readerName +' '+@sex+' '+@bookName+' '+CONVERT(NVARCHAR(10),@price)
FETCH NEXT FROM cur_Reader INTO @readerNo,@readerName,@sex,@bookName,@price
END
CLOSE cur_Reader
DEALLOCATE cur_Reader
SELECT @count = COUNT(BL_TBL_Reader.ReaderNo)
,@allPrice = SUM(Price)
FROM BL_TBL_Book,BL_TBL_Reader,BL_TBL_Borrow
WHERE BL_TBL_Book.BookNo =BL_TBL_Borrow.BookNo
AND BL_TBL_Borrow.ReaderNo = BL_TBL_Reader.ReaderNo
AND Academy = @academy
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN Tran_aa
CLOSE cur_Academy
DEALLOCATE cur_Academy
RETURN
END
PRINT '借书记录:' +CONVERT(NVARCHAR(5), @count) +' 借书金额:'+ CONVERT(NVARCHAR(10),@allPrice)
PRINT ''--======================================================================================'
-- (Declare mycursor cursor for Select c2 from t1 )
-- (Update t1 set c2 = 1000 + c2 where current of mycursor)
FETCH NEXT FROM cur_Academy INTO @academy --读取下一条,也可以是直接就是 Fetch cur_Academy ;
END
CLOSE cur_Academy --关闭游标
DEALLOCATE cur_Academy -- 解除分配
COMMIT TRAN Tran_aa
--执行存储过程
EXEC PAcademyReader