我准备了一些
fiddle：
CREATE TABLE t_process
("process_number" int,"process_status" varchar2(12))
;
INSERT ALL
INTO t_process ("process_number","process_status")
VALUES (1,'PROCESSING')
INTO t_process ("process_number","process_status")
VALUES (2,"process_status")
VALUES (3,'TO_BE_KILLED')
INTO t_process ("process_number","process_status")
VALUES (4,"process_status")
VALUES (5,"process_status")
VALUES (6,"process_status")
VALUES (7,"process_status")
VALUES (8,'WAITING')
INTO t_process ("process_number","process_status")
VALUES (9,'KILLED')
SELECT * FROM dual
;
这是我的处理程序：
CREATE OR REPLACE PROCEDURE MY_PROCEDURE(IN_ID IN NUMBER) IS BEGIN UPDATE T_PROCESS SET process_status = 'KILLING' WHERE process_number = IN_ID; COMMIT; -- DO SOME STUFF UPDATE T_PROCESS SET process_status = 'KILLED' WHERE process_number = IN_ID; COMMIT; END MY_PROCEDURE;
现在我想使用DBMS_PARALLEL_EXECUTE来运行这个自定义sql：
DECLARE id1 number = :id1; id2 number = :id2; BEGIN MY_PROCEDURE(id1); END;
所以,我的问题是：
我可以使用DBMS_PARALLEL_EXECUTE执行上面的sql语句吗？
因为我只找到了UPDATE示例.也许CREATE_CHUNKS_BY_sql只选择TO_BE_KILLED,然后用上面的语句选择RUN_TASK？
解决方法
是的,你可以很好地做到11g以上.我很惊讶为什么不在这里建议.
你可以在run_task中执行一个过程,比如begin MY_PROCEDURE(：start_id,：end_id);结束;
您可能需要修改过程以接受两个参数：start_id,：end_id
以下是示例代码(基于’create_chunks_by_rowid’).
DECLARE l_task VARCHAR2(30) := 'parallel_processing'; l_sql_stmt VARCHAR2(32767); l_try NUMBER; l_status NUMBER; BEGIN DBMS_PARALLEL_EXECUTE.create_task (task_name => l_task); DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name => l_task,table_owner => 'SCHEMANAME',table_name => 'T_PROCESS',by_row => TRUE,chunk_size => 10000); l_sql_stmt := 'begin MY_PROCEDURE( :start_id,:end_id ); end;'; DBMS_PARALLEL_EXECUTE.run_task(task_name => l_task,sql_stmt => l_sql_stmt,language_flag => DBMS_sql.NATIVE,parallel_level => 10); -- If there is error,RESUME it for at most 2 times. l_try := 0; l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task); WHILE(l_try < 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED) Loop l_try := l_try + 1; DBMS_PARALLEL_EXECUTE.resume_task(l_task); l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task); END LOOP; DBMS_PARALLEL_EXECUTE.drop_task(l_task); END; /
您可以根据您的情况/舒适度通过以下方式创建块.
CREATE_CHUNKS_BY_NUMBER_COL – 如果要按’process_number’更新
CREATE_CHUNKS_BY_sql – 如果您认为BY_sql将为您提供一组非常小的待处理块.请注意,在这种方法中,每个块只能处理每个块一行(start_id和end_id对于每个块都相同).
