ANBOB™

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.

打赏

微信扫一扫，打赏作者吧～