Oracle中使用主键将多表插入到表中

Question

我正在尝试使用单个表将数据插入到不同的表中，并且我正在插入的表具有主键。

INSERT
ALL
WHEN TO_CHAR(L_SHIPDATE,'YYYY') = '1995' THEN
 INTO YEAR1995
  VALUES (L_PARTKEY, L_EXTENDEDPRICE - 0.1*L_EXTENDEDPRICE)
WHEN TO_CHAR(L_SHIPDATE,'YYYY') = '1996' THEN
 INTO YEAR1996
  VALUES (L_PARTKEY, L_EXTENDEDPRICE - 0.1*L_EXTENDEDPRICE)
WHEN TO_CHAR(L_SHIPDATE,'YYYY') = '1997' THEN
 INTO YEAR1997
  VALUES (L_PARTKEY, L_EXTENDEDPRICE - 0.1*L_EXTENDEDPRICE)


SELECT distinct L_PARTKEY, L_EXTENDEDPRICE, L_SHIPDATE 
FROM LINEITEM;

CREATE TABLE YEAR1995
(
    Y95_PARTKEY NUMBER(12) NOT NULL,
    Y95_PRICE   NUMBER(12) NOT NULL,
    CONSTRAINT YEAR95_PKEY PRIMARY KEY (Y95_PARTKEY, Y95_PRICE)  
);

Answer 1

正如本文所建议的，您可以尝试以下方法：

exec dbms_errlog.create_error_log ('myInsertErrors');

INSERT
ALL
WHEN TO_CHAR(L_SHIPDATE,'YYYY') = '1995' THEN
 INTO YEAR1995
  VALUES (L_PARTKEY, L_EXTENDEDPRICE - 0.1*L_EXTENDEDPRICE)
WHEN TO_CHAR(L_SHIPDATE,'YYYY') = '1996' THEN
 INTO YEAR1996
  VALUES (L_PARTKEY, L_EXTENDEDPRICE - 0.1*L_EXTENDEDPRICE)
WHEN TO_CHAR(L_SHIPDATE,'YYYY') = '1997' THEN
 INTO YEAR1997
  VALUES (L_PARTKEY, L_EXTENDEDPRICE - 0.1*L_EXTENDEDPRICE)


SELECT distinct L_PARTKEY, L_EXTENDEDPRICE, L_SHIPDATE 
FROM LINEITEM

LOG ERRORS INTO err$_myInsertErrors REJECT LIMIT UNLIMITED;

这样，所有的insert异常（即重复数据）都将存储在ERR$_MYINSERTERRORS表中，并完成insert语句