Oracle中的并行处理

发布于 2024-09-26 10:52:35 字数 322 浏览 1 评论 0原文

我有一个“过程 A”，其中它必须向前端返回一个空游标，然后立即继续下一步，其中它将调用一个过程，该过程需要 20 分钟才能完成该过程。

Procedure A(cur_out refcursor)
begin

 OPEN cur_out  for
 select 
 null empname,
 null empid
 from dual;

procedure B();//Will take 20 min

end;

这里“过程 A”应该返回空游标，而不等待“过程 B”完成。

如何在oracle中实现这一点。

提前致谢。

原文

I had a 'procedure A' in which it has to return a null cursor to front end and then immediately it should continue with the next step in which it will call a procedure which will take 20 min to complete the proc.

Procedure A(cur_out refcursor)
begin

 OPEN cur_out  for
 select 
 null empname,
 null empid
 from dual;

procedure B();//Will take 20 min

end;

Here the 'Procedure A' should return the null cursor without waiting for the 'Procedure B' to complete.

How to implement this in oracle.

Thanks in advance.

分享到QQ

分享到微博

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

发布评论

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

南冥有猫 2024-10-03 10:52:35

假设您不需要过程 B 与 A 在同一会话中运行，您可以安排作业异步运行，即

CREATE OR REPLACE PROCEDURE a( p_cur_out OUT SYS_REFCURSOR )
AS
  l_jobno pls_integer;
BEGIN
  OPEN p_cur_out 
   FOR SELECT cast( null as varchar2(64) ) empname, 
              cast( null as integer ) empid
         FROM dual;

  dbms_job.submit( l_jobno,
                   'BEGIN B(); END;' );
  commit;
END a;

您将从 A 返回，并且将立即安排一个作业在单独的会话中运行过程 B (请注意，在当前会话提交之前，该作业不会启动，这就是我在此处添加提交的原因 - 如果您的代码要在其他地方提交，您可以消除它）。 dbms_job.submit 所做的就是创建作业——直到过程返回后作业才会开始执行。

如果 B 接受参数，则需要动态构建 PL/SQL 块。因此，如果 B 采用两个 NUMBER 参数

CREATE OR REPLACE PROCEDURE a( p_cur_out OUT SYS_REFCURSOR )
AS
  l_jobno  pls_integer;
  l_param1 pls_integer;
  l_param2 pls_integer;
BEGIN
  OPEN p_cur_out 
   FOR SELECT cast( null as varchar2(64) ) empname, 
              cast( null as integer ) empid
         FROM dual;

  dbms_job.submit( l_jobno,
                   'BEGIN B(' || to_char( l_param1 ) || ', ' ||
                                 to_char( l_param2 ) || '); END;' );
  commit;
END a;

Assuming that you don't need procedure B to run in the same session as A, you can schedule the job to run asynchronously, i.e.

CREATE OR REPLACE PROCEDURE a( p_cur_out OUT SYS_REFCURSOR )
AS
  l_jobno pls_integer;
BEGIN
  OPEN p_cur_out 
   FOR SELECT cast( null as varchar2(64) ) empname, 
              cast( null as integer ) empid
         FROM dual;

  dbms_job.submit( l_jobno,
                   'BEGIN B(); END;' );
  commit;
END a;

You'll return from A and a job will be scheduled immediately to run procedure B in a separate session (note that the job won't start until the current session commits which is why I added a commit here-- if your code is going to commit elsewhere, you can eliminate that). All dbms_job.submit is doing is creating the job-- the job will not start executing until after the procedure returns.

If B takes arguments, you'll need to build the PL/SQL block dynamically. So if B takes two NUMBER parameters

CREATE OR REPLACE PROCEDURE a( p_cur_out OUT SYS_REFCURSOR )
AS
  l_jobno  pls_integer;
  l_param1 pls_integer;
  l_param2 pls_integer;
BEGIN
  OPEN p_cur_out 
   FOR SELECT cast( null as varchar2(64) ) empname, 
              cast( null as integer ) empid
         FROM dual;

  dbms_job.submit( l_jobno,
                   'BEGIN B(' || to_char( l_param1 ) || ', ' ||
                                 to_char( l_param2 ) || '); END;' );
  commit;
END a;

回复收藏 0 原文

~没有更多了~