Oracle开发者中级第8课(Merge)实验

wuchangjian2021-11-16 23:48:11编程学习

概述

本实验参考DevGym中的实验指南。

创建环境

create table bricks_for_sale (
  colour   varchar2(10),
  shape    varchar2(10),
  price    number(10, 2),
  primary key ( colour, shape )
);

create table purchased_bricks (
  colour   varchar2(10),
  shape    varchar2(10),
  price    number(10, 2),
  primary key ( colour, shape )
);

insert into bricks_for_sale values ( 'red', 'cube', 4.95 );
insert into bricks_for_sale values ( 'blue', 'cube', 7.75 );
insert into bricks_for_sale values ( 'blue', 'pyramid', 9.99 );

commit;

数据如下:

SQL> select * from bricks_for_sale;

   COLOUR      SHAPE    PRICE
_________ __________ ________
red       cube           4.95
blue      cube           7.75
blue      pyramid        9.99

SQL> select * from purchased_bricks;

no rows selected

Insert-then-update 或 Update-then-insert

向表中添加行时,有时需要执行“不存在就插入,存在就更新”的逻辑,而不想写单独的insert和update语句,这就是upsert。

例如,以下PL/SQL采用先更新后插入的方式实现了upsert,关键点是用sql%rowcount是否为0来判断更新是否成功。这里有一个前提,就是表有相应的主键,否则update可能修改多行:

declare
  l_colour varchar2(10) := 'blue';
  l_shape  varchar2(10) := 'pyramid';
  l_price  number(10, 2) := 9.99;
begin

  update purchased_bricks pb
  set    pb.price = l_price
  where  pb.colour = l_colour
  and    pb.shape = l_shape;

  if sql%rowcount = 0 then
  
    insert into purchased_bricks
    values ( l_colour, l_shape, l_price );
  
  end if;
  
end;
/

select * from purchased_bricks;

   COLOUR      SHAPE    PRICE
_________ __________ ________
blue      pyramid        9.99

另一种实现upsert的方式是先插入再更新,关键点是判断insert是否会引起重复索引:

declare
  l_colour varchar2(10) := 'blue';
  l_shape  varchar2(10) := 'pyramid';
  l_price  number(10, 2) := 15.49;
begin

  insert into purchased_bricks
  values ( l_colour, l_shape, l_price );
  
exception
  when DUP_VAL_ON_INDEX then
  
    update purchased_bricks pb
    set    pb.price = l_price
    where  pb.colour = l_colour
    and    pb.shape = l_shape;
    
end;
/

select * from purchased_bricks;

commit;

取决于数据,两种方法都有可能有多余的操作,更好的方法是用Merge。

Merging New Values

Merge 是一种允许您根据需要执行插入或更新的语句。 要使用它,您需要在join子句中说明目标表中的值与源表中的值之间的关系。 然后在 when notmatched 子句中插入行。 并在when matched时更新它们。

目标表是您将添加或更改其行的表。 您将源数据合并到此。源数据必须是表,查询返回的表也行。

Merge示例如下,包含了上面所说的几个元素。源表时用查询产生的,join子句的联结键恰好是主键,以判断其唯一性。总之还比较容易理解:

merge into purchased_bricks pb
using ( 
  select 'blue' colour, 'cube' shape, 15.95 price 
  from   dual 
) bfs
on    ( pb.colour = bfs.colour and pb.shape = bfs.shape )
when not matched then
  insert ( pb.colour, pb.shape, pb.price )
  values ( bfs.colour, bfs.shape, bfs.price )
when matched then
  update set pb.price = bfs.price;
  
select * from purchased_bricks;

   COLOUR      SHAPE    PRICE
_________ __________ ________
blue      pyramid       15.49
blue      cube          15.95

Merging Two Tables

如果源表不是一个查询,那么两个表的合并也很简单:

merge into purchased_bricks pb
using bricks_for_sale bfs
on    ( pb.colour = bfs.colour and pb.shape = bfs.shape )
when not matched then
  insert ( pb.colour, pb.shape, pb.price )
  values ( bfs.colour, bfs.shape, bfs.price )
when matched then
  update set pb.price = bfs.price;
  
select * from purchased_bricks;

commit;

   COLOUR      SHAPE    PRICE
_________ __________ ________
blue      pyramid        9.99
blue      cube           7.75
red       cube           4.95

下面是不用upsert的实现方式,要复杂很多,不过逻辑也还清楚:

update purchased_bricks pb
set    pb.price = (
  select bfs.price
  from   bricks_for_sale bfs
  where  pb.colour = bfs.colour 
  and    pb.shape = bfs.shape
)
where  exists (
  select null
  from   bricks_for_sale bfs
  where  pb.colour = bfs.colour 
  and    pb.shape = bfs.shape
);

insert into purchased_bricks ( 
  colour, shape, price
) 
  select bfs.colour, bfs.shape, bfs.price 
  from   bricks_for_sale bfs
  where  not exists (
    select null
    from   purchased_bricks pb
    where  pb.colour = bfs.colour 
    and    pb.shape = bfs.shape
  );

select * from purchased_bricks;

rollback;

Merge Restrictions

Merge也有限制,你只能修改:

  • 不在join子句中的列
  • 每行一次

如果修改在join子句中的列,报错如下:

merge into purchased_bricks pb
using bricks_for_sale bfs
on    ( pb.colour = bfs.colour and pb.shape = bfs.shape )
when not matched then
  insert ( pb.colour, pb.shape, pb.price )
  values ( bfs.colour, bfs.shape, bfs.price )
