Example to solve the problem that Oracle cannot use more than 1000 in statements

This article brings you relevant knowledge about Oracle, which mainly introduces how to use the in method to query records. If the number of parameters after in exceeds 1000, what will happen? Error, JDBC will throw "java.sql.SQLException: ORA-01795: The maximum number of expressions in the list is 1000". Let's take a look at how to solve it. I hope it will be helpful to everyone.

Recommended tutorial: "Oracle Video Tutorial"

In oracle, use the in method to query records When, if the number of parameters after in exceeds 1000, an error will occur and JDBC will throw the exception "java.sql.SQLException: ORA-01795: The maximum number of expressions in the list is 1000".

My solution is:

1. Create a temporary table

ORACLE temporary table has two types: session-level temporary Tables and transaction-level temporary tables.

1. ON COMMIT DELETE ROWS

It is the default parameter of the temporary table, which means that the data in the temporary table is only valid during the transaction process (Transaction). When the transaction is submitted (COMMIT), The temporary segment of the temporary table will be automatically truncated (TRUNCATE), but the structure and metadata of the temporary table are still stored in the user's data dictionary. If the temporary table has completed its mission, it is best to delete the temporary table, otherwise the database will retain a lot of table structure and metadata of the temporary table.

2. ON COMMIT PRESERVE ROWS

It means that the contents of the temporary table can exist across transactions. However, when the session ends, the temporary segment of the temporary table will end with the end of the session. is discarded, the data in the temporary table will naturally be discarded. But the structure and metadata of the temporary table are also stored in the user's data dictionary. If the temporary table has completed its mission, it is best to delete the temporary table, otherwise the database will retain a lot of table structure and metadata of the temporary table.

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; --删除临时表

After creating the temporary table, you can use subqueries in the in statement, so that there will be no problem of more than 1000 error reports

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

2. Use in() or in()

Officially said: 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

The oracle tuple (A comma-delimited list of sets of expressions) is used here, which is a tuple. The syntax is as follows:

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

For example, we want to To query user information through user id from the user table, you can write like this:

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

The above statement is actually equivalent to:

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

Most of our projects will use ORM frameworks such as MyBatis. We can use MyBatis The foreach was originally written like this:

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

Now it is changed to:

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

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of Example to solve the problem that Oracle cannot use more than 1000 in statements. For more information, please follow other related articles on the PHP Chinese website!