一个ref cursor的使用例子
create or replaceprocedure 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; end if; Dbms_Output.put_line('n_userId:'||n_userId); mysql:='update dispatch_entitylog '|| 'set sendstatus=1, receiveuserid='||n_userId||','||'receivedate=sysdate,receivestatus=1,'||'receiveusername=(select u.realname from tbuser u where u.userid='||n_userId||'),'||'receiveentityname=(select e1.entityname from tbuser u, tbentity e1 where u.currententityid=e1.entityid and u.userid='||n_userId||'),'||'receiveorgname=(select e2.entityname from tbuser u, tbentity e1, tbentity e2 where u.currententityid=e1.entityid and e1.belongedentityid = e2.entityid and u.userid = '||n_userId||') '||'where nFlowId='||n_nFlowId||' and receivedate is null and nProcId = 64'; -- 承办部门修改，其它暂时不修改execute immediate mysql; Dbms_Output.put_line(mysql); mysql:='insert into wf_proc_flow (nFlowId,nDocId, nProcId, nDocsortId, '|| 'dRecvDate,dFnshDate, nFinishStatus,nOrder, nAndOr)'||'select '||n_nFlowId||', '||n_nDocId||', 64, 1, sysdate,sysdate,1, 0,0 from dual where not exists '||'(select nFlowId from wf_proc_flow where nDocId='||n_nDocId||' '||'and nProcId=64 and nFlowId='||n_nFlowId||')'; execute immediate mysql; Dbms_Output.put_line(mysql); mysql := 'update wf_proc_flow set cUserList='||to_char(n_userId)||' '||'where nFlowId = '||n_nFlowId||' and nDocId='||n_nDocId||' '|| 'and cUserList is null';execute immediate mysql; Dbms_Output.put_line(mysql); mysql := 'update wf_proc_flow set dFnshDate=sysdate, nFinishStatus=1 '|| 'where nFlowId ='||n_nFlowId||' and nDocId='||n_nDocId||' '|| 'and dFnshDate is null';execute immediate mysql; Dbms_Output.put_line(mysql); mysql := 'insert into wf_proc_action (nDocId, nUserId, nDocsortId, nProcId,dRecvDate,dFnshDate, nFinishStatus,nOrder, nAndOr, nFlowId) '||'select '||n_nDocId||','||n_userId||',1,64,sysdate,sysdate,1,0,0,'||n_nFlowId||' '||'from dual where not exists '||'(select nFlowId from wf_proc_action where nDocId='||n_nDocId||' and nProcId=64 and nFlowId='||n_nFlowId||')';execute immediate mysql; Dbms_Output.put_line(mysql); mysql:= 'update wf_proc_action set nUserId='||n_userId||' '|| 'where nFlowId ='||n_nFlowId||' and nDocId='||n_nDocId||' and nUserId is null';execute immediate mysql; Dbms_Output.put_line(mysql); mysql := 'update wf_proc_action set dFnshDate=sysdate, nFinishStatus=1 '|| 'where nFlowId = '||n_nFlowId||' and nDocId='||n_nDocId||' and dFnshDate is null';execute immediate mysql; Dbms_Output.put_line(mysql); mysql := 'delete from wf_doc_gw_entity where nFlowid='||n_nFlowId;execute immediate mySql; Dbms_Output.put_line(mysql); mysql := 'select wmsys.wm_concat(d.nUserId) from (select wpa.nFlowId, wpa.nUserId,'|| 'row_number() over (partition by wpa.nDocId order by wpa.nFlowId) rowNumId,'|| 'row_number() over (partition by wpa.nFlowId order by wpa.nOrder desc) rowNumberId '|| 'from wf_proc_action wpa, wf_doc_gw gw '|| 'where wpa.ndocid = gw.nDocId '|| 'and wpa.nProcId = gw.nProcId '|| 'and wpa.dfnshdate is null '|| 'and gw.nDocId = '||n_nDocId||') d where d.rowNumberId=1 and d.rowNumId=1'; Dbms_Output.put_line(mysql); open c_todoUserIds for mysql; loop fetch c_todoUserIds into v_todoUserIds; exit when c_todoUserIds%notfound; Dbms_Output.put_line('v_todoUserIds:'||v_todoUserIds); end loop; close c_todoUserIds; if(v_todoUserIds is null) then mysql:='update wf_doc_gw set cProcUserList='||n_userId||', nProcStatus=1 where nDocId='||n_nDocId; else mysql:='update wf_doc_gw set cProcUserList='||v_todoUserIds||',nProcStatus=0 where nDocId='||n_nDocId; end if; execute immediate mySql; Dbms_Output.put_line(mysql); end if; Exit when c_fixing_docIds%notfound; end loop; commit; Exception when others then close c_fixing_docIds; rollback; Dbms_Output.put_line(Sqlerrm); if c_fixing_docIds%isopen then close c_fixing_docIds; end if; end prod_fixing_datacb;