Oracle使用in语句不能超过1000问题的解决办法

目录
  • 前言
  • 我的解决方案是:
    • 一、建立临时表
      • 1、ON COMMIT DELETE ROWS
      • 2、ON COMMIT PRESERVE ROWS
    • 二、使用in() or in()
  • 总结

前言

在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

它表示临时表的内容可以跨事务而存在,不过,当该会话结束时,临时表的暂时段将随着会话的结束而被丢弃,临时表中的数据自然也就随之丢弃。但是临时表的结构以及元数据还存储在用户的数据字典中。如果临时表完成它的使命后,最好删除临时表,否则数据库会残留很多临时表的表结构和元数据。

建立临时表之后,in语句里面就可以使用子查询,这样就不会有超过1000报错的问题了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使用in语句不能超过1000问题解决的文章就介绍到这了,更多相关Oracle in语句不能超过1000内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • Oracle使用in语句不能超过1000问题的解决办法

    目录 前言 我的解决方案是: 一.建立临时表 1.ON COMMIT DELETE ROWS 2.ON COMMIT PRESERVE ROWS 二.使用in() or in() 总结 前言 在oracle中,使用in方法查询记录的时候,如果in后面的参数个数超过1000个,那么会发生错误,JDBC会抛出“java.sql.SQLException: ORA-01795: 列表中的最大表达式数为 1000”这个异常. 我的解决方案是: 一.建立临时表 ORACLE临时表有两种类型:会话级的临时表

  • oracle执行update语句时卡住问题分析及解决办法

    问题 开发的时候debug到一条update的sql语句时程序就不动了,然后我就在plsql上试了一下,发现plsql一直在显示正在执行,等了好久也不出结果.但是奇怪的是执行其他的select语句却是可以执行的. 原因和解决方法 这种只有update无法执行其他语句可以执行的其实是因为记录锁导致的,在oracle中,执行了update或者insert语句后,都会要求commit,如果不commit却强制关闭连接,oracle就会将这条提交的记录锁住.由于我的java程序中加了事务,之前debug

  • Linux下Oracle中SqlPlus时上下左右键乱码问题的解决办法

    window下的sqlplus可以通过箭头键,来回看历史命令,用起来非常的方便. 但是在Linux下,会出现各种乱码,非常不方便,如下图所示,每次打错一个字符就需要重新打一遍. 解决办法:rlwrap 可以用来支持Oracle下sqlplus历史命令的回调功能,提高效率. 解决过程: 1.首先下载rlwrap和readline: readline-6.3.tar.gz rlwrap-0.30.tar.gz 2.安装readline包 tar -zxvf readline-6.3.tar.gz c

  • oracle远程连接服务器出现 ORA-12170 TNS:连接超时 解决办法

    错误描述:oracle远程连接服务器出现 ORA-12170 TNS:连接超时 错误检查:有很多是oracle自身安装的问题,但是我这里服务器配置正常,监听正常,服务正常,远程可以ping通服务器. 这里主要是防火墙问题,解决办法: (1)关闭防火墙: (2)在防火墙中添加,orcale端口1521例外.

  • MySQL错误“Specified key was too long; max key length is 1000 bytes”的解决办法

    经过查询才知道,是Mysql的字段设置的太长了,于是我把这两个字段的长度改了一下就好了. 建立索引时,数据库计算key的长度是累加所有Index用到的字段的char长度后再按下面比例乘起来不能超过限定的key长度1000: latin1 = 1 byte = 1 character uft8 = 3 byte = 1 character gbk = 2 byte = 1 character 举例能看得更明白些,以GBK为例: CREATE UNIQUE INDEX `unique_record`

  • Oracle报错:ORA-28001:口令已失效解决办法

    目录 一.链接Oracle报错 :ORA-28001:口令已失效 二.查看数据库密码默认有效期: 三.解决方式一:修改用户密码 四.解决方式二:修改默认有效期 总结 一.链接Oracle报错 :ORA-28001:口令已失效 解决办法  原因:Oracle11G创建用户时缺省密码过期限制是180天(即6个月), 如果超过180天用户密码未做修改则该用户无法登录 解决方式: 方式一: 在密码将要过期或已经过期时进行修改密码,密码修改后该用户可正常连接数据库 方式二: 将口令有效期默认值180天修改

  • sql in查询元素超过1000条的解决方案

    目录 一.场景描述 二.解决方案 1.方案一:核心思路是,将集合拆分,使用or 连接. 2.方案二:用子查询(临时表)+关联查询 总结 一.场景描述 查询机构下的人员,要同时查询出该机构及其所有下级机构的人员:比如查询北京市,不仅要查询出归属于北京市的人员,还要查询归属于出朝阳区.西城区.海淀区等等的人员. 对于这个需求,有以下几种解决思路: 1.之前的系统,他们的机构编码本身有关联,比如北京编码是001的话,昌平区的编码就是0012:这样的话,他们查询可以用like.这种方法的问题是like查

  • oracle常用sql语句

    正在看的ORACLE教程是:oracle常用sql语句.SQL*Plus system/manager 2.显示当前连接用户 SQL> show user 3.查看系统拥有哪些用户 SQL> select * from all_users; 4.新建用户并授权 SQL> create user a identified by a;(默认建在SYSTEM表空间下) SQL> grant connect,resource to a; 5.连接到新用户 SQL> conn a/a

  • MySQL中实现插入或更新操作(类似Oracle的merge语句)

    如果需要在MySQL中实现记录不存在则insert,不存在则update操作.可以使用以下语句: 更新一个字段: INSERT INTO tbl (columnA,columnB,columnC) VALUES (1,2,3) ON DUPLICATE KEY UPDATE columnA=IF(columnB>0,1,columnA) 更新多个字段: INSERT INTO tbl (columnA,columnB,columnC) VALUES (1,2,3) ON DUPLICATE KE

  • Oracle中SQL语句连接字符串的符号使用介绍

    Oracle中SQL语句连接字符串的符号为|| 复制代码 代码如下: select catstr(tcdm) || (',') from T_YWCJ_RWCJR where cjrjh='009846' and rwid='12050' and jsdm='CJY' 拼接成一条数据并连接一个","

随机推荐