不需要在主表中插入重復記錄，而是想根據e_id更新現有記錄

CREATE TABLE new_details_staging 
(
    e_id         NUMBER(10),
    e_name       VARCHAR2(30),
    portal_desc  VARCHAR2(50),
    risk_dec     VARCHAR2(50),
    CONSTRAINT pk_new_details_staging PRIMARY KEY (e_id)
);

INSERT INTO new_details_staging 
VALUES (11, 'A', 'AA', 'High');

INSERT INTO new_details_staging 
VALUES (22, 'B', 'BB', 'Low');

CREATE TABLE lookup_ref 
(
    ref_id       NUMBER(10),
    ref_typ      VARCHAR2(30),
    ref_typ_desc VARCHAR2(20),
    CONSTRAINT pk_lookup_ref PRIMARY KEY (ref_id)
);

INSERT INTO lookup_ref 
VALUES (181, 'portal', 'AA');

INSERT INTO lookup_ref 
VALUES (182, 'portal', 'BB');

INSERT INTO lookup_ref 
VALUES (183, 'risk', 'High');

INSERT INTO lookup_ref 
VALUES (184, 'risk', 'Low');

CREATE TABLE new_details_main 
(
    e_id    NUMBER(10),
    e_name  VARCHAR2(30),
    portal  NUMBER(20),
    risk    NUMBER(20),
    CONSTRAINT pk_new_details_main PRIMARY KEY (e_id)
);

COMMIT;

存盤程序——

我想從暫存到存盤程序將記錄插入到主表中。

create or replace procedure sp_main(ov_err_msg OUT varchar2)
is
begin

INSERT INTO new_details_main (e_id, e_name, portal, risk)
SELECT n.e_id
,  n.e_name
,  (
   SELECT lp.ref_id
   FROM   lookup_ref lp -- lp is for Lookup-Portal
   WHERE  lp.ref_typ       = 'portal'
   AND    lp.ref_typ_desc  = n.portal_desc
  ),
  (
   SELECT lr.ref_id
   FROM   lookup_ref lr -- lr is for Lookup-Risk
   WHERE  lr.ref_typ       = 'risk'
   AND    lr.ref_typ_desc  = n.risk_dec
  )  
FROM  new_details_staging n
;
end;

此程序為我提供了所需的確切結果，但如果將資料添加到同一個 e_id 的臨時表中，那么它應該更新主表中的記錄而不是插入新記錄。例子，

如果我截斷臨時表并插入一個新行，其中風險為低，但這次它更新為高

INSERT INTO new_details_staging 
VALUES (22, 'B', 'BB', 'High');

因此，在我的主表中，應該只有 2 行，但對于 e_id 22，它應該已更新為 183。我如何使用 store proc 實作這一點？

uj5u.com熱心網友回復：

我說你需要的MERGE。

這是一個例子。

程序（OUT為了簡單起見，我洗掉了引數；無論如何您都沒有使用它）。此外，我重新撰寫了它，以便它不使用portal和risk值的子查詢，而是將同一個 ( lookup_ref) 表連接兩次（也許您需要使用外連接；您現在擁有的資料建議您不要使用，但是 - 有那個記）：

SQL> CREATE OR REPLACE PROCEDURE sp_main
  2  IS
  3  BEGIN
  4     MERGE INTO new_details_main m
  5          USING (SELECT n.e_id,
  6                        n.e_name,
  7                        lp.ref_id portal,
  8                        lr.ref_id risk
  9                   FROM new_details_staging n
 10                        JOIN lookup_ref lp
 11                           ON     lp.ref_typ_desc = n.portal_desc
 12                              AND lp.ref_typ = 'portal'
 13                        JOIN lookup_ref lr
 14                           ON     lr.ref_typ_desc = n.risk_dec
 15                              AND lr.ref_typ = 'risk') x
 16             ON (m.e_id = x.e_id)
 17     WHEN MATCHED
 18     THEN
 19        UPDATE SET m.e_name = x.e_name, m.portal = x.portal, m.risk = x.risk
 20     WHEN NOT MATCHED
 21     THEN
 22        INSERT     (e_id,
 23                    e_name,
 24                    portal,
 25                    risk)
 26            VALUES (x.e_id,
 27                    x.e_name,
 28                    x.portal,
 29                    x.risk);
 30  END;
 31  /

Procedure created.

測驗：使用“初始”資料集（您發布的資料）運行程式：

SQL> EXEC sp_main;

PL/SQL procedure successfully completed.

結果是：

SQL> SELECT * FROM new_details_main;

      E_ID E_NAME         PORTAL       RISK
---------- ---------- ---------- ----------
        11 A                 181        183
        22 B                 182        184

你說過你將從臨時表中洗掉行并插入一個新行：

SQL> DELETE FROM new_details_staging;

2 rows deleted.

SQL> INSERT INTO new_details_staging
  2  VALUES (22, 'B', 'BB', 'High');

1 row created.

讓我們再次運行該程序以檢查新結果：

SQL> EXEC sp_main;

PL/SQL procedure successfully completed.

SQL> SELECT * FROM new_details_main;

      E_ID E_NAME         PORTAL       RISK
---------- ---------- ---------- ----------
        11 A                 181        183
        22 B                 182        183    --> RISK is modified to 183

SQL>

標籤：甲骨文 plsql

上一篇：插入其他表時需要轉換資料型別

下一篇：Oracle中自定義生成的身份列