在Oracle过程中实现多线程 [英] Implementing Multithreading in Oracle Procedures

问题描述

我正在研究Oracle 10gR2.

这是我的问题-

我有一个过程，应将其称为 * proc_parent * (在包内)，该过程应调用另一个过程，将其称为 * user_creation * .我必须在循环内调用 * user_creation * ，该循环正在从表中读取一些列-这些列值作为参数传递给 * user_creation * 过程. /p>

代码如下:

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 * ，但是我无法弄清楚如何在循环中使用它们.

有人可以指引我正确的方向吗?

谢谢， 安库尔

解决方案

我想结束这个问题.可以在循环内部使用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 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

解决方案

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.

这篇关于在Oracle过程中实现多线程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！