實(shí)例解決Oracle使用in語(yǔ)句不能超過(guò)1000問(wèn)題

本篇文章給大家?guī)?lái)了關(guān)于Oracle的相關(guān)知識(shí)，其中主要介紹了關(guān)于使用in方法查詢(xún)記錄的時(shí)候，如果in后面的參數(shù)個(gè)數(shù)超過(guò)1000個(gè)，那么會(huì)發(fā)生錯(cuò)誤，JDBC會(huì)拋出“java.sql.SQLException: ORA-01795: 列表中的最大表達(dá)式數(shù)為 1000”這個(gè)異常。下面一起來(lái)看一下應(yīng)該怎么解決，希望對(duì)大家有幫助。

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

在oracle中，使用in方法查詢(xún)記錄的時(shí)候，如果in后面的參數(shù)個(gè)數(shù)超過(guò)1000個(gè)，那么會(huì)發(fā)生錯(cuò)誤，JDBC會(huì)拋出“java.sql.SQLException: ORA-01795: 列表中的最大表達(dá)式數(shù)為 1000”這個(gè)異常。

我的解決方案是：

一、建立臨時(shí)表

ORACLE臨時(shí)表有兩種類(lèi)型：會(huì)話級(jí)的臨時(shí)表和事務(wù)級(jí)的臨時(shí)表。

1、ON COMMIT DELETE ROWS

它是臨時(shí)表的默認(rèn)參數(shù)，表示臨時(shí)表中的數(shù)據(jù)僅在事務(wù)過(guò)程（Transaction）中有效，當(dāng)事務(wù)提交（COMMIT）后，臨時(shí)表的暫時(shí)段將被自動(dòng)截?cái)啵═RUNCATE），但是臨時(shí)表的結(jié)構(gòu) 以及元數(shù)據(jù)還存儲(chǔ)在用戶(hù)的數(shù)據(jù)字典中。如果臨時(shí)表完成它的使命后，最好刪除臨時(shí)表，否則數(shù)據(jù)庫(kù)會(huì)殘留很多臨時(shí)表的表結(jié)構(gòu)和元數(shù)據(jù)。

2、ON COMMIT PRESERVE ROWS

它表示臨時(shí)表的內(nèi)容可以跨事務(wù)而存在，不過(guò)，當(dāng)該會(huì)話結(jié)束時(shí)，臨時(shí)表的暫時(shí)段將隨著會(huì)話的結(jié)束而被丟棄，臨時(shí)表中的數(shù)據(jù)自然也就隨之丟棄。但是臨時(shí)表的結(jié)構(gòu)以及元數(shù)據(jù)還存儲(chǔ)在用戶(hù)的數(shù)據(jù)字典中。如果臨時(shí)表完成它的使命后，最好刪除臨時(shí)表，否則數(shù)據(jù)庫(kù)會(huì)殘留很多臨時(shí)表的表結(jié)構(gòu)和元數(shù)據(jù)。

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

建立臨時(shí)表之后，in語(yǔ)句里面就可以使用子查詢(xún)，這樣就不會(huì)有超過(guò)1000報(bào)錯(cuò)的問(wèn)題了

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

二、使用in() or in()

官方說(shuō)： 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） 也就是元組,語(yǔ)法如下：

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

比如我們想要從用戶(hù)表里通過(guò)用戶(hù)id 查詢(xún)用戶(hù)信息可以這樣寫(xiě)：

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

上面的語(yǔ)句其實(shí)等同于：

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')

大家的工程多數(shù)會(huì)用ORM框架如MyBatis 我們可以借助MyBatis的foreach 原來(lái)是這寫(xiě):

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

現(xiàn)在改成：

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

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