Oracle 更新以最安全的方式将一行拆分为两部分并带有回滚 [英] Oracle update to split a row into two in safest way with rollback
问题描述
我正在处理一个包含类似于以下数据的现有表:
I'm working on an existing table that has data similar to:
身份证 |姓名 |旧值 |新价值
ID | Name | OldValue | NewValue
这些列的数据类似于:
14 |姓名 |'x=>y/zs/z
旧值和新值显示值之间的变化.
14 | name | 'x=>y / zs / z
where the old and new values show a change between values.
我想做的是拆分这些，这样结果就是:
What I'd like to do is split these so that this is the result:
15 |姓名 |'x=>y' |'x=>s'16 |姓名 |'z
我需要将这一行安全地拆分为两个单独的行，然后删除现有数据，以确保我能够在任何更改受到影响时回滚.
15 | name | 'x=>y' | 'x=>s' 16 | name | 'z
I need to safely split this row into two separate rows and then delete the existing data making sure that I'm able to roll back any changes should it hit the fan.
我的想法是我也许应该将所有值放入一个临时表中，其中子句与我需要的内容相匹配.然后使用此表插入新行，之后删除现有行和临时表应该是安全的.
我不确定这是否正确，所以我正在寻找一些建议.
My thoughts are that I should perhaps put all the values into a temporary table where the clauses match what I need. Then use this table to insert the new rows, after which it should be safe to remove the existing rows and the temporary table.
I'm not sure if this is correct and so I was looking for some advice.
推荐答案
在单个事务中执行INSERT和DELETE.如果您发现任何问题，您可以随时回滚交易.
Do the INSERT and DELETE in single transaction. You can always ROLLBACK the transaction if you see any issues.
要拆分行看看:
例如
设置
SQL> CREATE TABLE t(str VARCHAR2(100));
Table created.
SQL> INSERT INTO t VALUES ('ID - Name - OldValue - NewValue');
1 row created.
SQL> COMMIT;
Commit complete.
表格数据
SQL> SELECT * FROM t;
STR
-------------------------------
ID - Name - OldValue - NewValue
INSERT 在 分割行并删除旧行
SQL> BEGIN
2 INSERT INTO t
3 SELECT trim(regexp_substr(str, '[^-]+', 1, LEVEL)) str
4 FROM t
5 CONNECT BY LEVEL <= regexp_count(str, '-')+1;
6
7 DELETE FROM t WHERE instr(str, '-') > 0;
8 END;
9 /
PL/SQL procedure successfully completed.
查看表格数据
SQL> SELECT * FROM t;
STR
---------------------
ID
Name
OldValue
NewValue
看起来不错.现在在拆分分隔字符串后插入行，然后删除旧行.
Seems good. The rows are now inserted after splitting the delimited string, and then deleted the old row.
ROLLBACK并检查表数据
SQL> ROLLBACK;
Rollback complete.
SQL> SELECT * FROM t;
STR
-------------------------------
ID - Name - OldValue - NewValue
所以，你回到了同一行.
So, you are back to the same row.
这篇关于Oracle 更新以最安全的方式将一行拆分为两部分并带有回滚的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
