Oracle存在则修改，不存在插入记录

最新推荐文章于 2025-04-02 16:14:01 发布

xingchen_jay

最新推荐文章于 2025-04-02 16:14:01 发布

阅读量5.1k

点赞数 1

分类专栏： mybatis

mybatis 专栏收录该内容

1 篇文章

订阅专栏

　　首先是创建表有着细微的区别，判断一个表是否存在，习惯了MS SQL的OBJECT_ID('对象表','对象类型')的童鞋们，你们是不是想到Oracle中也应该有这样的功能呢？遗憾了，Oracle中没有此类函数来判断一个表是否存在，那就只能通过委婉的方式来实现，MS SQL中有类似于 Select Name From SysObjects Where XType='U'这样的数据库表，那对应的Oracle中就有了select * from user_tables，通过查询系统表，判断这个表在数据库中是否存在，如果存在就删除，然后再创建。

declare num number;   
begin
    select count(1) into num from user_tables where table_name='ACCOUNT';   
    if num > 0 then   
      dbms_output.put_line('存在!');
      execute immediate 'drop table ACCOUNT '; 
    end if;   
      execute immediate 'create table Account
                        (
                                AccountID nvarchar2(50) primary key,
                                AccountName nvarchar2(50) 
                        )';  
      dbms_output.put_line('成功创建表!');
end;

与MS SQL创建一个表对比，是不是还是有一些显微的差异呢？答案当然是肯定的。
　　这个演示是前奏，现在来开始我们今天的主题，在Oracle中，表创建成功了，现在我要往这个表中插入数据，如果新插入的数据在表中存在则修改，不存在则插入，我在网上一搜，惊奇的发现Oracle中的exists()函数是判断两个数据集合的交集是否存在，与MS SQL有一定的区别。这样的对比虽然会显的不专业，但是我还是有对比和发表自己观点自由。于是我在网上疯狂的搜索Oracle在这个问题上的解决方案，总结了以下几种方案，以供大家选择：

1：隐式游标法 SQL%NOTFOUND SQL%FOUND

SQL%NOTFOUND 是SQL中的一个隐式游标，在增删查改的时候自动打开，如果有至少有一条记录受影响，都会返回false，这就就巧妙的构思出了第一种解决方案：

begin
update account set AccountName = '修改-a' where AccountID = '5';
IF SQL%NOTFOUND THEN
   insert into account(AccountID,AccountName) values('5','添加-b');
END IF;
end;

先根据唯一ID到数据表中修改一条记录，如果这条记录在表中存在，则修改，并且SQL%NOTFOUND返回false。如果修改的记录不存在，SQL%NOTFOUND返回true，并且执行插入语句。

2：异常法 DUP_VAL_ON_INDEX

当Oracle语句执行时，发生了异常exception进行处理

begin
insert into account(AccountID,AccountName) values('6','添加-b');
exception 
when DUP_VAL_ON_INDEX then begin 
update account set AccountName = '修改-b' where AccountID = '6';
end;
end;

当往表中插入一条数据，因为表中有主键约束，如果插入的数据在表中已经存在，则会抛出异常，在异常抛出后进行修改。

3：虚拟表法 dual

dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。

declare t_count number;
begin
select count(*) into t_count from dual where exists(select 1 from account where AccountID='11');
if t_count< 1 then
  dbms_output.put_line('添加');
  insert into account(AccountID,AccountName) values('11','添加-11');
else
  dbms_output.put_line('修改');
  update account set AccountName = '修改-11' where AccountID = '11';
  end if;
end;

先声明一个变量t_count,表dual表的值赋给t_count，如果这个值小于1，表示记录不存在，进行插入操作，反之，存在就进行修改操作。

4：no_data_found法

先查找要插入的记录是否存在，存在则修改，不存在则插入。具体的实现如下：

declare t_cols number;
begin
select AccountName into t_cols from account where AccountID = '8';
exception 
when no_data_found then begin 
   --dbms_output.put_line('添加');
   insert into account(AccountID,AccountName) values('8','添加-8');
end;
when others then 
  begin
    --dbms_output.put_line('修改');
    update account set AccountName = '修改-8' where AccountID = '8';
end;
end;

5：merge法

先来看一下merge的语法，

MERGE INTO table_name alias1   
USING (table|view|sub_query) alias2  
ON (join condition)   
WHEN MATCHED THEN   
    UPDATE table_name SET col1 = col_val1
WHEN NOT MATCHED THEN   
    INSERT (column_list) VALUES (column_values);

看了merge的语法后，依葫芦画瓢对于我这种抄袭的人来说已经不是什么难事了。

merge into Account t1  
using (select '3' AccountID,'肖文博' AccountName from dual) t2  
on (t1.AccountID = t2.AccountID)  
when matched then  
     update set t1.AccountName = t2.AccountName
when not matched then  
     insert values (t2.AccountID, t2.AccountName);  
commit;