oracle 在sql developer中执行异步存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7324995/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Execute async stored procedure in sql developer
提问by kralco626
I would like to use Oracle SQL Developer to execute a stored procedure asynchronously a large number of times.
我想使用 Oracle SQL Developer 多次异步执行存储过程。
Pseudo Code
伪代码
var pStatus number
var pOraErrCd varchar2
var pOraErrMsg varchar2
for i 1 .. 1000 -- do async
loop
exec myproc('test',:pStatus ,:pOraErrCd ,:pOraErrMsg);
end loop;
The stored procedure's purpose is to do some inserts. For testing I just want to execute the stored procedure asynchronously a large number of times. I don't care about any return values.
存储过程的目的是做一些插入。为了测试，我只想大量异步执行存储过程。我不在乎任何返回值。
Is there a "easy"way to do this?
有没有“简单”的方法来做到这一点？
回答by Justin Cave
Since you want to simulate N sessions each calling the procedure 1000/N times, I would probably do something like
由于您想模拟 N 个会话，每个会话调用该过程 1000/N 次，我可能会做类似的事情
CREATE OR REPLACE PROCEDURE call_myproc_n_times( p_n IN NUMBER )
AS
p_status NUMBER;
p_ora_error_code VARCHAR2(1000);
p_ora_error_msg VARCHAR2(1000);
BEGIN
FOR i IN 1 .. p_n
LOOP
myproc( 'test',
p_status,
p_ora_error_code,
p_ora_error_msg );
END LOOP;
END;
DECLARE
l_num_sessions number := 10;
l_exec_per_session number := 100;
l_jobno pls_integer;
BEGIN
FOR i IN 1 .. l_num_sessions
LOOP
dbms_job.submit(
l_jobno,
'BEGIN ' ||
' call_myproc_n_times( ' || l_exec_per_session || ' ); ' ||
'END;',
sysdate + interval '1' minute );
END LOOP;
commit;
END;
This example will start 10 sessions each of which will execute the procedure 100 times in quick succession assuming your database's JOB_QUEUE_PROCESSESis at least 10 meaning that Oracle is allowed to have 10 jobs running in the background simultaneously. Creating the CALL_MYPROC_N_TIMESprocedure isn't strictly necessary-- it just makes building the string to execute in the job easier.
此示例将启动 10 个会话，每个会话将快速连续执行该过程 100 次，假设您的数据库JOB_QUEUE_PROCESSES至少为 10，这意味着允许 Oracle 同时在后台运行 10 个作业。创建CALL_MYPROC_N_TIMES过程并不是绝对必要的——它只是使构建要在工作中执行的字符串更容易。
An alternative would be to submit 1000 jobs each of which just called MYPROConce and relying on the JOB_QUEUE_PROCESSESparameter to limit the number of jobs that would be run simultaneously. That would work, it's just more difficult to change database parameters if you want to run more of fewer simultaneous sessions-- it's easy to adjust L_NUM_SESSIONSin the code I posted.
另一种方法是提交 1000 个作业，每个作业只调用MYPROC一次，并依靠JOB_QUEUE_PROCESSES参数来限制同时运行的作业数量。这会起作用，如果您想运行更多或更少的同时会话，更改数据库参数会更加困难 -L_NUM_SESSIONS在我发布的代码中很容易调整。
