ORA-00933 SQL命令未正确结束 INSERT INTO ... SELECT

最近在修改数据库存储过程时,出现了一个ORA-00933错误,

执行的是 INSERT INTO...SELECT 语句,具体语句如下:

INSERT INTO BASP_DX.QLR@GT(BDCDYH, QSZT)
SELECT NVL(e.BDCDYH, ' '), b.LIFECYCLE AS QSZT
FROM DJ_DY as
LEFT JOIN DJ_XGDJGL d
ON d.ZSLBH = a.SLBH
LEFT JOIN DJ_DJB e
ON e.SLBH = d.FSLBH
AND e.SLBH = '0123456789'
LEFT JOIN DJ_QLRGL f
ON f.SLBH = e.SLBH
AND f.QLRLX = '权利人'
LEFT JOIN DJ_QLRGL b
ON b.SLBH = a.SLBH
AND (b.QLRLX = '抵押权人' OR (b.QLRLX = '抵押人' AND b.QLRID = f.QLRID))
WHERE a.SLBH = '20170318'
AND e.SLBH IS NOT NULL
AND b.QLRID IS NOT NULL
AND (a.LIFECYCLE = '0' OR a.LIFECYCLE IS NULL);

没毛病啊!!!谷狗上得来一篇文章

说造成ORA-00933的可能原因有:

  • 使用了含有ORDER BYINNER JOIN子句的INSERT语句
  • 使用了含有ORDER BYINNER JOIN子句的DELETE语句
  • 使用了含有INNER JOIN子句的UPDATE语句

然鹅,我的语句都没有啊!!!但是这给了我灵感,

会不会是JOIN后面的条件太多了被当成INNER JOIN

(没错,就是瞎猜的,脑洞大没办法~~~)

于是乎,我就傻里傻气的开始注释LEFT JOIN后的条件,

当我注释掉AND (b.QLRLX = '抵押权人' OR (b.QLRLX = '抵押人' AND b.QLRID = f.QLRID))

时,奇迹发生了,不报错了(当然,一下子插入了几千条,吓得小生鼠标都掉了),

接着,我将条件放到WHERE语句后面,成了如下形式:

INSERT INTO BASP_DX.QLR@GT(BDCDYH, QSZT)
SELECT NVL(e.BDCDYH, ' '), b.LIFECYCLE AS QSZT
FROM DJ_DY as
LEFT JOIN DJ_XGDJGL d
ON d.ZSLBH = a.SLBH
LEFT JOIN DJ_DJB e
ON e.SLBH = d.FSLBH
AND e.SLBH = '0123456789'
LEFT JOIN DJ_QLRGL f
ON f.SLBH = e.SLBH
AND f.QLRLX = '权利人'
LEFT JOIN DJ_QLRGL b
ON b.SLBH = a.
-- 条件原来在这里
WHERE a.SLBH = '20170318'
AND e.SLBH IS NOT NULL
-- LEFT JOIN后的条件移动到了这里
AND (b.QLRLX = '抵押权人' OR (b.QLRLX = '抵押人' AND b.QLRID = f.QLRID))
AND (a.LIFECYCLE = '0' OR a.LIFECYCLE IS NULL);

这样便可以运行成功,但是...这是为什么啊!!!Tell me Why???

最后小生想到了我大*,赶快上去提了个问题

经高人点拨,是一种叫做Triangular Join的东西导致的,

大致就是说语句里面有自连接(如上例中的DJ_XGDJGL分别被命名为bf做了关联),

但是这并没有关系,关键是这个自连接的条件里面不能有涉及自连接表的字段进行比较

(上例中的b.QLRID = f.QLRID就不符合这一点,所以执行失败,尝试了一下移除这个条件,

便可以执行了)

总结

会造成ORA-00933的原因分为三种:

  • 使用了含有ORDER BYINNER JOIN子句的INSERTDELETE语句
  • 使用了含有INNER JOIN子句的UPDATE语句
  • 使用了条件中含有自连接表字段比较的Triangular Join

参考链接:

http://www.databasestar.com/ora-00933/

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:4549764300346084350

http://*.com/questions/42870062/ora-00933-sql-command-not-properly-ended-with-insert-into-select-statement

上一篇:Oracle.EntityFrameworkCore使用时报错:ORA-00933:SQL命令未正确结束


下一篇:mybatis支持oracle批量插入