相关 《Oracle/Mysql迁移到Postgresql事务回滚行为差异及改造方法》 《Oracle与Postgresql在PLSQL内事务回滚的重大差异》
这个差异点非常容易造成Oracle迁移到PG后业务逻辑出现重大差异。
1 总结
先放总结
- Oracle：在PLSQL内如果语句执行失败，进入异常处理程序后，PL程序正常退出。那么在执行失败语句前面的SQL
不会回滚，执行结果都正常提交了。 - Postgresql：在PLPGSQL内如果语句执行失败，进入异常处理程序后，PL正常退出。
那么整个PL内的所有SQL自动回滚，因为：- PG不支持PL内写SAVEPOINT （Oracle在每个语句前有隐式的savepoint）
- PL整体包装在一个大事务内。
Oracle11g
2 测试用例Oracle11g
代码语言：javascript复制drop table employees;
create table employees(employee_id int, salary int, raise int, job_id varchar(16), last_name varchar(16), first_name varchar(16));
insert into employees values (1, 1000, 60, 'ST_CLERK', 'Geller', 'Rose');
insert into employees values (2, 2000, 100, 'ST_CLERK', 'Green', 'Rachel');
insert into employees values (3, 4000, 400, 'ST_AAAAA', 'Bing', 'Chanller');
insert into employees values (4, 6000, 630, 'AD_CLERK', 'Geller', 'Rose');
insert into employees values (5, 7000, 120, 'AD_DSFWR', 'Green', 'Rachel');
insert into employees values (6, 8000, 410, 'AD_AAAAA', 'Bing', 'Chanller');
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 = 1;
INSERT INTO emp_name (employee_id, last_name, salary) VALUES (100, emp_lastname, emp_salary);
INSERT INTO emp_name (employee_id, last_name, salary) VALUES (emp_id, emp_lastname, emp_salary);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('Insert was rolled back');
END;
/
select * from emp_name;结果
3 测试用例Postgresql14
代码语言：javascript复制drop table employees;
create table employees(employee_id int, salary int, raise int, job_id varchar(16), last_name varchar(16), first_name varchar(16));
insert into employees values (1, 1000, 60, 'ST_CLERK', 'Geller', 'Rose');
insert into employees values (2, 2000, 100, 'ST_CLERK', 'Green', 'Rachel');
insert into employees values (3, 4000, 400, 'ST_AAAAA', 'Bing', 'Chanller');
insert into employees values (4, 6000, 630, 'AD_CLERK', 'Geller', 'Rose');
insert into employees values (5, 7000, 120, 'AD_DSFWR', 'Green', 'Rachel');
insert into employees values (6, 8000, 410, 'AD_AAAAA', 'Bing', 'Chanller');
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);
select * from emp_name;
DO $$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 = 1;
INSERT INTO emp_name (employee_id, last_name, salary) VALUES (100, emp_lastname, emp_salary);
INSERT INTO emp_name (employee_id, last_name, salary) VALUES (emp_id, emp_lastname, emp_salary);
EXCEPTION
WHEN others THEN
raise notice 'Insert was rolled back';
END$$;
select * from emp_name;执行结果
