RETURNING into用法

1、概述

returning...into...和insert/upadate/delete语句(也只能和他们一起使用，否则会报错：ORA-06547:RETURING clause must be used with INSERT, UPDATE, or DELETE statement)一起使用，用于赋予指定列的值给相应变量，类似于select...into...语句。returning...into...在insert、update和delete语句中返回的结果是有区别，其中：
　　1) delete语句：returning...into...返回的是delete之前的结果； 
　　2) insert语句：returning...into...返回的是insert之后的结果； 
　　3) update语句：returning...into...返回的是update之后的结果。  

2、语法

   1) delete语句中使用

delete from table_name [where exp]
  returning column1, column2... into var1, var2...

   2) update语句中使用

insert into table_name values(...)
    returning column1, column2... into var1, var2...

   3) insert语句中使用

update table_name set exp1 [where exp2]
  returning column1, column2... into var1, var2...

注意：returning into不能使用INSERT INTO... SELECT语句和MERGE语句。

3、举例使用

   1)插入数据

declare
  v_gid t_student.gid%type;
begin
   insert into t_student(gid, name, age, sex) values(seq_admin.nextval, 'ZHANSAN', 28, '1')
        returning gid into v_gid;
   commit;
   dbms_output.put_line(v_gid);
end;

     打印结果：

100001

   2）修改数据

declare
  v_name t_student.name%type;
  v_age t_student.age%type;
begin
   update t_student set name = 'CHENZHEN' where gid = 1
        returning age, name into v_age, v_name;
   commit;
   dbms_output.put_line('name:'||v_name||', age:'||v_age);
end;

      打印结果：

name:CHENZHEN, age:45

    3）删除数据

declare
  v_name t_student.name%type;
  v_age t_student.age%type;
begin
   delete from t_student where gid = 1
        returning age, name into v_age, v_name;
   commit;
   dbms_output.put_line('name:'||v_name||', age:'||v_age);
end;

       打印结果：

name:CHENZHEN, age:45

     4）DML影响多个数据行，使用BULK COLLECT返回给数组

declare
  TYPE gid_tbl_type IS TABLE OF t_student.gid%type;
  v_gid_tbl gid_tbl_type;
begin
   update t_student set name = 'CHENZHEN'
        returning gid BULK COLLECT into v_gid_tbl;--使用BULK COLLECT将列的值返回给数组
   commit;
   for i in 1..v_gid_tbl.count loop
     dbms_output.put_line('updated gid: '||v_gid_tbl(i));
   end loop;
end;

        打印结果：

updated gid: 100001
updated gid: 100002

      5）批绑定中使用returning into

declare
  TYPE name_tbl_type IS TABLE OF t_student.name%type;
  TYPE gid_tbl_type IS TABLE OF t_student.gid%type;  
  v_name_tbl name_tbl_type := name_tbl_type('TONGZI', 'WENLI', 'DAZHUANG', 'ZHUANGSHAO');
  v_gid_tbl gid_tbl_type;
begin
  forall i in v_name_tbl.first..v_name_tbl.last
    insert into t_student(gid, name) values(seq_admin.nextval, v_name_tbl(i))
        returning gid BULK COLLECT into v_gid_tbl;--使用BULK COLLECT将列的值返回给数组
   commit;
   for i in 1..v_gid_tbl.count loop
     dbms_output.put_line('gid: '||v_gid_tbl(i)||', name:'||v_name_tbl(i)||' inserted');
   end loop;
end;

        打印结果：

gid: 100003, name:TONGZI inserted
gid: 100004, name:WENLI inserted
gid: 100005, name:DAZHUANG inserted
gid: 100006, name:ZHUANGSHAO inserted

     6）动态语句中使用returning into

declare
  TYPE gid_tbl_type IS TABLE OF t_student.gid%type;
  v_gid_tbl gid_tbl_type;
  v_name t_student.name%type;
begin
  v_name := 'CHENZHEN';
   execute immediate ' update t_student set name = :1 returning gid into :2' using v_name 
         returning bulk collect into v_gid_tbl;
   commit;
   
   for i in 1..v_gid_tbl.count loop
     dbms_output.put_line('updated gid: '||v_gid_tbl(i));
   end loop;
end;

                打印结果：

updated gid: 100003
updated gid: 100004
updated gid: 100005
updated gid: 100006