当前位置： 文江博客话题详情

重复的插入到表值中发生，跳过该特定插入而无需错误，然后进行下一个插入语句

发布于 2025-02-01 09:55:06 字数 839 浏览 2 评论 0原文

我有一个名为table1的表，有2列（否，名称），

 CREATE TABLE TABLE1( NO NUMBER(3) NOT NULL, NAME VARCHAR2(5) NOT NULL);
 
 CREATE UNIQUE INDEX TX ON TABLE1(NO,NAME);

我试图插入值如下所述：

INSERT INTO TABLE1 VALUES(101, 'JOHN');
INSERT INTO TABLE1 VALUES(102, 'MORN'); 
INSERT INTO TABLE1 VALUES(103, 'TONY');
INSERT INTO TABLE1 VALUES(103, 'TONY');
INSERT INTO TABLE1 VALUES(103, 'RONY');

如上所述，我给了一个插入语句两次。实际上，上面的插入语句工作正常，但是当它到达第四个插入语句时，它是抛出的错误。我正在寻找插入值对值语句的插入，如果重复插入语句出现，它应该跳过该特定的插入语句而不显示错误并进行下一个插入语句。

我尝试使用以下语句，但它在Oracle SQL中不起作用

INSERT IGNORE INTO TABLE1 VALUES(101, 'JOHN');
INSERT IGNORE INTO TABLE1 VALUES(102, 'MORN'); 
INSERT IGNORE INTO TABLE1 VALUES(103, 'TONY');
INSERT IGNORE INTO TABLE1 VALUES(103, 'TONY');
INSERT IGNORE INTO TABLE1 VALUES(103, 'RONY');
原文

I have a table named TABLE1 having 2 columns (NO, NAME)

 CREATE TABLE TABLE1( NO NUMBER(3) NOT NULL, NAME VARCHAR2(5) NOT NULL);
 
 CREATE UNIQUE INDEX TX ON TABLE1(NO,NAME);

I am trying to insert values as mentioned below:

INSERT INTO TABLE1 VALUES(101, 'JOHN');
INSERT INTO TABLE1 VALUES(102, 'MORN'); 
INSERT INTO TABLE1 VALUES(103, 'TONY');
INSERT INTO TABLE1 VALUES(103, 'TONY');
INSERT INTO TABLE1 VALUES(103, 'RONY');

As you see above, I have given one insert statement twice. Actually, above insert statements are working fine but when it comes 4th insert statement, it is throwing error which is expected. I am looking for INSERT INTO VALUES statement if duplicate insert statement comes it should skip that particular insert statement without showing error and go for next INSERT statement.

I tried using the below statements but its not working in Oracle SQL

INSERT IGNORE INTO TABLE1 VALUES(101, 'JOHN');
INSERT IGNORE INTO TABLE1 VALUES(102, 'MORN'); 
INSERT IGNORE INTO TABLE1 VALUES(103, 'TONY');
INSERT IGNORE INTO TABLE1 VALUES(103, 'TONY');
INSERT IGNORE INTO TABLE1 VALUES(103, 'RONY');

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（4）

日久见人心 2025-02-08 09:55:06

您可以使用错误日志和重定向错误：

begin
  dbms_errlog.create_error_log (
    dml_table_name     => 'TABLE1'
  );
end;
/

然后：

INSERT INTO TABLE1 VALUES(101, 'JOHN') LOG ERRORS REJECT LIMIT UNLIMITED;
INSERT INTO TABLE1 VALUES(102, 'MORN') LOG ERRORS REJECT LIMIT UNLIMITED;
INSERT INTO TABLE1 VALUES(103, 'TONY') LOG ERRORS REJECT LIMIT UNLIMITED;
INSERT INTO TABLE1 VALUES(103, 'TONY') LOG ERRORS REJECT LIMIT UNLIMITED;
INSERT INTO TABLE1 VALUES(103, 'RONY') LOG ERRORS REJECT LIMIT UNLIMITED;

然后：

SELECT * FROM table1;

输出：

noname
101john
102morn
103rony
103tony

和：

SELECT * FROM ERR$_TABLE1;

输出：

ora_err_number $ora_err_mesg $ora_err_rowid $ora_err_optyp $ora_err_tag $noname
1ora-00001：唯一约束（fiddle_taxeggnaztfzwunuzxol.tx）违反了＆lt; br＆gt;nullinull103tony

db＆lt; “ rel =“ nofollow noreferrer”>此处

You can use an error log and redirect errors to that:

begin
  dbms_errlog.create_error_log (
    dml_table_name     => 'TABLE1'
  );
end;
/

Then:

