오라클 병합 쿼리에서 기본 키를 반환하는 방법

오라클 병합 쿼리에서 기본 키를 반환하는 방법

오라클 병합 쿼리에서 기본 키를 반환하고 싶습니다.없는 경우 하나의 문을 사용하여 삽입하고 있으며 절차나 기능을 사용하고 싶지 않습니다.

이것은 샘플 쿼리입니다.

merge into myTable e
  using (select :empname name from dual) s
  on (UPPER(TRIM(e.empname)) = UPPER(TRIM(s.name)))
  when not matched then insert (empname) 
    values (s.name)

내 테이블의 기본 키 필드를 하나 더 가져와야 합니다.시퀀스 및 트리거를 사용하여 기본 키를 삽입합니다.

추가를 해봤습니다.RETURNING empID into :empId그러나 그것은 오류를 일으킵니다.

문제가 있습니다.

1. Merge Into지원하지 않음Returning Into, 그게 안 될 겁니다
2. 새 레코드를 삽입할 때만 사용되기 때문에 시퀀스가 항상 사용되지는 않습니다.
3. 시퀀스가 아직 현재 세션에서 사용되지 않은 경우 Sequence.curval을 쿼리하려면 오류가 발생하므로 시퀀스의 기존 값을 가져오는 것은 작동하지 않습니다.

해결 방법:

1. 프로시저나 익명의 프로그램 블록을 사용하여 값을 업데이트합니다. 만약sql%rowcount업데이트 후 0을 반환하고 대신 삽입을 수행합니다.
2. 선택 항목 사용(쿼리 대상)UPPER(TRIM(name))업데이트된 레코드를 찾습니다.

이거 먹어보세요.ID를 캡처하려면 패키지를 선언해야 합니다. 그렇지 않으면 SQL 문에 표시되지 않고 오류가 발생합니다.

pls-00231: 함수 이름. SQL에서 사용할 수 없습니다.

따라서 먼저 캡처 기능이 있는 패키지를 만든 다음 나중에 병합 문에서 ID에 액세스합니다.

CREATE OR REPLACE PACKAGE CaptureId
AS
   FUNCTION SaveId(newId IN NUMBER) RETURN NUMBER;
   FUNCTION GetId RETURN NUMBER;
END;

CREATE OR REPLACE PACKAGE BODY CaptureId
AS
   capturedId NUMBER(10);

   FUNCTION SaveId(newId IN NUMBER) RETURN NUMBER IS
   BEGIN
      capturedId := newId;
      RETURN capturedId;
   END;

   FUNCTION GetId RETURN NUMBER IS
   BEGIN
      RETURN capturedId;
   END;
END;

다음과 같이 정의되는 간단한 표와 시퀀스 생성기가 주어집니다.

CREATE TABLE EMPLOYEE
(
    EMPLOYEE_ID NUMBER(10) NOT NULL,
    FIRST_NAME VARCHAR2(120) NOT NULL,
    LAST_NAME VARCHAR2(120) NOT NULL,
    CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEE_ID) ENABLE
);

CREATE SEQUENCE SEQ_EMPLOYEE;

그런 다음 익명 블록에 있는 패키지를 병합 문과 함께 사용하여 ID를 캡처한 후 반환할 수 있습니다.이는 매우 간단한 예이며 ID를 테이블 유형으로 캡처하기 위해 패키지를 재작업하지 않는 한 배열 바인딩된 변수에서는 작동하지 않습니다.기회가 된다면 이를 보여주는 사례를 만들어 볼 수도 있겠습니다.

BEGIN
   MERGE INTO EMPLOYEE USING (SELECT CaptureId.SaveId(:myInputId) AS EMPLOYEE_ID,
                                     :myFirstName as FIRST_NAME,
                                     :myLastName as LAST_NAME
                                FROM DUAL) B
      ON (A.EMPLOYEE_ID = B.EMPLOYEE_ID)
   WHEN NOT MATCHED THEN
       INSERT (EMPLOYEE_ID,
               FIRST_NAME,
               LAST_NAME)
       VALUES (CaptureId.SaveId(SEQ_EMPLOYEE.NEXTVAL),
               B.FIRST_NAME,
               B.LAST_NAME)
   WHEN MATCHED THEN
     UPDATE SET A.FIRST_NAME= B.FIRST_NAME,
                A.LAST_NAME= B.LAST_NAME;

   SELECT CaptureId.GetId INTO :myOutputId FROM DUAL;
END;

언급URL : https://stackoverflow.com/questions/13251501/how-to-return-primary-key-from-oracle-merge-query