一个ref cursor的使用例子
Sql代码
create or replace
procedure prod_fixing_datacb IS
type cur_userId is ref cursor; -- 游动浮标定义
c_msgUserIds cur_userId;
c_actionUserIds cur_userId;
c_msgUids cur_userId;
c_todoUserIds cur_userId;
mysql varchar2(4000);
n_msgUserId number:=0; -- 承办用户wf_msg.nUserId
n_actionUserId number:=0; -- 承办用户wf_proc_action.nUserId
n_msgUid number:=0; -- 来文登记用户wf_msg.nUserId
v_todoUserIds varchar2(2000);
n_userId number:=0; -- 获取的承办用户结果
n_nDocId number:=0;
n_nFlowId number:=0;
CURSOR c_fixing_docIds IS
-- 修复收文中批示，拟办节点缺少续办对象的数据
select
gwe.nDocId, gwe.nFlowId
from
wf_doc_gw gw, wf_doc_gw_entity gwe
where gw.ndocId = gwe.ndocId
and gw.ndocsortId = 1
and gw.nstate=0
and gw.cProcUserList is null
and gw.nProcId in (66,10)
order by gw.nDocId;
begin
open c_fixing_docIds;
Loop
Fetch c_fixing_docIds into
n_nDocId,n_nFlowId;
if (n_nFlowId>0) then
-- 获得承办用户USERID
mysql := 'select msgUserId from ('||
'select nMsgId, nUserId as msgUserId,currentProcId,'||
'row_number() over(partition by currentProcId order by dwrite desc) rowNumberId '||
'from wf_msg '||
'where currentProcId = 64 and ndocid='||n_nDocId||') a where rowNumberId=1';
open c_msgUserIds for mysql;
loop
fetch c_msgUserIds into n_msgUserId;
exit when c_msgUserIds%notfound;
if(n_msgUserId>0) then
n_userId := n_msgUserId;
end if;
end loop;
close c_msgUserIds;
if (n_userId=0) then
mysql := 'select actionUserId from '||
'(select nUserId as actionUserId, '||
'row_number() over(partition by nFlowId order by dRecvDate desc) rowNumberId '||
'from wf_proc_action '||
'where nProcId = 64 and '||
'ndocid='||n_nDocId||') a where rowNumberId=1";';
open c_actionUserIds for mysql;
loop
fetch c_actionUserIds into n_actionUserId;
exit when c_actionUserIds%notfound;
if(n_actionUserId>0) then
n_userId := n_actionUserId;
end if;
end loop;
close c_actionUserIds;
end if;
if (n_userId=0) then
mysql := 'select msgUserId from ('||
'select nMsgId, nUserId as msgUserId,currentProcId,'||
'row_number() over(partition by currentProcId order by dwrite desc) rowNumberId '||
'from wf_msg '||
'where currentProcId = 20 and ndocid='||n_nDocId||') a where rowNumberId=1';
open c_msgUids for mysql;
loop
fetch c_msgUids into n_msgUid;
exit when c_msgUids%notfound;
if(n_msgUid>0) then
n_userId := n_msgUid;
end if;
end loop;
close c_msgUids;