INSERT INTO TABLE1 VALUES(101, 'JOHN') LOG ERRORS REJECT LIMIT UNLIMITED;
INSERT INTO TABLE1 VALUES(102, 'MORN') LOG ERRORS REJECT LIMIT UNLIMITED;
INSERT INTO TABLE1 VALUES(103, 'TONY') LOG ERRORS REJECT LIMIT UNLIMITED;
INSERT INTO TABLE1 VALUES(103, 'TONY') LOG ERRORS REJECT LIMIT UNLIMITED;
INSERT INTO TABLE1 VALUES(103, 'RONY') LOG ERRORS REJECT LIMIT UNLIMITED;

Then:

SELECT * FROM table1;

Outputs:

NONAME
101JOHN
102MORN
103RONY
103TONY

and:

SELECT * FROM ERR$_TABLE1;

Outputs:

ORA_ERR_NUMBER$ORA_ERR_MESG$ORA_ERR_ROWID$ORA_ERR_OPTYP$ORA_ERR_TAG$NONAME
1ORA-00001: unique constraint (FIDDLE_TAXEGGNAZTFZWUNUZXOL.TX) violated<br>nullInull103TONY

db<>fiddle here

回复 原文
春夜浅 2025-02-08 09:55:06

您可以使用Merge语句检查现有行，只插入新的行：

MERGE INTO TABLE1 dst
USING (
  SELECT 101 AS no, 'JOHN' AS name FROM DUAL
) src
ON (dst.no = src.no AND dst.name = src.name)
WHEN NOT MATCHED THEN
  INSERT (no, name) VALUES (src.no, src.name);

MERGE INTO TABLE1 dst
USING (
  SELECT 102 AS no, 'MORN' AS name FROM DUAL
) src
ON (dst.no = src.no AND dst.name = src.name)
WHEN NOT MATCHED THEN
  INSERT (no, name) VALUES (src.no, src.name);

MERGE INTO TABLE1 dst
USING (
  SELECT 103 AS no, 'TONY' AS name FROM DUAL
) src
ON (dst.no = src.no AND dst.name = src.name)
WHEN NOT MATCHED THEN
  INSERT (no, name) VALUES (src.no, src.name);

MERGE INTO TABLE1 dst
USING (
  SELECT 103 AS no, 'TONY' AS name FROM DUAL
) src
ON (dst.no = src.no AND dst.name = src.name)
WHEN NOT MATCHED THEN
  INSERT (no, name) VALUES (src.no, src.name);

MERGE INTO TABLE1 dst
USING (
  SELECT 103 AS no, 'RONY' AS name FROM DUAL
) src
ON (dst.no = src.no AND dst.name = src.name)
WHEN NOT MATCHED THEN
  INSERT (no, name) VALUES (src.no, src.name);

然后：

SELECT * FROM table1;

输出：

noname
101john
102morn
103rony
103tony

db＆lt;＆gt;

You can use MERGE statements to check for existing rows and only insert new ones:

MERGE INTO TABLE1 dst
USING (
  SELECT 101 AS no, 'JOHN' AS name FROM DUAL
) src
ON (dst.no = src.no AND dst.name = src.name)
WHEN NOT MATCHED THEN
  INSERT (no, name) VALUES (src.no, src.name);

MERGE INTO TABLE1 dst
USING (
  SELECT 102 AS no, 'MORN' AS name FROM DUAL
) src
ON (dst.no = src.no AND dst.name = src.name)
WHEN NOT MATCHED THEN
  INSERT (no, name) VALUES (src.no, src.name);

MERGE INTO TABLE1 dst
USING (
  SELECT 103 AS no, 'TONY' AS name FROM DUAL
) src
ON (dst.no = src.no AND dst.name = src.name)
WHEN NOT MATCHED THEN
  INSERT (no, name) VALUES (src.no, src.name);

MERGE INTO TABLE1 dst
USING (
  SELECT 103 AS no, 'TONY' AS name FROM DUAL
) src
ON (dst.no = src.no AND dst.name = src.name)
WHEN NOT MATCHED THEN
  INSERT (no, name) VALUES (src.no, src.name);

MERGE INTO TABLE1 dst
USING (
  SELECT 103 AS no, 'RONY' AS name FROM DUAL
) src
ON (dst.no = src.no AND dst.name = src.name)
WHEN NOT MATCHED THEN
  INSERT (no, name) VALUES (src.no, src.name);

Then:

SELECT * FROM table1;

Outputs:

NONAME
101JOHN
102MORN
103RONY
103TONY

db<>fiddle here

回复 原文
悲念泪 2025-02-08 09:55:06

您可以将插入语句包装在PL/SQL匿名块中，并捕获dup_val_on_index exception：

