Oralce fetch first ORA-00933 SQL命令未正确结束

项目使用的是MyBatis,之前用的是DB2的数据库,现切换到Oracle时,其中一条SQL语句报错ORA-00933:

select * 
from com_transaction 
where 1=1 
and user_id = ?  
order by id desc 
fetch first 10 rows only

错误:
Caused by: org.springframework.jdbc.BadSqlGrammarException:
Error querying database. Cause: java.sql.SQLException: ORA-00933: SQL 命令未正确结束
The error may exist in conf-mybatis/biz/ComTransactionDao.xml
The error may involve com.hisun.branch.dao.ComTransactionMapper.getComTransactionByParams-Inline
The error occurred while setting parameters
SQL: select * from com_transaction where 1=1 and user_id = ? order by id desc fetch first 10 rows only;
Cause: java.sql.SQLException: ORA-00933: SQL 命令未正确结束
; bad SQL grammar []; nested exception is java.sql.SQLException: ORA-00933: SQL 命令未正确结束

在想会不会是Oracle不支持fetch first rows only关键字?

阅读 8.7k
2 个回答

fetch first 10 rows only,这个是取前十条的记录吧,在oracle 12c中可以这样写,12c之前的版本要结合ronum进行判断,示例代码:

select *
from (
  select t.*, rownum as rn
  from com_transaction t
  where 1=1 
  and user_id = ?  
  order by id desc 
)
where rn <= 10

感谢@刑爱明 的答案,另外补充一下,在MyBatis中,“小于等于”符号必须要用CDATA包含起来

<![CDATA[ 
    where rn <= 10
]]>
撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题
宣传栏