Oracle多表更新及MERGE命令和闪回机制还原数据表

一、多表更新

比如线上有个系统由于某一个模块出现异常,导致系统整体的数据出现了错误,需要你手动改写数据库错误,Oracle update语句更新的值来自另一张表

update语法最基本的格式为

UPDATE TABLE_NAME SET COLUMN1 = VALUE1 WHERE COLUMN2 = VALUE2

下面首先来创建两张表,令表名为GF_CONS_TRAN_TEST  AS A

-- ----------------------------
-- Table structure for GF_CONS_TRAN_TEST
-- ----------------------------
CREATE TABLE GF_CONS_TRAN_TEST (
"ID" VARCHAR2(64 BYTE) NOT NULL ,
"USERNAME" VARCHAR2(255 BYTE) NULL ,
"PASSWORD" VARCHAR2(255 BYTE) NULL ,
"CREATEDATE" DATE NULL ,
"PHONE" VARCHAR2(255 BYTE) NULL
)
LOGGING
NOCOMPRESS
NOCACHE ; -- ----------------------------
-- Records of GF_CONS_TRAN_TEST
-- ----------------------------
INSERT INTO GF_CONS_TRAN_TEST VALUES ('0001', '国网金州新区', '123445', TO_DATE('2019-11-29 10:25:23', 'YYYY-MM-DD HH24:MI:SS'), null);
INSERT INTO GF_CONS_TRAN_TEST VALUES ('0002', '大连供电公司', 'phhs_all', TO_DATE('2019-11-18 10:25:59', 'YYYY-MM-DD HH24:MI:SS'), null);
INSERT INTO GF_CONS_TRAN_TEST VALUES ('0003', '中山供电公司', 'zs_phs', TO_DATE('2019-11-29 10:26:49', 'YYYY-MM-DD HH24:MI:SS'), null); -- ----------------------------
-- Indexes structure for table GF_CONS_TRAN_TEST
-- ---------------------------- -- ----------------------------
-- Checks structure for table GF_CONS_TRAN_TEST
-- ----------------------------
ALTER TABLE GF_CONS_TRAN_TEST ADD CHECK ("ID" IS NOT NULL); -- ----------------------------
-- Primary Key structure for table GF_CONS_TRAN_TEST
-- ----------------------------
ALTER TABLE GF_CONS_TRAN_TEST ADD PRIMARY KEY ("ID");

A表结构及数据如下:

Oracle多表更新及MERGE命令和闪回机制还原数据表

再创建一张和A表结构类似的表B ,GF_CONS_TRAN_TEST_COPY  AS B

-- ----------------------------
-- Table structure for GF_CONS_TRAN_TEST_COPY
-- ----------------------------
CREATE TABLE GF_CONS_TRAN_TEST_COPY (
"ID" VARCHAR2(64 BYTE) NOT NULL ,
"USERNAME" VARCHAR2(255 BYTE) NULL ,
"PASSWORD" VARCHAR2(255 BYTE) NULL ,
"CREATEDATE" DATE NULL
)
LOGGING
NOCOMPRESS
NOCACHE ; -- ----------------------------
-- Records of GF_CONS_TRAN_TEST_COPY
-- ----------------------------
INSERT INTO GF_CONS_TRAN_TEST_COPY VALUES ('0001', '国网金州新区_更新', '123445', null);
INSERT INTO GF_CONS_TRAN_TEST_COPY VALUES ('0003', '中山供电公司_更新', 'zs_phs', null); -- ----------------------------
-- Indexes structure for table GF_CONS_TRAN_TEST_COPY
-- ---------------------------- -- ----------------------------
-- Checks structure for table GF_CONS_TRAN_TEST_COPY
-- ----------------------------
ALTER TABLE "USER_PSSC"."GF_CONS_TRAN_TEST_COPY" ADD CHECK ("ID" IS NOT NULL); -- ----------------------------
-- Primary Key structure for table GF_CONS_TRAN_TEST_COPY
-- ----------------------------
ALTER TABLE GF_CONS_TRAN_TEST_COPY ADD PRIMARY KEY ("ID");

B表结构及数据如下

Oracle多表更新及MERGE命令和闪回机制还原数据表

可以看到第一张表(GF_CONS_TRAN_TEST AS A)中有三条数据,第二张表(GF_CONS_TRAN_TEST_COPY AS B)有两条数据,都有相同的字段ID,(要不然没有更新条件),假设这里需要更新A表中的USENAME列为表B中的数据,因为有唯一主键ID做索引,我们可以这样写

