Oracle 10：INSERT进入视图时难以理解的行为？

我们在Oracle DB Version 10.2.0.5.8中有一个视图.该视图使用INSTEAD OF触发器.

这是触发器的代码：

CREATE OR REPLACE TRIGGER V1_T1_BIUD
  INSTEAD OF INSERT OR UPDATE OR DELETE
  ON V1_T1
  FOR EACH ROW
DECLARE
  AnyId   NUMBER;
BEGIN
  IF INSERTING THEN
    INSERT INTO Table T1 (
       F1,F2,F3,F4,F5
    ) VALUES (
       :new.F1,:new.F2,:new.F3,:new.F4,:new.F5
    );
  ELSIF UPDATING THEN
    UPDATE T1 SET F1 = :new.F1,F2 = :new.F2,F3 = :new.F3,F4 = :new.F4,F5 = :new.F5
    WHERE F1 = :old.F1;
  ELSIF DELETING THEN
    DELETE FROM T1
    WHERE F1 = :old.F1;
  END IF;
END;
/

这是一个INSERT语句示例：

INSERT INTO V_T1 (
  F1,F5
)
SELECT A.V,A.S,A.F,A.T,A.Z
FROM (
  SELECT 'E' V,'N' S,'ABC' F,'E' T,'E' Z FROM DUAL UNION ALL 
  SELECT 'E','Y','QWE','O','E'   FROM DUAL UNION ALL
  SELECT 'I','GHJ','I','I'   FROM DUAL           
) A
ORDER BY 1,2,3;
COMMIT;

注意select的末尾的ORDER BY子句.这个INSERT语句的结果是这样的：

F1 F2 F3  F4 F5
---------------
E  N  ABC I  I 
E  Y  QWE I  I 
I  Y  GHJ I  I

如您所见,第4列和第5列未正确填充所有其他数据行中最后一个数据行的值.

如果我们像这样更改INSERT语句：

INSERT INTO V_T1 (
  F1,3,4,5;
COMMIT;

结果是这样的：

F1 F2 F3  F4 F5
---------------
E  N  ABC E  E 
E  Y  QWE O  E 
I  Y  GHJ I  I

再次,请注意ORDER BY子句,它现在命令所有行而不是第一个insert语句中的前三行.

编辑：如果省略ORDER BY子句,结果也符合预期(例如,如示例2所示).

有人可以向我解释这种行为吗？

P. S.关于评论：

我今天没有时间调查或提供有关此主题的更多信息.我将在我们的数据库上创建一个完整的示例,并在接下来的几天内将其发布到此处.感谢您的耐心等待！

解决方法

create table t1 (f1 varchar2(2),f2 varchar2(2),f3 varchar2(3),f4 char(2),f5 char(2));

create view v1_t1 as select * from t1;

…而不是完全如问题所示的触发器.

根据DBMS_OUTPUT,触发器内部的新值是错误的,但是受列数据类型影响的是Oracle认为只能找到的东西.

它仍然发生在11.2.0.3(Linux)中.有趣的是,如果我将UNION ALL更改为UNION,我会得到略微不同的结果;在10g中,两列最终为null,在11g中它们有x：

insert into v1_t1 (
  F1,A.Z
FROM (
    SELECT 'E' V,'E' Z FROM DUAL UNION
    SELECT 'E','E'   FROM DUAL UNION
    SELECT 'I','I'   FROM DUAL
) A
ORDER BY 1,3;

3 rows created.

select * from v1_t1;

F1 F2 F3  F4 F5
-- -- --- -- --
E  N  ABC x  x
E  Y  QWE x  x
I  Y  GHJ x  x

…这甚至更奇怪 – 看起来可能修复了其他一些bug对这个有点影响.

所以不是一个真正的答案;您需要向Oracle发出服务请求,我相当确定他们只是告诉您删除订单,因为它没有任何价值,正如您已经知道的那样.

对于Thilo;没有任何订单的计划(11g)：

----------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |       |     3 |    51 |     9  (34)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | V1_T1 |       |       |            |          |
|   2 |   VIEW                   |       |     3 |    51 |     9  (34)| 00:00:01 |
|   3 |    SORT UNIQUE           |       |     3 |       |     9  (78)| 00:00:01 |
|   4 |     UNION-ALL            |       |       |       |            |          |
|   5 |      FAST DUAL           |       |     1 |       |     2   (0)| 00:00:01 |
|   6 |      FAST DUAL           |       |     1 |       |     2   (0)| 00:00:01 |
|   7 |      FAST DUAL           |       |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

并按顺序计划1,3或1,5 – 相同的计划哈希值(11g)：

----------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |       |     3 |    51 |    10  (40)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | V1_T1 |       |       |            |          |
|   2 |   SORT ORDER BY          |       |     3 |    51 |    10  (40)| 00:00:01 |
|   3 |    VIEW                  |       |     3 |    51 |     9  (34)| 00:00:01 |
|   4 |     SORT UNIQUE          |       |     3 |       |     9  (78)| 00:00:01 |
|   5 |      UNION-ALL           |       |       |       |            |          |
|   6 |       FAST DUAL          |       |     1 |       |     2   (0)| 00:00:01 |
|   7 |       FAST DUAL          |       |     1 |       |     2   (0)| 00:00:01 |
|   8 |       FAST DUAL          |       |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

我看到从其他表中选择相同类型的损坏,但前提是子查询中的结果在被排序之前已经联合;虽然然后我得到空值而不是x. (我简单地想知道x是否来自双自身,但虚拟是大写的X,这显示小写的x).

在@ Annjawn的注释之后,将插入从V1_T1更改为T1中的直接插入工作正常(即插入了正确的值),并且奇怪地具有相同的计划哈希,即使它显示表名而不是名称列中的视图.同时使用UNION或UNION ALL,以及10gR2和11gR2.我猜,这似乎是工会混淆的触发器.

进一步到数据类型点…视图必须有char列,表不一定,这不是一个惊喜,因为视图上的触发器似乎是问题.如果我使用char列设置表但在视图中将它们转换为varchar2,那么我没有看到问题：

create table t1 (f1 varchar2(2),f5 char(2));

create view v1_t1 as select f1,f2,f3,cast(f4 as varchar(2)) f4,cast(f5 as varchar(2)) f5
from t1;

但如果我这样做,反过来确实表现出问题：

create table t1 (f1 varchar2(2),f4 varchar(2),f5 varchar(2));

create view v1_t1 as select f1,cast(f4 as char(2)) f4,cast(f5 as char(2)) f5
from t1;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!