在 Oracle 过程中实现多线程

发布于 2024-12-17 11:48:08 字数 891 浏览 1 评论 0原文

我正在研究 Oracle 10gR2。

这是我的问题 -

我有一个过程，我们称之为 *proc_parent* （在包内），它应该调用另一个过程，我们称之为 *user_creation* 。我必须在循环内调用 *user_creation*，该循环从表中读取一些列 - 并且这些列值作为参数传递给 *user_creation* 过程。

代码如下：

FOR i IN (SELECT    community_id,
                        password,
                        username 
               FROM     customer 
               WHERE    community_id IS NOT NULL 
               AND      created_by = 'SRC_GLOB'
              )
     LOOP
        user_creation (i.community_id,i.password,i.username);
     END LOOP;

COMMIT;

user_Creation 过程调用 Web 服务来执行某些业务逻辑，然后根据响应更新表。

我需要找到一种可以在此处使用多线程的方法，以便我可以运行此过程的多个实例来加快速度。我知道我可以使用 *DBMS_SCHEDULER* ，也可能使用 *DBMS_ALERT* 但我无法弄清楚如何在循环中使用它们。

有人可以引导我走向正确的方向吗？

谢谢，安库尔

原文

I am working on Oracle 10gR2.

And here is my problem -

I have a procedure, lets call it *proc_parent* (inside a package) which is supposed to call another procedure, lets call it *user_creation*. I have to call *user_creation* inside a loop, which is reading some columns from a table - and these column values are passed as parameters to the *user_creation* procedure.

The code is like this:

FOR i IN (SELECT    community_id,
                        password,
                        username 
               FROM     customer 
               WHERE    community_id IS NOT NULL 
               AND      created_by = 'SRC_GLOB'
              )
     LOOP
        user_creation (i.community_id,i.password,i.username);
     END LOOP;

COMMIT;

user_Creation procedure is invoking a web service for some business logic, and then based on the response updates a table.

I need to find a way by which I can use multi-threading here, so that I can run multiple instances of this procedure to speed up things. I know I can use *DBMS_SCHEDULER* and probably *DBMS_ALERT* but I am not able to figure out, how to use them inside a loop.

Can someone guide me in the right direction?

Thanks,
Ankur

分享到QQ

分享到微博

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

发布评论

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

我纯我任性 2024-12-24 11:48:08

你能做的就是同时提交很多工作。请参见示例 28-2 在单个事务

这会用您想要在一次交易中同时提交的所有作业填充 pl/sql 表。一旦它们被提交（启用），它们就会开始运行，数量是系统可以处理的数量，或者是资源管理器计划允许的数量。

轻量级作业的开销非常……最小/轻。

回复收藏 0 原文

又怨 2024-12-24 11:48:08

我想结束这个问题。 DBMS_SCHEDULER 以及 DBMS_JOB（尽管 DBMS_SCHEDULER 是首选）可以在循环内使用来提交和执行作业。

例如，下面是一个示例代码，使用可以在循环内调用的 DBMS_JOB：

...
FOR i IN (SELECT community_id,
                 password,
                 username
          FROM   customer
          WHERE  community_id IS NOT NULL
          AND    created_by = 'SRC_GLOB'
         )
LOOP
DBMS_JOB.SUBMIT(JOB => jobnum,
                WHAT => 'BEGIN user_creation (i.community_id,i.password,i.username); END;'      
COMMIT;
END LOOP;

在 SUBMIT 之后使用提交将并行启动作业（以及过程）。

I would like to close this question. DBMS_SCHEDULER as well as DBMS_JOB (though DBMS_SCHEDULER is preferred) can be used inside the loop to submit and execute the job.

For instance, here's a sample code, using DBMS_JOB which can be invoked inside a loop:

...
FOR i IN (SELECT community_id,
                 password,
                 username
          FROM   customer
          WHERE  community_id IS NOT NULL
          AND    created_by = 'SRC_GLOB'
         )
LOOP
DBMS_JOB.SUBMIT(JOB => jobnum,
                WHAT => 'BEGIN user_creation (i.community_id,i.password,i.username); END;'      
COMMIT;
END LOOP;

Using a commit after SUBMIT will kick off the job (and hence the procedure) in parallel.

回复收藏 0 原文

~没有更多了~