oracle 自治事务的使用举例.

ORACLE自治事务可以用于独立于MT的AT应用. 可以用在记日志的独立事务中.
下面举个小例来说明.

首先创建日志表.
-- Create table
create table SYSTEMLOG
(
  SLID        NUMBER not null,
  SLTIMESTAMP DATE not null,
  SPENDSECOND NUMBER,
  SL          VARCHAR2(4000)
)
tablespace DATA_SPC
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 128K
    next 128K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate primary, unique and foreign key constraints
alter table SYSTEMLOG
  add constraint PK_SYSTEMLOG primary key (SLID)
  using index
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );


-----------------------------------------------------------

create or replace procedure writesyslog(msg varchar2) is
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 INSERT INTO SYSTEMLOG(SLID,SLTIMESTAMP,SL)
 VALUES(SEQ_SYSTEMLOG_ID.NEXTVAL, SYSDATE, msg);
 COMMIT;
end writesyslog;

-----------------------------------------------------------

-- Create sequence
create sequence SEQ_SYSTEMLOG_ID
minvalue 1
maxvalue 999999999999
start with 1000021
increment by 1
cache 20;


-----------------------------------------------------------

create or replace procedure TESTP1 is
CC number;
begin
  select count(*) into CC from test1 t1 WHERE T1.LTABLE_OWNER='XMAN';
  dbms_output.put_line(to_char(CC)||' rows will be delete!');
  DELETE FROM TEST1 T1 WHERE T1.LTABLE_OWNER='XMAN';
  dbms_output.put_line('write log.');
  writesyslog('DELETE FROM TEST1 T1 WHERE T1.LTABLE_OWNER=XMAN');
  dbms_output.put_line('rollback!');
  rollback;
end TESTP1;




SQL> execute TESTP1;
 
109 rows will be delete!
write log.
rollback!
 
PL/SQL procedure successfully completed
 
Executed in 0.125 seconds
 
SQL>


可以看出在过程TESTP1中, 在调用WRITESYSLOG中用到了COMMIT, 但MT中的事务并没有COMMIT;