oracle 如何从oracle合并查询返回主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13251501/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to return primary key from oracle merge query
提问by Sen Jacob
I want to return the primary key from an oracle merge query. I'm using a single statement to insert if not exist and I don't want to use procedure or function to do so..
我想从 oracle 合并查询中返回主键。如果不存在，我正在使用单个语句插入，我不想使用过程或函数来这样做..
this is the sample query
这是示例查询
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)
and I need to get another primary key field of the myTable. the primary key is inserted using sequence and trigger
我需要获取 myTable 的另一个主键字段。使用序列和触发器插入主键
I tried adding RETURNING empID into :empIdbut it gives error
我尝试添加 RETURNING empID into :empId但它给出了错误
回答by GolezTrol
There's a problem.
有问题。
Merge Intodoesn't supportReturning Into, so that won't work.- The sequence will not always be used, because it's only used when inserting new records.
- Getting the existing value of a sequence won't work, because you get an error if you want to query Sequence.currval when the sequence wasn't used in the current session yet.
Merge Into不支持Returning Into，所以行不通。- 该序列不会总是被使用，因为它只在插入新记录时使用。
- 获取序列的现有值将不起作用，因为如果您想在当前会话中尚未使用序列时查询 Sequence.currval，则会出现错误。
To solve it:
要解决它：
- Use a procedure or anonymous program block to try to update the value. If
sql%rowcountreturn 0 after the update, perform the insert instead. - Use the selection (query for
UPPER(TRIM(name))) to find the record that was updated.
- 使用过程或匿名程序块尝试更新值。如果
sql%rowcount更新后返回 0，则改为执行插入。 - 使用选择（查询
UPPER(TRIM(name))）来查找已更新的记录。
回答by Dan Sorak
You can try this. You will need to declare a package to capture your id, otherwise it will not be visible to the SQL statement and you will get a error:
你可以试试这个。你需要声明一个包来捕获你的id，否则它对SQL语句是不可见的，你会得到一个错误：
pls-00231: functionnamemay not be used in SQL
pls-00231: 函数名不能在 SQL 中使用
So, first create the package with functions to capture and then later access the ID from the merge statement:
因此，首先使用要捕获的函数创建包，然后从合并语句中访问 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;
Given a simple table and sequence generator defined as:
给定一个简单的表和序列生成器，定义为：
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;
You can then use the package in an anonymous block with your merge statement to capture the id and return it. Note that this is a very simple example and it will not work with array-bound variables unless you rework the package to capture the IDs into a table type. If I get a chance, I may try to put together an example that shows this.
然后，您可以在带有合并语句的匿名块中使用该包来捕获 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;
