使用远程数据库数据更新本地数据库

发布于 2024-11-27 04:04:11 字数 681 浏览 2 评论 0原文

我在 Oracle 查询方面遇到了一些问题

create or replace
PROCEDURE       "LOAD_USERNAME" 
IS
   cursor usu is
      select userid
      from local_user;

BEGIN
   for usu_rec in usu
   loop
      update loc_user set username =(
         select cod_user 
            from (
               select cod_user, row_number() over (order by date_createad desc) r
               from user_table@DBLINK where cod_person=usu_rec.userid
            )
            where r = 1
         ) 
      where externaluserid=usu_rec.userid;

   end loop;
END;

基本上，尝试从其他数据库获取用户（最后创建的）的代码并更新本地表。这似乎有效，但我花了太多时间。我只能通过DBLINK检查远程数据库。

拜托，我需要一些帮助以获得更好的方法来做到这一点。

我预先感谢您的帮助。

原文

I got a bit of a problem with a Oracle query

create or replace
PROCEDURE       "LOAD_USERNAME" 
IS
   cursor usu is
      select userid
      from local_user;

BEGIN
   for usu_rec in usu
   loop
      update loc_user set username =(
         select cod_user 
            from (
               select cod_user, row_number() over (order by date_createad desc) r
               from user_table@DBLINK where cod_person=usu_rec.userid
            )
            where r = 1
         ) 
      where externaluserid=usu_rec.userid;

   end loop;
END;

Basically, trying to get code of a user(last one created) from other database and update a local table. This seems to work but I takes too much time. I can only check the remote database through a DBLINK.

Please, I want some help for a better way to do this.

I thank your help in advance.

分享到QQ

分享到微博

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

发布评论

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

年少掌心 2024-12-04 04:04:12

您希望尽量减少通过网络的次数。因此，您应该加入驱动光标中的远程表并将用户名拉回那里。这会更好，因为该查询只执行一次（索引/设计将决定它的运行情况）。但您的更新将仅适用于本地数据。

编辑：删除了我的 PL/SQL，因为 @Aitor 的更好

回复收藏 0 原文

记忆消瘦 2024-12-04 04:04:12

正如索德韦德所说，最好将连接放在光标中。您可以尝试这样的操作：

create or replace
PROCEDURE       "LOAD_USERNAME" 
IS
   cursor usu is
select distinct local_user.userid,your_dblink_table.cod_user
      from local_user, user_table@bdlink your_dblink_table
where local_user.userid=your_dblink_table.codperson
and local_user.externaluserid=local_user.userid;
BEGIN
   for usu_rec in usu
   loop
      update loc_user set username =usu_rec.cod_user
where externauserid=usu_rec.userid;
 end loop;
commit;
END;

如果您必须加载大量更新，您可以尝试在游标中使用批量收集/为所有方法。

As Sodved said, is better to had the join in your cursor. You can try something like this:

create or replace
PROCEDURE       "LOAD_USERNAME" 
IS
   cursor usu is
select distinct local_user.userid,your_dblink_table.cod_user
      from local_user, user_table@bdlink your_dblink_table
where local_user.userid=your_dblink_table.codperson
and local_user.externaluserid=local_user.userid;
BEGIN
   for usu_rec in usu
   loop
      update loc_user set username =usu_rec.cod_user
where externauserid=usu_rec.userid;
 end loop;
commit;
END;

If you have to load massive updates, you can try a bulk collect/for all approach in the cursor.

回复收藏 0 原文