两个游标应该申明在一起
-----------------------------------
Cursor NoticeStaffInfos is
(Select distinct C.NOTICESTAFFID From SFRJRTU A,SFRJLOCK B,SFRJNOTICELOCKID C,SFRJNOTICESTAFF D
WHERE A.RTUID = B.RTUID AND B.LOCKID = C.LOCKID AND C.NOTICESTAFFID = D.NOTICESTAFFID AND A.RTUID = RTUIDNo);cursor NoticeStaffIDExist is
(Select C.* From SFRJNOTICELOCKID C,SFRJNOTICESTAFF D WHERE C.NOTICESTAFFID = D.NOTICESTAFFID AND C.NOTICESTAFFID = NoitceInfo.NOTICESTAFFID);
----------------------------------------
但是由于你的第二个游标使用的第一个游标中的变量,加之你所要操作是删除,你可以这样操作:
BEGIN
GetResult := False; Declare
Cursor NoticeStaffInfos is
(Select distinct C.NOTICESTAFFID From SFRJRTU A,SFRJLOCK B,SFRJNOTICELOCKID C,SFRJNOTICESTAFF D
WHERE A.RTUID = B.RTUID AND B.LOCKID = C.LOCKID AND C.NOTICESTAFFID = D.NOTICESTAFFID AND A.RTUID = RTUIDNo);
BEGIN
open NoticeStaffInfos; for NoitceInfo in NoticeStaffInfos Loop
Delete From SFRJNOTICELOCKID WHERE NOTICESTAFFID = NoitceInfo.NOTICESTAFFID;
Commit; Delete from SFRJNOTICESTAFF
where rowid in (Select d.rowid
From SFRJNOTICELOCKID C,SFRJNOTICESTAFF D
WHERE C.NOTICESTAFFID = D.NOTICESTAFFID
AND C.NOTICESTAFFID = NoitceInfo.NOTICESTAFFID
and c.noticestaffid is not null);
Commit;
Exit When NoticeStaffinfos%NotFound;
end Loop; close NoticeStaffInfos;
END;
End;
-----------------------------------
Cursor NoticeStaffInfos is
(Select distinct C.NOTICESTAFFID From SFRJRTU A,SFRJLOCK B,SFRJNOTICELOCKID C,SFRJNOTICESTAFF D
WHERE A.RTUID = B.RTUID AND B.LOCKID = C.LOCKID AND C.NOTICESTAFFID = D.NOTICESTAFFID AND A.RTUID = RTUIDNo);cursor NoticeStaffIDExist is
(Select C.* From SFRJNOTICELOCKID C,SFRJNOTICESTAFF D WHERE C.NOTICESTAFFID = D.NOTICESTAFFID AND C.NOTICESTAFFID = NoitceInfo.NOTICESTAFFID);
----------------------------------------
但是由于你的第二个游标使用的第一个游标中的变量,加之你所要操作是删除,你可以这样操作:
BEGIN
GetResult := False; Declare
Cursor NoticeStaffInfos is
(Select distinct C.NOTICESTAFFID From SFRJRTU A,SFRJLOCK B,SFRJNOTICELOCKID C,SFRJNOTICESTAFF D
WHERE A.RTUID = B.RTUID AND B.LOCKID = C.LOCKID AND C.NOTICESTAFFID = D.NOTICESTAFFID AND A.RTUID = RTUIDNo);
BEGIN
open NoticeStaffInfos; for NoitceInfo in NoticeStaffInfos Loop
Delete From SFRJNOTICELOCKID WHERE NOTICESTAFFID = NoitceInfo.NOTICESTAFFID;
Commit; Delete from SFRJNOTICESTAFF
where rowid in (Select d.rowid
From SFRJNOTICELOCKID C,SFRJNOTICESTAFF D
WHERE C.NOTICESTAFFID = D.NOTICESTAFFID
AND C.NOTICESTAFFID = NoitceInfo.NOTICESTAFFID
and c.noticestaffid is not null);
Commit;
Exit When NoticeStaffinfos%NotFound;
end Loop; close NoticeStaffInfos;
END;
End;
for NoitceInfo in NoticeStaffInfos Loop
这两个都是打开游标的方式,只采用一个即可。
去掉 open NoticeStaffInfos
----------------------------
BEGIN
GetResult := False;Declare
Cursor NoticeStaffInfos is
(Select distinct C.NOTICESTAFFID From SFRJRTU A,SFRJLOCK B,SFRJNOTICELOCKID C,SFRJNOTICESTAFF D
WHERE A.RTUID = B.RTUID AND B.LOCKID = C.LOCKID AND C.NOTICESTAFFID = D.NOTICESTAFFID AND A.RTUID = RTUIDNo);
BEGIN
for NoitceInfo in NoticeStaffInfos Loop
Delete From SFRJNOTICELOCKID WHERE NOTICESTAFFID = NoitceInfo.NOTICESTAFFID;
Commit; Delete from SFRJNOTICESTAFF d
where NOTICESTAFFID in (Select NOTICESTAFFID
From SFRJNOTICELOCKID C
WHERE C.NOTICESTAFFID = D.NOTICESTAFFID
AND C.NOTICESTAFFID = NoitceInfo.NOTICESTAFFID)
and noticestaffid is not null;Commit;
Exit When NoticeStaffinfos%NotFound;
End Loop;close NoticeStaffInfos;
END;
End;
显式:open NoticeStaffInfos; 需要显式的关闭!close NoticeStaffInfos;
隐式: for NoitceInfo in NoticeStaffInfos Loop 使用完系统自动关闭!
所以支持你采用bzszp(www.bzszp.533.net) 的方法
Cursor NoticeStaffInfos is
Select distinct C.NOTICESTAFFID From SFRJRTU A,SFRJLOCK B,SFRJNOTICELOCKID C,SFRJNOTICESTAFF D WHERE A.RTUID = B.RTUID AND B.LOCKID = C.LOCKID AND C.NOTICESTAFFID = D.NOTICESTAFFID AND A.RTUID = RTUIDNo;
cursor NoticeStaffIDExis(p varchar2) is
Select C.* From SFRJNOTICELOCKID C,SFRJNOTICESTAFF D WHERE C.NOTICESTAFFID = D.NOTICESTAFFID AND C.NOTICESTAFFID = p;
BEGIN
for NoitceInfo in NoticeStaffInfos Loop
Delete From SFRJNOTICELOCKID WHERE NOTICESTAFFID = NoitceInfo.NOTICESTAFFID;
Commit;
for NoticeExist in NoticeStaffIDExist(NoitceInfo.NOTICESTAFFID) Loop
IF NoticeExist.NOTICESTAFFID is not Null THEN
Exit;
End IF;
Delete FROM SFRJNOTICESTAFF WHERE NOTICESTAFFID = NoitceInfo.NOTICESTAFFID;
Commit;
Exit;
end Loop;
end Loop;
END;