在pl \ sql中自动执行大量更新查询 [英] Automate bulk of update queries in pl\sql

问题描述

对于频繁的一段时间，我正在执行相同的过程，以连续的值更新几个表.希望在下面做一个简单的例子，

UPDATE   Table_1 SET   vchar_val = REPLACE (vchar_val, '.360/', '.370/'),
      WHERE   vchar_val LIKE 'http://services%.360/%'
     AND c_version IN ('ALL', 'N/A', '37.0'); 

对于第37版，我将"36"的位置替换为"37".我将继续为'38'做同样的事情，并且持续下去...这让我感到无聊又耗时，因为我已经为不同的表添加了50条这样的记录，而我要手动编辑所有更新查询.

因此，我计划编写一个调度程序，可以通过输入作为先前版本和当前版本的输入来触发每个版本，在其中我将放置所有这些更新查询.

这里是我触手可及的地方，如果我通过输入版本值作为输入，就应该引入要存储的本地参数.我如何为我的更新脚本分配本地变量.

我将

之类的文本连接起来

  REPLACE (vchar_val, '.'+ @PrevVersion  +'/', '.'+ @CurrentVersion  +'/')

PrevVer **& CurrentVer **是我的局部变量，其值是.360& .370响应.

我想我在此代码段中错过了一段安静的代码，因为我在运行此代码时会出错.

请帮助我重新排列此代码以自动执行查询或您的注释，以任何其他方式完成此工作...

谢谢

-VIno

解决方案

begin 
  for i in 36 .. 50 loop
    UPDATE   Table_1 
    SET vchar_val = REPLACE (vchar_val, '.'|| i ||'0/', '.'|| i+1 ||'0/')
    WHERE vchar_val LIKE 'http://services%.'|| i ||'0/%'
    AND c_version IN ('ALL', 'N/A', i+1 ||'.0'); 
  end loop;
end;

当然，您可以使用一些精美的reg_exp在一次更新中完成此操作，但是我将那个练习留给另一个同伴stackoverflower:)

For frequent period of time, i'm doing same process of updating few tables with consecutive values. Hope to make this simple an example below,

UPDATE   Table_1 SET   vchar_val = REPLACE (vchar_val, '.360/', '.370/'),
      WHERE   vchar_val LIKE 'http://services%.360/%'
     AND c_version IN ('ALL', 'N/A', '37.0'); 

For 37th version, i'm replacing the places where '36' with '37'. The same i'll do for '38' and it continues...This is making me bore and time consuming process as i've 50 plus records like this for different tables for which i'm manually editing all update queries.

So i planned to write a scheduler which i can trigger for each version by giving input as previous version and current version, in which i'll put all this update queries.

Here comes the place where i struck, if i go by giving version values as input, i'm supposed to introduce local parameter to store. HOW CAN I ASSIGN THAT LOCAL VARIABLE TO MY UPDATE SCRIPT.??????

I go with concatenate the texts like

  REPLACE (vchar_val, '.'+ @PrevVersion  +'/', '.'+ @CurrentVersion  +'/')

PrevVer** & CurrentVer** is my local variable with values .360 & .370 resp.

I think i miss quiet piece of code in this snippet as i'm getting error when running this.

Please help me guys to rearrange this code to automate the query or ur comments to do this job in any alternative way...

Thanks

-VIno

解决方案

begin 
  for i in 36 .. 50 loop
    UPDATE   Table_1 
    SET vchar_val = REPLACE (vchar_val, '.'|| i ||'0/', '.'|| i+1 ||'0/')
    WHERE vchar_val LIKE 'http://services%.'|| i ||'0/%'
    AND c_version IN ('ALL', 'N/A', i+1 ||'.0'); 
  end loop;
end;

Of course you could do that in one single update with some fancy reg_exp, but I leave that exercice to another fellow stackoverflower :)

这篇关于在pl \ sql中自动执行大量更新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！