實例解決Oracle使用in語句不能超過1000問題

本篇文章給大家帶來了關于Oracle的相關知識，其中主要介紹了關于使用in方法查詢記錄的時候，如果in后面的參數個數超過1000個，那么會發生錯誤，JDBC會拋出“java.sql.SQLException: ORA-01795: 列表中的最大表達式數為 1000”這個異常。下面一起來看一下應該怎么解決，希望對大家有幫助。

推薦教程：《Oracle視頻教程》

在oracle中，使用in方法查詢記錄的時候，如果in后面的參數個數超過1000個，那么會發生錯誤，JDBC會拋出“java.sql.SQLException: ORA-01795: 列表中的最大表達式數為 1000”這個異常。

我的解決方案是：

一、建立臨時表

ORACLE臨時表有兩種類型：會話級的臨時表和事務級的臨時表。

1、ON COMMIT DELETE ROWS

它是臨時表的默認參數，表示臨時表中的數據僅在事務過程（Transaction）中有效，當事務提交（COMMIT）后，臨時表的暫時段將被自動截斷（TRUNCATE），但是臨時表的結構 以及元數據還存儲在用戶的數據字典中。如果臨時表完成它的使命后，最好刪除臨時表，否則數據庫會殘留很多臨時表的表結構和元數據。

2、ON COMMIT PRESERVE ROWS

它表示臨時表的內容可以跨事務而存在，不過，當該會話結束時，臨時表的暫時段將隨著會話的結束而被丟棄，臨時表中的數據自然也就隨之丟棄。但是臨時表的結構以及元數據還存儲在用戶的數據字典中。如果臨時表完成它的使命后，最好刪除臨時表，否則數據庫會殘留很多臨時表的表結構和元數據。

create global temporary table test_table  (id varchar2(50), name varchar2(10))  on commit preserve rows; --創建臨時表（當前會話生效）  --添加數據 insert into test_table VALUES('ID001', 'xgg'); insert into test_table VALUES('ID002', 'xgg2');  select * from test_table; --查詢數據  TRUNCATE TABLE test_table; --清空臨時表數據 DROP TABLE test_table; --刪除臨時表

建立臨時表之后，in語句里面就可以使用子查詢，這樣就不會有超過1000報錯的問題了

select * from table_name where id in(select id from test_table);

二、使用in() or in()

官方說： A comma-delimited list of expressions can contain no more than 1000 expressions. A comma-delimited list of sets of expressions can contain any number of sets, but each set can contain no more than 1000 expressions

這里使用oracle tuple（ A comma-delimited list of sets of expressions） 也就是元組,語法如下：

SELECT * FROM TABLE_NAME WHERE (1, COLUMN_NAME) IN  ((1, VALUE_1),  (1, VALUE_2),  ... ... ... ... (1, VALUE_1000), (1, VALUE_1001));

比如我們想要從用戶表里通過用戶id 查詢用戶信息可以這樣寫：

select * from user u where (1, u.id) in ((1, 'id001'),(1,'id002'),(1,'id003'))

上面的語句其實等同于：

select * from user u where (1=1 and u.id='id001') or (1=1 and u.id='id002') or (1=1 and u.id='id003')

大家的工程多數會用ORM框架如MyBatis 我們可以借助MyBatis的foreach 原來是這寫:

where u.id in <foreach collection="userIds" item="item" separator="," open="(" close=")" index=""> 	#{item} </foreach>

現在改成：

where (1, u.id) in <foreach collection="userIds" item="item" separator="," open="(" close=")" index=""> 	(1, #{item}) </foreach>

推薦教程：《Oracle視頻教程》