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;
阅读(727) | 评论(0) | 转发(0) |