oracle - 插入后返回多个值

Question

我得到了这个“消息”表。

CREATE TABLE message (
    id INT PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    category_id INT NOT NULL REFERENCES category (id) ON DELETE CASCADE,
    text VARCHAR2(4000),
    media VARCHAR2(500),
    creation_date DATE DEFAULT SYSDATE
);

CREATE SEQUENCE message_seq;
CREATE OR REPLACE TRIGGER message_bir 
BEFORE INSERT ON message
FOR EACH ROW

BEGIN
  SELECT message_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;

我插入一些东西后，我需要最后插入的ID和日期。

INSERT INTO message (user_id, category_id, media)
VALUES (1, 1, 'fdsfsd')
RETURNING id INTO :last_insert_id

上面给了我最后插入的ID，但是就像我说的，我也需要creation_date。我不想在之后执行选择查询...

有没有办法在执行插入操作后取回2个值？

Answer 1

你可以写:

RETURNING id, creation_date INTO :last_insert_id, :last_creation_date.

参见 http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/returninginto_clause.htm