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;