How to Use the oracle RETURNING clause, oraclereturning

How to Use the oracle RETURNING clause, oraclereturning

RETURNING is usually used in combination with DML statements. (Insert update delete)

Usage:

UPDATE table_name SET expr1RETURNING column_nameINTO xxx

INSERT: returns the added value.
UPDATE: return the updated value.

DELETE: returns the value before deletion.

RETURNING can be used in sqlplus and plsql again.

For plsql, the preceding Code indicates that xxx is the declared variable name.

For sqlplus, xxx can be a variable, that is

VARIABLE var_name varchar2(10)UPDATE table_name SET expr1RETURNING column_name INTO :var_name;

Here: var_name uses the Bind Variable.

In addition, RETURNING seems to be applicable

Insert into values supports RETURNING

Insert into select, and MERGE statements do not support RETURNING.

Example 1:

Table creation statement:

CREATE TABLE TEST111(    A1 VARCHAR(10),    A2 VARCHAR(20));CREATE SEQUENCE TEST111_S1START WITH 1INCREMENT BY 1CACHE 20MAXVALUE 999999999999999999999999999CYCLE;

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 SEQ FROM DUAL; COMMIT; DBMS_OUTPUT.PUT_LINE(SEQ);END; 

Example 2:
In addition, RETURNING can be combined with bulk collect (batch binding, and FORALL)

DECLARETYPE table_type IS TABLE OF column_name%TYPE;v_tab table_type;BEGIN UPDATE table_name SET expr1 RETURNING column_name BULK COLLECT INTO v_tab; FOR i IN v_tab.first .. v_tab.last LOOP DBMS_OUTPUT.put_line( l_tab(i));END LOOP;COMMIT;END;

ORA-06547: INSERT, UPDATE, or DELETE statements must use the RETURNING clause

Cause of this error:

The returning into clause acts on insert, update, and delete, but the select clause does not work. You should use.

The error message is stored as follows:

Create or replace procedure p_stu_info (s_id number, s_name varchar2) is v_name varchar2 (10); v_age number; v_ErrMsg varchar2 (200); begin execute immediate 'select name, age from student_test where id =: 1 and name =: 2 'using s_id, s_name returning into v_name, v_age; dbms_output.put_line (v_name | 'age: '| to_char (v_age); exception when others then v_ErrMsg: = SUBSTRB (SQLERRM, 1,200); dbms_output.put_line ('student not found'); end p_stu_info;

Change it to the following and it will be OK:

Create or replace procedure p_stu_info (s_id number, s_name varchar2) is v_name varchar2 (10); v_age number; v_ErrMsg varchar2 (200); 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 | 'age: '| to_char (v_age); exception when others then v_ErrMsg: = SUBSTRB (SQLERRM, 1,200); dbms_output.put_line ('the student cannot be found, cause of error:' | v_ErrMsg ); end p_stu_info;