UPDATE GF_CONS_TRAN_TEST A SET A.USERNAME = (
SELECT B.USERNAME FROM GF_CONS_TRAN_TEST_COPY B WHERE A.ID = B.ID
)

Oracle多表更新及MERGE命令和闪回机制还原数据表

结果显示更新成功,但是受影响的行是3行,我们再看下表A更新后数据

Oracle多表更新及MERGE命令和闪回机制还原数据表

由于表B中没有记录0002,因为用刚才的语句 WHERE A.ID = B.ID所以将表A的数据全部给更新了,表B中没有的显示为空了,所以说把之前的数据整没了,但是老板可没这么说啊,

幸好,Oracle数据库在我们执行插入、更新、删除等操作时,容易产生误操作,导致数据库其他的内容被修改,通过普通的sql语句操作无法还原,可采用Oracle数据库表的闪回机制,根据时间戳,

二、还原表

1.先查询这个时间点的数据是否为要还原的数据

SELECT
*
FROM
TABLENAME AS OF TIMESTAMP TO_TIMESTAMP (
'2019-07-05 08:50:00',
'YYYY-MM-DD HH24:MI:SS'
) --表某个时间段的数据

Oracle多表更新及MERGE命令和闪回机制还原数据表

可以看到数据为我们更新之前的原始数据

2.开启这张表的闪回机制

ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT

3.把表的状态闪回到这个时间段

FLASHBACK TABLE TABLE_NAME  TO TIMESTAMP TO_TIMESTAMP('2019-11-29 11:00:00','YYYY-MM-DD HH24:MI:SS')

4.关闭这张表的闪回状态(切勿忘记执行该步骤)

ALTER TABLE TABLE_NAME DISABLE ROW MOVEMENT

执行完毕会发现表A的数据已经还原到这个时间之前的状态了,因为之前写的update语句会更新全部的记录,所以我们加入限制条件,当B表的记录主键ID在表A中存在时开始更新,SQL语句如下:

UPDATE GF_CONS_TRAN_TEST A SET A.USERNAME = (SELECT B.USERNAME FROM GF_CONS_TRAN_TEST_COPY B WHERE A.ID = B.ID)
WHERE EXISTS (SELECT 1 FROM GF_CONS_TRAN_TEST_COPY B WHERE B.ID = A.ID)

Oracle多表更新及MERGE命令和闪回机制还原数据表

我们可以看到这回更新了两条记录,与我们所想完全一致,回头查看表A的数据

Oracle多表更新及MERGE命令和闪回机制还原数据表

这两条数据为我们刚才所更新的数据,并且0002数据没有变化,符合当初的要求

三、MERGE命令

在oracle9g引入了MERGE命令,可以在一个sql语句中对一个表同时进行insert和update操作,merge命令可以从一个或多个数据源中选择行来update或者insert到一个表或多个表。

Merge into的作用就是解决B表跟新A表数据,如果A表没有,则把B表数据插入A表或向一个表中插入数据,如果该表有该数据则更新,如果没有则插入。

一般我们执行插入语句会是这样

INSERT INTO GF_CONS_TRAN_TEST (ID, USERNAME, PASSWORD) VALUES('0004','国网庄河供电','zh_org')

当然对于主键ID来说常用的两种方式就是UUID和自增长了,很少会出现主键重复的问题,但是假如在插入数据的时候根据主键4判断,如果没有插入数据,如果有则根据ID来更新,岂不一举两得

为了可以看到效果,我们手动更新A表数据如下

Oracle多表更新及MERGE命令和闪回机制还原数据表

B表数据不变

Oracle多表更新及MERGE命令和闪回机制还原数据表

B表中有0001在A表中没有,0003在A表中存在,0002在B表中没有,如果没有出错应该是更新0003,插入0001,SQL执行如下:

MERGE INTO GF_CONS_TRAN_TEST A USING (SELECT B.ID, B.USERNAME, B.PASSWORD FROM GF_CONS_TRAN_TEST_COPY B ) C ON (A.ID = C.ID)
WHEN MATCHED THEN
UPDATE SET A.USERNAME = C.USERNAME, A.PASSWORD = C.PASSWORD
WHEN NOT MATCHED THEN
INSERT (A.ID, A.USERNAME, A.PASSWORD) VALUES(C.ID, C.USERNAME, C.PASSWORD)

执行结构如下:

Oracle多表更新及MERGE命令和闪回机制还原数据表

Oracle多表更新及MERGE命令和闪回机制还原数据表

与我们所想一致,一条为更新操作,一条为新增操作。

上一篇:Cobbler安装CentOS7系统时报错 curl:(7)Failed connect to 10.0.0.201:80;Connection refused


下一篇:第一次阅读作业!