在Oracle中获取插入行的ID，使用序列和触发器插入ID。

Question

我将尝试获取Oracle数据库中“自增列”的值。为了创建该列，我使用序列和触发器。

创建脚本：

create table SD_LOG (
    ID_SD_LOG       number(19) primary key,
    LOG_DATE        timestamp(3) default systimestamp NOT NULL,
    MODULE          nvarchar2(15) NOT NULL,
    INSTANCE        number(5) NOT NULL,
    REMOTE_ADDR     nvarchar2(39) NOT NULL,
    USERNAME        nvarchar2(30) NOT NULL,
    USER_AGENT      nvarchar2(1024) NOT NULL,
    HTTP_METHOD     nvarchar2(8) NOT NULL,
    HTTP_REQ_URL    nvarchar2(1024) NOT NULL,
    HTTP_STATUS     number(5),
    ERROR           nvarchar2(100),
    DETAILS         clob
);

/

-- Generate ID using sequence and trigger
create sequence SD_LOG_seq start with 1 increment by 1;

create or replace trigger SD_LOG_seq_tr
 before insert on SD_LOG for each row
 when (new.ID_SD_LOG is null)
begin
 select SD_LOG_seq.nextval into :new.ID_SD_LOG from dual;
end;
/

每次我进行插入操作时，我需要获取ID_SD_LOG创建的值以便稍后更新行。我遵循了其他一些问题的解决方法，但由于某种原因，我尝试的语句失败了，并显示以下错误信息：“SQL Error: ORA-00905: missing keyword”。这是我正在尝试运行的语句（仅插入而不带“RETURNING...”是可以正常工作的）：

INSERT INTO SD_LOG 
(MODULE, INSTANCE, REMOTE_ADDR, USERNAME, USER_AGENT, HTTP_METHOD, HTTP_REQ_URL) 
VALUES
('modulename', '1', '192.168.0.1', 'User Name', 'blah blah blah blah', 'POST', '/page?query=1234567890')
RETURNING ID_SD_LOG INTO gen_id;

有什么想法为什么我会收到那个错误？或者有其他方法可以不使用存储过程来完成这个操作吗？

- Fede E.

---

你是如何运行这个查询的？是在 PL/SQL 块内部吗？因为（遗憾的是），RETURNING 子句是 Oracle 中的 PL/SQL 语言特性，而不是 SQL 语言特性。 - Lukas Eder

如果我在PL / SQL块中运行查询，并将gen_id定义为NUMBER，则对我来说可以正常工作。正如@LukasEder所问的那样，在执行INSERT ... RETURNING ...语句的上下文是什么？ - Bob Jarvis - Слава Україні

@LukasEder 啊...我正在Oracle SQL Developer中测试这些语句，我猜它使用的是SQL？ - Fede E.

Answer 1

RETURNING子句是PL/SQL语言的一个特性。您无法在普通的SQL上下文中使用它，比如SQL Developer脚本、SQL*Plus或JDBC语句。这当然是非常不幸的。此Oracle意见平台上有一个想法可以得到投票支持，以解决此问题。 但是，您可以将语句包装在匿名块中，以返回到PL/SQL上下文中：

SET SERVEROUTPUT ON
DECLARE
  gen_id SD_LOG.ID_SD_LOG%TYPE;
BEGIN
  INSERT INTO SD_LOG 
  (MODULE, INSTANCE, REMOTE_ADDR, USERNAME, USER_AGENT, HTTP_METHOD, HTTP_REQ_URL) 
  VALUES
  ('modulename', '1', '192.168.0.1', 'User Name', 'blah blah blah blah', 'POST', '/page?query=1234567890')
  RETURNING ID_SD_LOG INTO gen_id;

  dbms_output.put_line(gen_id);
END;
/

- Lukas Eder