oracle - Oracle 唯一密钥 - 获取 PK 或 RowID

Question

是否可以捕获触发重复异常的主密钥或记录rowID？

table1 有 PK: col1 和 Unique1: col2

例如

begin
  insert into table1(col1, col2, col3)
  values (1, 2, 3);
exception
  when dup_val_on_index then
    --- here, can you somehow indicate either PK or ROWID of the record that generated the exception of uniqueness?
  e.g.
  update table1 set 
    col3 = 100
  where rowid = "GETROWID" or col1 = "GETPK";
end;

标签： oracleprimary-keyunique-key

Answer 1

在“正常”代码中，您不使用常量来插入值；您通常会将值放在变量中，因此您的代码看起来更像：

DECLARE
  strVar1    TABLE1%TYPE;
  nVar2      NUMBER;
  nVar3      NUMBER;
begin
  SELECT s1, n2, n3
    INTO strVar1, nVar2, nVar3
    FROM SOME_TABLE;

  insert into table1(col1, col2, col3)
    values (strVar1, nVar2, nVar3);
exception
  when dup_val_on_index then
    update table1
      set col3 = 100
      where col1 = strVar1;
end;

但更好的主意是首先使用 MERGE 语句来避免异常：

MERGE INTO TABLE1 t1
  USING (SELECT S1, N2, N3
           FROM SOME_TABLE) s
    ON (t1.COL1 = s.S1)
  WHEN MATCHED THEN
    UPDATE SET COL3 = 100
  WHEN NOT MATCHED THEN
    INSERT (COL1, COL2, COl3)
    VALUES (s.S1, s.N2, s.N3);