特性说明
在Oracle模式下,OceanBase临时表已支持基本的create、select、insert、delete、update等功能,然而,有较多客户还需要merge into、insert all临时表的功能。merge into的作用是将源表中的数据行以更新或插入的方式合并到目标表中,insert all则可向多个目标表插入数据。
在OceanBase 4.2.2版本中,OceanBase对merge into、insert all临时表的功能进行了支持,同时还支持了insert、update、delete包含临时表的视图。下文详细介绍这三种使用场景。
使用场景
merge into临时表
临时表可作为merge into的目标表、源表,若merge into的目标表、源表是视图,视图中也可包含临时表。
下面对功能进行举例说明。
首先创建临时表temp1和非临时表t1。
create global temporary table temp1(c1 int,c2 int) on commit preserve rows;
create table t1(c1 int, c2 int);
然后向temp1和t1中插入一些数据。
insert into temp1 values(1,1),(2,2),(5,5);
insert into t1 values(1,2),(2,4),(3,6);
使用t1作为源表,temp1作为目标表,做merge into操作,将t1表中的数据和temp1表中的数据进行匹配,对匹配成功的temp1表数据进行更新,将匹配不成功的t1表数据插入temp1表中。
merge into temp1 using t1 on (temp1.c1=t1.c1)
when matched then update set temp1.c2=t1.c2
when not matched then insert (c1,c2) values (t1.c1,t1.c2);
最后查看merge into临时表的结果,如下。
select * from temp1;
+------+------+
| C1 | C2 |
+------+------+
| 1 | 2 |
| 2 | 4 |
| 5 | 5 |
| 3 | 6 |
+------+------+
insert all临时表
临时表可作为insert all的目标表、源表。
首先创建临时表temp1和非临时表t1, t2。
create global temporary table temp1(c1 int,c2 int) on commit preserve rows;
create table t1(c1 int, c2 int);
create table t2(c1 int, c2 int);
然后向temp1, t1, t2中插入一些数据。
insert into temp1 values(1,1),(2,2);
insert into t1 values(1,2),(2,4);
insert into t2 values(3,5);
使用t2作为源表,temp1和t1作为目标表,做insert all操作,将t2表中的数据插入temp1和t1表中。
insert all
into temp1 (c1, c2) values (v1, v2)
into t1 (c1, c2) values (v1, v2)
select c1 as v1, c2 as v2 from t2;
最后查看insert all临时表的结果,如下。
select * from temp1;
+------+------+
| C1 | C2 |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 5 |
+------+------+
select * from t1;
+------+------+
| C1 | C2 |
+------+------+
| 1 | 2 |
| 2 | 4 |
| 3 | 5 |
+------+------+
insert、update、delete包含临时表的视图
可以对包含临时表的视图进行insert、update、delete操作。
首先创建临时表temp1和包含临时表的视图v1。
create global temporary table temp1(c1 int,c2 int) on commit preserve rows;
create view v1 as select * from temp1;
对视图进行insert操作。
insert into v1 values (3,4);
insert into v1 values (4,5);
查看insert的结果,如下。
select * from temp1;
+------+------+
| C1 | C2 |
+------+------+
| 3 | 4 |
| 4 | 5 |
+------+------+
select * from v1;
+------+------+
| C1 | C2 |
+------+------+
| 3 | 4 |
| 4 | 5 |
+------+------+
对视图进行update操作。
update v1 set c1 = 5 where c2 = 4;
查看update的结果,如下。
select * from temp1;
+------+------+
| C1 | C2 |
+------+------+
| 5 | 4 |
| 4 | 5 |
+------+------+
select * from v1;
+------+------+
| C1 | C2 |
+------+------+
| 5 | 4 |
| 4 | 5 |
+------+------+
对视图进行delete操作。
delete from v1 where c1 = 4;
查看delete的结果,如下。
select * from temp1;
+------+------+
| C1 | C2 |
+------+------+
| 5 | 4 |
+------+------+
select * from v1;
+------+------+
| C1 | C2 |
+------+------+
| 5 | 4 |
+------+------+