DML returning into 用法，使用操作前的值

The RETURNING INTO clause allows us to return column values for rows affected by DML statements. The following test table is used to demonstrate this clause.

SQL> create table testreturn (id int,name varchar2(10));
Table created.
SQL> create sequence seq;
Sequence created.
SQL> l
  1  declare
  2  v_id testreturn.id%type;
  3  begin
  4  insert into testreturn values(seq.nextval,'anbob')
  5  returning id into v_id;
  6  commit;
  7  dbms_output.put_line('id values:'||v_id);
  8* end;
SQL> /
id values:1
PL/SQL procedure successfully completed.
SQL> /
id values:2
PL/SQL procedure successfully completed.
SQL> /
id values:3
PL/SQL procedure successfully completed.
SQL> /
id values:4
PL/SQL procedure successfully completed.
SQL> /
id values:5
PL/SQL procedure successfully completed.
delete与update 就不再测试了，
下面演示一种批量的操作
conn system/oracle
create table anbob.allobj as 
select * from dba_objects;
conn anbob/anbob
SQL> create table allobj_dellog(obj# int,username varchar2(20),dtime date);
Table created.
SQL> desc allobj;
 Name                                   Null?    Type
 -------------------------------------- -------- ------------------------------
 OWNER                                           VARCHAR2(30)
 OBJECT_NAME                                     VARCHAR2(128)
 SUBOBJECT_NAME                                  VARCHAR2(30)
 OBJECT_ID                                       NUMBER
 DATA_OBJECT_ID                                  NUMBER
 OBJECT_TYPE                                     VARCHAR2(19)
 CREATED                                         DATE
 LAST_DDL_TIME                                   DATE
 TIMESTAMP                                       VARCHAR2(19)
 STATUS                                          VARCHAR2(7)
 TEMPORARY                                       VARCHAR2(1)
 GENERATED                                       VARCHAR2(1)
 SECONDARY                                       VARCHAR2(1)
SQL> desc allobj_dellog;
 Name                                   Null?    Type
 -------------------------------------- -------- ------------------------------
 OBJ#                                            NUMBER(38)
 USERNAME                                        VARCHAR2(20)
 DTIME                                           DATE
SQL> l
  1  declare
  2  type t_objid is table of allobj.object_id%type;
  3  v_tab_del t_objid;
  4  begin
  5  delete from allobj where rownum<11
  6  returning object_id bulk collect into v_tab_del;
  7  forall i in 1.. v_tab_del.count
  8  insert into allobj_dellog values(v_tab_del(i),user,sysdate);
  9   commit;
  10* end;
SQL> /
PL/SQL procedure successfully completed.
SQL> select * from allobj_dellog;
      OBJ# USERNAME             DTIME
---------- -------------------- -------------------
        20 ANBOB                2011-10-21 10:33:03
        44 ANBOB                2011-10-21 10:33:03
        28 ANBOB                2011-10-21 10:33:03
        15 ANBOB                2011-10-21 10:33:03
        29 ANBOB                2011-10-21 10:33:03
         3 ANBOB                2011-10-21 10:33:03
        25 ANBOB                2011-10-21 10:33:03
        39 ANBOB                2011-10-21 10:33:03
        51 ANBOB                2011-10-21 10:33:03
        26 ANBOB                2011-10-21 10:33:03
10 rows selected.