BEGIN
  INSERT INTO TABLE1 VALUES(101, 'JOHN');
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
  NULL;
END;
/

BEGIN
  INSERT INTO TABLE1 VALUES(102, 'MORN');
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
  NULL;
END;
/

BEGIN
  INSERT INTO TABLE1 VALUES(103, 'TONY');
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
  NULL;
END;
/

BEGIN
  INSERT INTO TABLE1 VALUES(103, 'TONY');
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
  NULL;
END;
/

BEGIN
  INSERT INTO TABLE1 VALUES(103, 'RONY');
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
  NULL;
END;
/

然后：

SELECT * FROM table1;

输出：输出：

noname
101john
102morn
103rony
103tony

db＆lt;

You can wrap your INSERT statements in a PL/SQL anonymous block and catch the DUP_VAL_ON_INDEX exception:

BEGIN
  INSERT INTO TABLE1 VALUES(101, 'JOHN');
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
  NULL;
END;
/

BEGIN
  INSERT INTO TABLE1 VALUES(102, 'MORN');
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
  NULL;
END;
/

BEGIN
  INSERT INTO TABLE1 VALUES(103, 'TONY');
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
  NULL;
END;
/

BEGIN
  INSERT INTO TABLE1 VALUES(103, 'TONY');
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
  NULL;
END;
/

BEGIN
  INSERT INTO TABLE1 VALUES(103, 'RONY');
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
  NULL;
END;
/

Then:

SELECT * FROM table1;

Outputs:

NONAME
101JOHN
102MORN
103RONY
103TONY

db<>fiddle here

回复 原文
染火枫林 2025-02-08 09:55:06

您可以使用提示您的插入语句以跳过此类行。但它起作用比带有检查或错误登录表的普通插入量要慢得多。

 开始
插入table1值（101，'john'）;
插入table1值（102，'morn'）; 
插入table1值（103，'tony'）;
插入table1值（103，'tony'）;
插入table1值（103，'rony'）;
结尾;/

ORA-00001：违反了唯一约束（Fiddle_fqmlaywwhwtrjvynunxg.tx）
ORA-06512：在第5行

 开始
insert /*+ ignore_row_on_dupkey_index（table1（no，name））* / in table1 value（101，'john'）;
插入 /*+ ignore_row_on_dupkey_index（table1（no，name））* / in table1值（102，'morn'）;
插入 /*+ ignore_row_on_dupkey_index（table1（no，name））* / in table1值（103，'tony'）;
插入 /*+ ignore_row_on_dupkey_index（table1（no，name））* / in table1值（103，'tony'）;
插入 /*+ ignore_row_on_dupkey_index（table1（no，name））* / in table1 value（103，'rony'）;
结尾;/

1行影响

db＆lt;＆gt; fiddle

You may use IGNORE_ROW_ON_DUPKEY_INDEX hint in your INSERT statements to skip such rows. But it works much slower than plain insert with check or error logging table.

begin
INSERT INTO TABLE1 VALUES(101, 'JOHN');
INSERT INTO TABLE1 VALUES(102, 'MORN'); 
INSERT INTO TABLE1 VALUES(103, 'TONY');
INSERT INTO TABLE1 VALUES(103, 'TONY');
INSERT INTO TABLE1 VALUES(103, 'RONY');
end;/

ORA-00001: unique constraint (FIDDLE_FQMLAYWWHWTRJVYNUNXG.TX) violated
ORA-06512: at line 5

begin
INSERT /*+ ignore_row_on_dupkey_index(TABLE1(NO,NAME))*/ INTO TABLE1 VALUES(101, 'JOHN');
INSERT /*+ ignore_row_on_dupkey_index(TABLE1(NO,NAME))*/ INTO TABLE1 VALUES(102, 'MORN');
INSERT /*+ ignore_row_on_dupkey_index(TABLE1(NO,NAME))*/ INTO TABLE1 VALUES(103, 'TONY');
INSERT /*+ ignore_row_on_dupkey_index(TABLE1(NO,NAME))*/ INTO TABLE1 VALUES(103, 'TONY');
INSERT /*+ ignore_row_on_dupkey_index(TABLE1(NO,NAME))*/ INTO TABLE1 VALUES(103, 'RONY');
end;/

1 rows affected

db<>fiddle here

回复 原文
~没有更多了~

关于作者

半透明的墙

暂无简介

文章
评论
27 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

alipaysp_snBf0MSZIv

文章 0 评论 0

梦断已成空

文章 0 评论 0

瞎闹

文章 0 评论 0

凯凯我们等你回来

文章 0 评论 0

寄意

文章 0 评论 0

似梦非梦

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文