|
CREATE TABLE parallel_executions
(
jobid NUMBER(13, 0) ,
processid NUMBER(13, 0) ,
parent_name VARCHAR2(50)
)
/
grant execute on dbms_lock to ... -- only for testing the procedure bellow
/
/*************************** Create procedure *************************/
CREATE OR REPLACE PROCEDURE aggregate_in_parallel
AS
v_job NUMBER;
v_parent_name parallel_executions.parent_name%TYPE := 'LOCK_SLEEP';
v_cnt NUMBER;
BEGIN
DBMS_JOB.submit (v_job, 'dbms_lock.sleep(30);');
INSERT INTO parallel_executions
(jobid, processid, parent_name
)
VALUES (v_job, 1, v_parent_name
);
COMMIT;
DBMS_JOB.submit (v_job, 'dbms_lock.sleep(20);');
INSERT INTO parallel_executions
(jobid, processid, parent_name
)
VALUES (v_job, 2, v_parent_name
);
COMMIT;
DBMS_JOB.submit (v_job, 'dbms_lock.sleep(10);');
INSERT INTO parallel_executions
(jobid, processid, parent_name
)
VALUES (v_job, 3, v_parent_name
);
COMMIT;
LOOP
DBMS_LOCK.sleep (20);
DELETE FROM parallel_executions pe
WHERE NOT EXISTS (SELECT 1
FROM user_jobs uj
WHERE uj.job = pe.jobid)
AND parent_name = v_parent_name;
COMMIT;
SELECT COUNT (*)
INTO v_cnt
FROM parallel_executions
WHERE parent_name = v_parent_name;
EXIT WHEN (v_cnt = 0);
END LOOP;
END;
/
/*************************** Test it *************************/
SET serveroutput on
BEGIN
aggregate_in_parallel;
DBMS_OUTPUT.put_line
('Finish .... continue with others serial procedures ...');
END;
/
/*************************** Create syncronize table *************************/
CREATE TABLE parallel_executions
(
jobid NUMBER(13, 0) ,
processid NUMBER(13, 0) ,
parent_name VARCHAR2(50)
)
/
grant execute on dbms_lock to ... -- only for testing the procedure bellow
/
/*************************** Create procedure *************************/
CREATE OR REPLACE PROCEDURE aggregate_in_parallel
AS
v_job NUMBER;
v_parent_name parallel_executions.parent_name%TYPE := 'LOCK_SLEEP';
v_cnt NUMBER;
BEGIN
DBMS_JOB.submit (v_job, 'dbms_lock.sleep(30);');
INSERT INTO parallel_executions
(jobid, processid, parent_name
)
VALUES (v_job, 1, v_parent_name
);
COMMIT;
DBMS_JOB.submit (v_job, 'dbms_lock.sleep(20);');
INSERT INTO parallel_executions
(jobid, processid, parent_name
)
VALUES (v_job, 2, v_parent_name
);
COMMIT;
DBMS_JOB.submit (v_job, 'dbms_lock.sleep(10);');
INSERT INTO parallel_executions
(jobid, processid, parent_name
)
VALUES (v_job, 3, v_parent_name
);
COMMIT;
LOOP
DBMS_LOCK.sleep (20);
DELETE FROM parallel_executions pe
WHERE NOT EXISTS (SELECT 1
FROM user_jobs uj
WHERE uj.job = pe.jobid)
AND parent_name = v_parent_name;
COMMIT;
SELECT COUNT (*)
INTO v_cnt
FROM parallel_executions
WHERE parent_name = v_parent_name;
EXIT WHEN (v_cnt = 0);
END LOOP;
END;
/
/*************************** Test it *************************/
SET serveroutput on
BEGIN
aggregate_in_parallel;
DBMS_OUTPUT.put_line
('Finish .... continue with others serial procedures ...');
END;
/
This script creates a procedure to run in parallel others procedures.
In the given sql code, the following three jobs are executed in parallel (replace them with your own tasks):
- dbms_lock.sleep(30);
- dbms_lock.sleep(20);
- dbms_lock.sleep(10); |
