Oracle 11gR2:我可以使用DBMS_PARALLEL_EXECUTE来运行我的自定义PL/SQL代码吗? [英] Oracle 11gR2: Can I use DBMS_PARALLEL_EXECUTE to run my custom PL/SQL code?
问题描述
我准备了一些小提琴:
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, 'PROCESSING')
INTO t_process ("process_number", "process_status")
VALUES (3, 'TO_BE_KILLED')
INTO t_process ("process_number", "process_status")
VALUES (4, 'PROCESSING')
INTO t_process ("process_number", "process_status")
VALUES (5, 'PROCESSING')
INTO t_process ("process_number", "process_status")
VALUES (6, 'TO_BE_KILLED')
INTO t_process ("process_number", "process_status")
VALUES (7, 'TO_BE_KILLED')
INTO t_process ("process_number", "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
:
Now I want to use DBMS_PARALLEL_EXECUTE
to run this custom SQL
:
DECLARE
id1 number = :id1;
id2 number = :id2;
BEGIN
MY_PROCEDURE(id1);
END;
所以,我的问题是:
我可以使用DBMS_PARALLEL_EXECUTE
执行上述SQL语句吗?
Can I use DBMS_PARALLEL_EXECUTE
to execute above SQL statement?
因为我只找到了UPDATE
个示例.也许CREATE_CHUNKS_BY_SQL
仅选择TO_BE_KILLED
,然后选择具有上述声明的RUN_TASK
?
Because I only found UPDATE
examples. Maybe CREATE_CHUNKS_BY_SQL
to select only TO_BE_KILLED
and then RUN_TASK
with above statement?
推荐答案
是的,您可以很好地完成11g以上的版本.我很惊讶为什么在这里没有建议.
YES, You can very well do that 11g onwards. I am surprised why it was not suggested here.
您可以在run_task中执行一个过程,例如begin MY_PROCEDURE( :start_id, :end_id ); end;
You can execute a procedure inside run_task like begin MY_PROCEDURE( :start_id, :end_id ); end;
您可能需要修改过程以接受两个参数:start_id,:end_id
You may need to modify procedure to accept two parameters :start_id, :end_id
以下是示例代码(基于"create_chunks_by_rowid").
Here is the sample code (based on ‘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
.
You can create chunks
by following means as well based upon your situation / comfort.
CREATE_CHUNKS_BY_NUMBER_COL
-如果要通过'process_number'更新
CREATE_CHUNKS_BY_NUMBER_COL
-- If you want to update by 'process_number'
CREATE_CHUNKS_BY_SQL
-如果您认为BY_SQL将为您提供一组非常小的要处理的块.请注意,在这种方法中,每个块将只能处理每个块1行(每个块的 start_id和end_id将相同).
CREATE_CHUNKS_BY_SQL
-- If you think BY_SQL is going to give you a very smaller set of chunks to be processed. Beware of the fact that each chunk will be able to process just 1 row per chunk (start_id and end_id will same for each chunk) in this approach.
这篇关于Oracle 11gR2:我可以使用DBMS_PARALLEL_EXECUTE来运行我的自定义PL/SQL代码吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!