Example of using a ref cursor

Example of using a ref cursor SQL code create or replace procedure operation IS type cur_userId is ref cursor; -- the swimming buoy defines c_msgUserIds cur_userId; c_actionUserIds cur_userId; c_msgUids cur_userId; mysql varchar2 (4000); n_msgUserId number: = 0; -- owner user wf_msg.nUserId n_actionUserId number: = 0; -- owner user wf_proc_action.nUserId n_msgUid number: = 0; -- User wf_msg.nUserId v_todoUserIds varchar2 (2000); n_userId number: = 0; -- Obtain the owner user result n_nDocId number: = 0; n_nFlowId number: = 0; CURSOR c_fixing_docIds IS -- fixed the instructions in the Receiving text. The data of the objects to be continued IS missing in the proposed node. 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 -- get owner 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 found; 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'; -- Subject Department modification, execute immediate mysql; merge (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; merge (mysql); mysql: = 'insert into wf_proc_action (nDocId, nUserId, nDocsortId, nProcId, dRecvDate, dFnshDate, nFinishStatus, nOrder, nAndOr, nFlowId) '| 'select' | n_nDocId |', '| n_userId |', sysdate, sysdate, 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'; values (mysql); open c_todoUserIds for mysql; loop fetch c_todoUserIds into v_todoUserIds; exit when c_todoUserIds % notfound; values ('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; terminate (mysql); end if; Exit when c_fixing_docIds % notfound; end loop; commit; Exception when others then close c_fixing_docIds; rollback; restore (Sqlerrm ); if c_fixing_docIds % isopen then close c_fixing_docIds; end if; end prod_fixing_datacb;