没有矛盾,但必须准确读取文档e .g。
CREATE TABLE t
(col NUMBER(1) NOT NULL)
/
Table created.
CREATE PROCEDURE insert_t1
AS
BEGIN
INSERT INTO t
(col)
SELECT 1 FROM dual
UNION ALL
SELECT 2 FROM dual;
INSERT INTO t
(col)
SELECT 9 FROM dual
UNION ALL
SELECT 10 FROM dual;
END;
/
Procedure created.
SELECT col
FROM t
/
no rows selected.
INSERT INTO t
SELECT 9 FROM dual
UNION ALL
SELECT 10 FROM dual
/
INSERT INTO t
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
SELECT col
FROM t
/
no rows selected.
这说明,假设插入两个记录的尝试中指定的顺序,一个DML语句回滚到执行语句之前建立的保存点含蓄,既不记录在数据库中存在。如果我们再继续:
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
CURSOR csr
IS
SELECT col
FROM t
ORDER BY col;
BEGIN
BEGIN
insert_t1;
EXCEPTION
WHEN OTHERS THEN
FOR rec IN csr LOOP
dbms_output.put_line('COL: ' || rec.col);
END LOOP;
RAISE;
END;
END;
/
COL: 1
COL: 2
DECLARE
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at line 15
这表明,如果你退出存储子程序有未处理的异常,Oracle不会做任何回滚由第一插入语句插入记录仍然在表中。但是,如上所述,当直接执行DML时,整个第二个插入语句已被回滚到第二个语句执行之前建立的隐式保存点。
但是,如果我们然后尝试查询表。
SELECT col
FROM t
/
no rows selected.
这表明,如果您有未处理的异常退出的匿名块甲骨文确实做回滚。这将再次是在匿名块执行之前建立的隐式保存点。