Several minor issues with the returning statement in Oracle

You can specify the returning statement in the DML Statement of oracle. The use of returning statements can simplify PL/SQL programming in many cases.

It is not intended to explain the use of the returning statement (in fact, it is very simple to use, and there is no big difference with the select into statement .), It mainly describes the features of the returning statement.

In fact, this article originated from a question my colleague asked me:

When the update statement is used, will the returning result be the result before update or after update?

I have been asked this question. In the case of Delete, returning must return the result before Delete. In the case of insert, returning must return the result after insert. However, when update returns the result, it cannot be inferred. In addition, the primary key columns are generally returned when the update returning statement is used, but the primary key columns are not modified, therefore, it is unclear whether the Oracle returns the results before update or after update.

Of course, a simple example can be tested:

SQL> Create Table T (ID number, name varchar2 (30 ));

The table has been created.

SQL> set serverout on
SQL> declare
2 v_name varchar2 (30 );
3 begin
4 insert into T values (1, 'angtk ') Returning name into v_name;
5 dbms_output.put_line ('insert: '| v_name );
6 v_name: = NULL;
7 update t set name = 'ytk 'returning name into v_name;
8 dbms_output.put_line ('Update: '| v_name );
9 v_name: = NULL;
10 Delete t returning name into v_name;
11 dbms_output.put_line ('delete: '| v_name );
12 end;
13/
Insert: yangtk
Update: ytk
Delete: ytk

The PL/SQL process is successfully completed.

Obviously, the returning statement of the update operation is the result after the update operation is returned.

By the way, I would like to summarize several returning operations related issues:

1. The returning statement seems to be common to the Return Statement.

SQL> set serverout on
SQL> declare
2 v_name varchar2 (30 );
3 begin
4 insert into T values (1, 'yangtk ') return name into v_name;
5 dbms_output.put_line ('insert: '| v_name );
6 v_name: = NULL;
7 update t set name = 'ytk 'Return name into v_name;
8 dbms_output.put_line ('Update: '| v_name );
9 v_name: = NULL;
10 Delete t return name into v_name;
11 dbms_output.put_line ('delete: '| v_name );
Ixdba. Net Technical Community
12 end;
13/
Insert: yangtk
Update: ytk
Delete: ytk

The PL/SQL process is successfully completed.

2. The returning statement can also use the sqlplus variable. In this way, the returning statement does not have to be used in PL/SQL statements.

SQL> var v_name varchar2 (30)
SQL> insert into T values (1, 'angtk ') Returning name into: v_name;

One row has been created.

SQL> Print v_name

V_name
--------------------------------
Yangtk

SQL> Update t set name = 'ytk 'returning name into: v_name;

1 row updated.

SQL> Print v_name

V_name
--------------------------------
Ytk

SQL> Delete t returning name into: v_name;

One row has been deleted.

SQL> Print v_name

V_name
--------------------------------
Ytk

3. The insert into values Statement supports the returning statement, but the insert into SELECT statement does not. The merge statement does not support the returning statement.

SQL> merge into t using (select * from T) T1
2 On (T. ID = t1.id)
3 when matched then update set name = t1.name
4 when not matched then insert values (t1.id, t1.name)
5 returning name into: v_name;
Returning name into: v_name
* Error in row 5th:
ORA-00933: SQL command ended incorrectly

SQL> insert into T select * from t returning name into: v_name;
Insert into T select * from t returning name into: v_name
* Error in row 1st:
ORA-00933: SQL command ended incorrectly

These two restrictions are really inconvenient. I wonder if Oracle will be released in a later version.
I personally feel that the returning statement and the bulk collect into statement have more opportunities to work with each other.