最后，偶自己测试成功，供大家参考吧：
[php]
create or replace package JobsPkg as
/* Types */
subtype tString is varchar2(30);
subtype tJob is varchar2(64);
subtype tDBString is varchar2(255);
type tArrayJob is table of tJob index by binary_integer;
type tArraySeqByJob is table of number index by binary_integer;
/* Execute Executes single job by submiting it to the job queue.*/
procedure Execute(pJob in tJob, pCommit in boolean := true);
/* Execute Executes array of jobs by submiting it to the job queue.*/
procedure Execute(pJobs in tArrayJob, pCommit in boolean := true);
/* SignalCompletion Send an alert to signal job completion. */
procedure SignalCompletion(pJob in tJob, pMessage in tDBString := null);
/* WaitForCompletion---Waits for completion of all parallel jobs
* from the array pJobs passed as a parameter. */
procedure WaitForCompletion(pJobs tArrayJob,pjobstotal tArrayjob);
function BuildAlertName(pJob in tJob) return tString;
end JobsPkg;
create or replace package body JobsPkg as
/* BuildAlertName */
function BuildAlertName(pJob in tJob) return tString is
vAlert tString;
begin
if length(pJob)0 then
vAlert := upper(substr(pJob, instr(pJob, '.') + 1));
else
vAlert := upper(substr(pJob, 1, 30));
end if;
end if;
return vAlert;
end BuildAlertName;
/**Execute Executes single job by submiting it to the job queue. */
procedure Execute(pJob in tJob, pCommit in boolean := true) is
vJobtJob := ltrim(rtrim(pJob));
vJobNum binary_integer;
begin
if substr(vJob, -1) != ';' then
vJob := vJob || ';';
end if;
dbms_job.submit(vJobNum, vJob,sysdate+1/(24*60));--,'trunc(sysdate,''mi'')+ 1/(24*60)');
--DebugPkg.Log(to_char(vJobNum) || ': ' || vJob);
if pCommit then
commit;
end if;
end Execute;
/* Execute Executes set of jobs by submiting it to the job queue.*/
procedure Execute(pJobs in tArrayJob, pCommit in boolean := true) is
begin
if pJobs.count > 0 then
for nIndex in pJobs.first .. pJobs.last loop
Execute(pJobs(nIndex), (not pCommit));
end loop;
if pCommit then
commit;
end if;
end if;
end Execute;
/* SignalCompletion Send an alert to signal job completion.*/
procedure SignalCompletion(pJob in tJob, pMessage in tDBString := null) is
begin
dbms_alert.signal(BuildAlertName(pJob),
nvl(pMessage, pJob || ' completed.'));
commit;
end SignalCompletion;
/* WaitForCompletion
* Waits for completion of all parallel jobs from the array pJobs passed as a parameter.
*/
procedure WaitForCompletion(pJobs tArrayJob,pJobsTotal tArrayJob) is
vAlertstarrayjob;
vAlert tJob;
vNametJob;
vMessage tDBString;
vStatuspls_integer;
vTimeOut pls_integer := 3600;
inumber;
v_job_diff binary_integer;
begin
/* this values record the number of left unsubmit procedures */
v_job_diff:=pJobsTotal.count-pjobs.count;
if pJobsTotal.count > 0 then
/* Step 1. Build array of alerts.*/
for nIndex in pJobsTotal.first .. pJobsTotal.last loop
vAlerts(nIndex) := BuildAlertName(pJobsTotal(nindex));
end loop;
/* Step 2.Register for alerts. */
vAlert := vAlerts.first;
vname:=valerts(valert);
while (vAlert is not null) loop
vname:=valerts(valert);
dbms_alert.register(vname);
vAlert := vAlerts.next(vAlert);
end loop;
/* Step 3. Waiting until all of the alerts are received. */
i:=0;
while (vAlerts.count > 0) loop
dbms_alert.waitany(vName, vMessage, vStatus, vTimeOut);
if vStatus = 0 then
dbms_alert.remove(vName);
for vindex in valerts.first..valerts.last loop
if upper(trim(valerts(vindex)))=vName then
vAlerts.delete(vindex);
exit;
end if;
end loop;
dbms_output.put_line('Received alert: ' || vName ||
' with message: ' || vMessage);
i:=i+1;
/* heresubmit the left procedure as job */
if i<=v_job_diff then
Execute(pJobstotal(pjobs.count+i), true);
end if;
/*endsubmit the left procedure as job */
end if;
end loop;
end if;
end WaitForCompletion;
end JobsPkg;
[/php]
|
