在sql Developer中执行异步存储过程

发布于 2024-12-03 09:10:48 字数 352 浏览 1 评论 0原文

我想使用 Oracle SQL Developer 多次异步执行存储过程。

伪代码

var pStatus number
var pOraErrCd varchar2
var pOraErrMsg varchar2
for i 1 .. 1000 -- do async
loop 
    exec myproc('test',:pStatus ,:pOraErrCd ,:pOraErrMsg);
end loop;

存储过程的目的是执行一些插入操作。为了测试，我只想多次异步执行存储过程。我不关心任何返回值。

有没有一种“简单”的方法来做到这一点？

原文

I would like to use Oracle SQL Developer to execute a stored procedure asynchronously a large number of times.

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

不再让梦枯萎 2024-12-10 09:10:48

由于您想要模拟 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;

此示例将启动 10 个会话，每个会话将快速连续执行该过程 100 次，假设您的数据库的 JOB_QUEUE_PROCESSES 至少为 10，这意味着 Oracle 允许同时在后台运行 10 个作业。创建 CALL_MYPROC_N_TIMES 过程并不是绝对必要的——它只是使构建要在作业中执行的字符串变得更容易。

另一种方法是提交 1000 个作业，每个作业仅调用 MYPROC 一次，并依靠 JOB_QUEUE_PROCESSES 参数来限制同时运行的作业数量。这可行，只是如果您想运行更多或更少的并发会话，则更改数据库参数会更加困难 - 在我发布的代码中调整 L_NUM_SESSIONS 很容易。

Since you want to simulate N sessions each calling the procedure 1000/N times, I would probably do something like

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_PROCESSES is at least 10 meaning that Oracle is allowed to have 10 jobs running in the background simultaneously. Creating the CALL_MYPROC_N_TIMES procedure isn't strictly necessary-- it just makes building the string to execute in the job easier.

An alternative would be to submit 1000 jobs each of which just called MYPROC once and relying on the JOB_QUEUE_PROCESSES parameter 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_SESSIONS in the code I posted.

回复收藏 0 原文

~没有更多了~