How to use Oracle returning clause _oracle

Returning itself is often used in conjunction with DML statements. (INSERT UPDATE DELETE)

How to use:

UPDATE table_name SET EXPR1
returning column_name into
xxx

INSERT: Returns the value that is added
Update: The updated value when returned

Delete: Returns the value before deletion

Returning can be used in Sqlplus and Plsql

If it is plsql as above code, XXX is the variable name declared

If it is sqlplus,xxx can be a variable, that is,

VARIABLE var_name varchar2 (a)
UPDATE table_name SET expr1 returning column_name into:var_name
;

Here: The var_name uses the binding variable

In addition, returning seemingly can be with return general

INSERT into VALUES support returning

INSERT into SELECT, and merge statements do not support returning

Example 1:

To build a table statement:

CREATE TABLE TEST111 (
    A1 VARCHAR (),
    A2 VARCHAR)
;

CREATE SEQUENCE test111_s1
START with 1
INCREMENT by 1
CACHE
MAXVALUE 999999999999999999999999999
CYCLE;

DECLARE 
 SEQ number;
BEGIN
 INSERT into TEST111 VALUES (test111_s1. Nextval, ' AAA2 ')
 returning A1 into SEQ;
 Dbms_output. Put_Line (SEQ);
End; 

DECLARE 
 SEQ number;
BEGIN
 INSERT into TEST111 VALUES (test111_s1. Nextval, ' AAA3 ');
 SELECT test111_s1. Currval into the SEQ from DUAL;
 COMMIT;
 Dbms_output. Put_Line (SEQ);

Example 2:
In addition, returning can be combined with bulk COLLECT (bulk binding, another is ForAll)

DECLARE
TYPE table_type is table of Column_name%type;
V_tab Table_type;
BEGIN
 UPDATE table_name
 SET expr1
 returning column_name BULK into COLLECT;

 For I in V_tab.first. V_tab.last LOOP
 dbms_output.put_line (L_tab (i));
End LOOP;

COMMIT;
End;

The ora-06547:insert,update or DELETE statement must use the returning clause

The reason for this error:

The returning into clause acts on the Insert,update,delete, while the Select is not available and should be used into.

The error is stored as follows:

Create or Replace procedure P_stu_info (s_id number, s_name varchar2) is
 v_name  varchar2 (a);
 V_age number  ;
 V_errmsg varchar2 ();
Begin
 Execute immediate ' select Name,age from Student_test where id=:1 and Name=:2 '
  using s_id, S_name
  return ing into V_name, v_age;
 Dbms_output.put_line (V_name | | ' The Age of: ' | | To_char (V_age));
Exception when
 others then
  v_errmsg: = SUBSTRB (SQLERRM, 1);
  Dbms_output.put_line (' Unable to find the corresponding student ');
End P_stu_info;

Change to the following so that's OK:

Create or Replace procedure P_stu_info (s_id number, s_name varchar2) is
 v_name  varchar2 (a);
 V_age number  ;
 V_errmsg varchar2 ();
Begin
 Execute immediate ' select Name,age from Student_test where id=:1 and Name=:2 ' into
  v_name, v_age
  using S _id, S_name;
 Dbms_output.put_line (V_name | | ' The Age of: ' | | To_char (V_age));
Exception when
 others then
  v_errmsg: = SUBSTRB (SQLERRM, 1);
  Dbms_output.put_line (' Unable to find the corresponding student, the wrong reason: ' | | V_ERRMSG);
End P_stu_info;