ute 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;