savepoint语句
savepoint语句命名和标记事务处理中的当前点。使用savepoing可以回滚事务的一部分,而不是整个事务。对于会话而言,活动的savepoint的数目是没有限制的。
DROP TABLE emp_name; CREATE TABLE emp_name AS SELECT employee_id, last_name, salary FROM employees;
CREATE UNIQUE INDEX empname_ix ON emp_name (employee_id);
DECLARE emp_id employees.employee_id%TYPE; emp_lastname employees.last_name%TYPE; emp_salary employees.salary%TYPE;
BEGIN SELECT employee_id, last_name, salary INTO emp_id, emp_lastname, emp_salary FROM employees WHERE employee_id = 120;
UPDATE emp_name SET salary = salary * 1.1 WHERE employee_id = emp_id;
DELETE FROM emp_name WHERE employee_id = 130;
SAVEPOINT do_insert;
INSERT INTO emp_name (employee_id, last_name, salary) VALUES (emp_id, emp_lastname, emp_salary);
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN ROLLBACK TO do_insert; DBMS_OUTPUT.PUT_LINE('插入被回滚'); END; |
当回滚到一个保存点的时候,该保存点后声明的所有保存点被删除。回滚到的这个保存点不被删除。最简单的提交或回滚语句会删除所有的保存点。
如果在递归子程序中声明了保存点,则在递归的每个层次上,保存点的新实例都会运行,但只能回滚到最近声明的保存点。
保存点是非声明的标识符。在一个事务中重用保存点的名称,相当于将保存点从旧的位置移动到当前位置。
--重用保存点名称 DROP TABLE emp_name; CREATE TABLE emp_name AS SELECT employee_id, last_name, salary FROM employees;
CREATE UNIQUE INDEX empname_ix ON emp_name (employee_id);
DECLARE emp_id employees.employee_id%TYPE; emp_lastname employees.last_name%TYPE; emp_salary employees.salary%TYPE;
BEGIN SELECT employee_id, last_name, salary INTO emp_id, emp_lastname, emp_salary FROM employees WHERE employee_id = 120; --保存点原来位置 SAVEPOINT my_savepoint;
UPDATE emp_name SET salary = salary * 1.1 WHERE employee_id = emp_id;
DELETE FROM emp_name WHERE employee_id = 130; --保存点新位置 SAVEPOINT my_savepoint;
INSERT INTO emp_name (employee_id, last_name, salary) VALUES (emp_id, emp_lastname, emp_salary);
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN ROLLBACK TO my_savepoint; DBMS_OUTPUT.PUT_LINE('事务回滚.'); END; |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17013648/viewspace-1122925/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17013648/viewspace-1122925/