What exactly does Returning .... INTO .... do in Oracle SQL?

Question

I've recently switched from using SQL Server to Oracle. There's some Oracle specific functions that are confusing me. The documentation at https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/returninginto_clause.htm didn't make much sense to me.

If I do something like:

INSERT INTO my_table (val2, val3, val4)
VALUES (2, 3, 4)
RETURNING val1 INTO 1

where the table schema is:

CREATE TABLE my_table (
    val1 NUMBER NOT NULL,
    val2 NUMBER,
    val3 NUMBER,
    val4 NUMBER,
    CONSTRAINT pk_val1 PRIMARY KEY (val1)
)

what does it do? what does it return?

Stortz

you can do Insert Into in sql server... – Infarct

That's why my question wasn't about 'Insert Into' it was about 'Returning Into'. – Stortz

Answer 1

Oracle returning clause returns the specified column value in output variable. From your query it seems that you are using oracle auto increment to populate primary key. So

INSERT INTO my_table (val2, val3, val4) VALUES (2, 3, 4) RETURNING val1 INTO 1

This will insert a row in my_table with values of variables specified at index 2,3,4 into columns val2, val3, val4 and return generated primary key in variable specified at index 1.

Bioplasm

Thank you! I wish Oracle's documentation were that clear. – Stortz

"values of variables specified at index 2,3,4" implies that there are bind variables. If "2" should be ":2", "3" should be ":3", etc. then this would make sense. As written, however, those are constants not bind variables. – Blunge

Answer 2

The SQL statement you posted doesn't make sense. You can't return a column into a constant. You return it into a local variable of some sort.

Let's say, for example, that your table has a trigger on it that sets the value of the val1 column using a trigger. That would allow the insert statement to succeed despite not specifying a value for the primary key. Assuming you have such a trigger and you do something like

INSERT INTO my_table (val2, val3, val4)
  VALUES (2, 3, 4)
  RETURNING val1 INTO l_local_variable

will insert a row into my_table and return the value that the trigger stored in the val1 column into the local variable l_local_variable.

Blunge