不胜人生一场醉

-- Create table

CREATE TABLE TEST

(

testID NUMBER(20) not null,

testName VARCHAR2(20)

);

-- Create/Recreate primary, unique and foreign key constraints

ALTER TABLE TEST

ADD CONSTRAINT testprimaryKey PRIMARY KEY (testID);

-- Created on 2008-1-10 by bq_wang

DECLARE

-- Local variables here

exitProc EXCEPTION;

BEGIN

-- Test statements here

DELETE FROM test;

COMMIT;

SAVEPOINT b; --

INSERT INTO test VALUES(4,'d');

COMMIT WORK; --仅控制b之前的事务

BEGIN

SAVEPOINT a;

INSERT INTO test VALUES(1,'a');

INSERT INTO test VALUES(2,'b');

UPDATE test SET testID=1 WHERE testID=2;

COMMIT WORK;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK TO SAVEPOINT a; --如果有问题则回滚savepoint a的事务，不影响savepoint b

RAISE exitProc; --直接跳到存储过程最后，如果屏蔽仍可以处理之后的事务

END;

SAVEPOINT c; --控制以下事务，如果屏蔽，则由最后的commit完成b和c的提交

INSERT INTO test VALUES(5,'e');

COMMIT WORK;

EXCEPTION

WHEN exitProc THEN

ROLLBACK;

DBMS_OUTPUT.PUT_LINE('end');

END;