Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Returning multiple values after insert

Tags:

oracle

plsql

I got this 'Message' table.

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;

After i insert something i need the last inserted id and the date.

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

The above gives me the last inserted id, but like i said i also need the creation_date. I dont want to do a select query after...

Is there a way to get 2 values back after run an insert?

like image 772
yooouuri Avatar asked Nov 04 '15 12:11

yooouuri


1 Answers

You can write:

RETURNING id, creation_date INTO :last_insert_id, :last_creation_date.

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

like image 97
Hellmar Becker Avatar answered Sep 21 '22 01:09

Hellmar Becker