oracle - 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关键字？

解决方案

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

这篇关于oracle - Oralce fetch first ORA-00933 SQL命令未正确结束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！