when matched then
  update set pb.colour = bfs.colour, pb.shape = bfs.shape;

Error at Command Line : 3 Column : 9
Error report -
SQL Error: ORA-38104: Columns referenced in the ON Clause cannot be updated: "PB"."COLOUR"

如果1行被修改多次(因为join条件变了),报错如下:

merge into purchased_bricks pb
using bricks_for_sale bfs
on    ( pb.colour = bfs.colour )
when not matched then
  insert ( pb.colour, pb.shape, pb.price )
  values ( bfs.colour, bfs.shape, bfs.price )
when matched then
  update set pb.price = bfs.price;

Error report -
ORA-30926: unable to get a stable set of rows in the source tables

Conditional Merging

如果想做有条件的更新和插入,在matched子句中加where条件即可。

例如,以下SQL只upsert颜色为blue的行:

update bricks_for_sale 
set    price = 100;

insert into bricks_for_sale values ( 'red', 'pyramid', 5.99 );

merge into purchased_bricks pb
using bricks_for_sale bfs
on    ( pb.colour = bfs.colour and pb.shape = bfs.shape )
when not matched then
  insert ( pb.colour, pb.shape, pb.price )
  values ( bfs.colour, bfs.shape, bfs.price )
  where  bfs.colour = 'blue'
when matched then
  update set pb.price = bfs.price
  where  bfs.colour = 'blue';
  
select * from purchased_bricks;

rollback;

   COLOUR      SHAPE    PRICE
_________ __________ ________
blue      pyramid         100
blue      cube            100
red       cube           4.95

在merge前,两表的数据为:

SQL> select * from bricks_for_sale;

   COLOUR      SHAPE    PRICE
_________ __________ ________
red       cube            100
blue      cube            100
blue      pyramid         100
red       pyramid        5.99

SQL> select * from purchased_bricks;

   COLOUR      SHAPE    PRICE
_________ __________ ________
blue      pyramid        9.99
blue      cube           7.75
red       cube           4.95

Single Operation Merge

merge 中的 when matched和 when not matched子句都是可选的。 因此,您可以实现仅插入或仅更新的merge。例如:

-- 仅插入的merge
merge into purchased_bricks pb
using bricks_for_sale bfs
on    ( pb.colour = bfs.colour and pb.shape = bfs.shape )
when not matched then
  insert ( pb.colour, pb.shape, pb.price )
  values ( bfs.colour, bfs.shape, bfs.price );

-- 仅更新的merge
merge into purchased_bricks pb
using bricks_for_sale bfs
on    ( pb.colour = bfs.colour and pb.shape = bfs.shape )
when matched then
  update set pb.price = bfs.price;
  
select * from purchased_bricks;

rollback;

如果不用merge,也可以使用以下SQL实现仅更新的合并。但确定是需要访问bricks_for_sale表两次,而merge语句只需1次:

update purchased_bricks pb
set    pb.price = (
  select bfs.price
  from   bricks_for_sale bfs
  where  pb.colour = bfs.colour 
  and    pb.shape = bfs.shape
)
where  exists (
  select null
  from   bricks_for_sale bfs
  where  pb.colour = bfs.colour 
  and    pb.shape = bfs.shape
);

Merge + Delete

您还可以使用合并从目标表中删除行,这只会发生在目标表中的行在源表中具有匹配行时。
为此,请在when matched子句中的更新后添加删除子句。例如:

when matched then
  update set pb.price = bfs.price
  delete where pb.colour = 'blue'

删除使用更新后目标表中的值,也就是说,只会影响现有的行,merge操作中新增的行不受影响。

例如,在合并前的数据如下:

insert into bricks_for_sale values ( 'blue', 'cuboid', 5.99 );

select * from purchased_bricks;

   COLOUR      SHAPE    PRICE
_________ __________ ________
blue      pyramid         100
blue      cube            100
red       cube           4.95

select * from bricks_for_sale;

   COLOUR      SHAPE    PRICE
_________ __________ ________
red       cube            100
blue      cube            100
blue      pyramid         100
red       pyramid        5.99
blue      cuboid         5.99

使用以下SQL合并:

merge into purchased_bricks pb
using bricks_for_sale bfs
on    ( pb.colour = bfs.colour and pb.shape = bfs.shape )
when not matched then
  insert ( pb.colour, pb.shape, pb.price )
  values ( bfs.colour, bfs.shape, bfs.price )
when matched then
  update set pb.price = bfs.price
  delete where pb.colour = 'blue' ;
  
select * from purchased_bricks;

   COLOUR      SHAPE    PRICE
_________ __________ ________
red       cube            100
red       pyramid        5.99
blue      cuboid         5.99

rollback;

环境清理

drop table purchased_bricks;
drop table bricks_for_sale;
返回列表

上一篇:数据库相关

下一篇:Session

相关文章

划重点|iOS15正式发布, 全新的通知推送系统,你必须要知道

划重点|iOS15正式发布, 全新的通知推送系统,你必须要知道

简介: 今年友盟+联合达摩院决策智能实验室讲算法技术,...

Jira 破解版 Docker 部署

> [关注我的个人BLOG,查看更详细的文章信息](http://z...

山东最水利水电安全员模拟真题集及答案解析

百分百题库提供水利水电安全员考试试题、水利水电安全员考试预测题、水利水电安全员考试真题、...

水果店不新鲜水果处理,水果店卖不出去的水果怎么处理

水果店不新鲜水果处理,水果店卖不出去的水果怎么处理

1、增加销售方法剩的就少 多吸粉加上顾客,能提高水果的宣传。可以建顾客群...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。