我有一个Oracle程序,其中我想执行以下操作:
目前我使用FOR循环单独处理每个记录。
我想:
这是一个很好的代码,这看起来很简单,只有一个INSERT和UPDATE语句具有相同的WHERE子句。然而,在执行两个语句之间的时间,可能插入更多的需要复制的记录。如果我使用相同的WHERE子句,我可以将记录标记为已处理的记录,但没有实际处理。
有一种方法可以保存主键列表在两个语句中使用还是合并语句?或者你会推荐我坚持使用FOR循环并一次处理一个记录?所有建设性输入欢迎。
解决方案如果在事务中运行 code> ,任何失败都会导致插入和更新被回滚。
insert into tgt .. select * from src 其中< condition> 和row_status<> 'copy'; - 信息记录复制一次后? update src set row_status ='copied'其中< same_where_condition_as_before> commit;如果在读取之后在源表中插入新行,您可能需要运行块,再次修改,如果合适,更改的条件。
I have an Oracle procedure in which I would like to do the following:
Currently, I am using a FOR loop to process each record individually. This works, but it is a lot of code to do something I think could be simpler.
I would like to:
This may seem simple, just one INSERT and an UPDATE statement with the same WHERE clause. However, in the time between execution of the two statements, more records may be inserted that need to be copied. If I use the same WHERE clause, I may mark records as processed that have not actually been processed.
Is there a way I can save a list of the Primary Keys for use in both statements or merge the statements? Or would you recommend I stick with the FOR loop and process the records one at a time? All constructive input welcome.
解决方案If you run the two statements within a transaction, any failures will cause both the insert and the update to be rolled back.
insert into tgt.. select * from src where <condition> and row_status <> 'copied'; --ignoring records after they have been copied once? update src set row_status = 'copied' where <same_where_condition_as_before> commit;If there are new rows inserted in the source table after they have been read, you might need to run the block again, with a changed where condition if appropriate.
更多推荐
复制具有insert select的记录,然后更新同一组记录中的属性
发布评论