游标使用错误
delimiter $$
drop procedure if exists sp_c $$
create procedure sp_c (out oMax int)
begin
declare cur1 cursor for select eid from emp;
declare iMax,iTemp int;
declare i int;
set i=1;
open cur1;
fetch cur1 into iTemp;
set iMax=iTemp;
while i < 500 do
fetch cur1 into iTemp;
if iTemp> iMax then
set iMax=iTemp;
end if;
end while;
close cur1;
set oMax=iMax;
end$$
delimiter ;
我刚学习游标的使用.这里主要有什么问题?还有.哪里有游标的例子啊?
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = ‘sp_c ‘ AND type = ‘P ‘)
DROP PROCEDURE sp_c
GO
create procedure sp_c
(
@oMax int
)
AS
BEGIN
— 為 CURSOR 物件提供變數, 以保存 CURSOR 物件當前行欄位值
DECLARE @nMax int
DECLARE rs_cursor CURSOR FOR
select eid from emp
OPEN rs_cursor
FETCH NEXT FROM rs_cursor INTO @nMax
WHILE @@FETCH_STATUS = 0
BEGIN
————-在這裡面做你想要做的事情
FETCH NEXT FROM rs_cursor INTO @CP_NO
END
CLOSE rs_cursor
DEALLOCATE rs_cursor
— 返回資料集
ReturnData:
— 事務完成, 退出預存程序
RETURN 0
— 錯誤處理
ErrorHandler:
GOTO ReturnData
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
