oracle300万条数据查询,oracle 数据库子查询超过1000条数据解决方案

查询出来的结果>1000条

select vl.UNIT_CODE

from VC_LEVEL vl

where vl.UNIT_TYPE = 0

and vl.VALID_STATUS = 1

and vl.LEVEL_NO <= 3

start with vl.ID_VC_LEVEL = 694

connect by prior vl.ID_VC_LEVEL = vl.PARENT_ORG_ID)；

放进一个list  aa 里面

select doc.id_vc_doc_version_info,

doc.doc_ver_code,

doc.doc_ver_name,

doc.org_code,

doc.status

from vc_doc_version_info doc

where 1 = 1 and doc.status = ‘1‘

and doc.id_vc_doc_type=‘1‘

and exists (select 1

from vc_doc_rel_area area

where area.id_vc_doc_version_info = doc.id_vc_doc_version_info

and area.org_code in( aa )

);

解决方案

sb.append(" and exists(select 1 from VcDocRelArea area where area.idVcDocVersionInfo=doc.idVcDocVersionInfo " );

// sb.append(" and area.orgCode in ( ");

String[] strs1=orgCodeList.toArray(new String[orgCodeList.size()]);

sb.append(createIdsInHqlAllowEmpty(strs1,"area.orgCode"));

sb.append("))");

工具类

public static String createIdsInHqlAllowEmpty(String[] ids, String propertyStr) {

if(ids.length<=1000){

String idStr = createIds4Hql(ids);

StringBuffer hqlBuffer = new StringBuffer("");

if (StringUtils.isNotEmpty(idStr)) {

hqlBuffer.append(" and " + propertyStr + " in ").append(idStr);

}

return hqlBuffer.toString();

}else{

StringBuffer hqlBuffer = new StringBuffer("");

hqlBuffer.append(" and ( ");

int num = ids.length/1000;

int remain = ids.length%1000;

for(int i=0;i<=num;i++){

String[] newIds = null;

if(i!=num){

newIds = Arrays.copyOfRange(ids, i*1000, (i+1)*1000);

}else{

newIds = Arrays.copyOfRange(ids, i*1000, i*1000+remain);

}

String idStr = createIds4Hql(newIds);

if (StringUtils.isNotEmpty(idStr)) {

if(i==0){

hqlBuffer.append(propertyStr + " in ").append(idStr);

}else{

hqlBuffer.append(" or " + propertyStr + " in ").append(idStr);

}

}

}

hqlBuffer.append(")");

return hqlBuffer.toString();

}

}

public static String createIds4Hql(String[] ids) {

StringBuffer idBuffer = new StringBuffer("");

String idsHql = null;

StringBuffer hqlBuffer = new StringBuffer("");

if (ids!=null&&ids.length>0) {

for (String id : ids) {

idBuffer.append("‘" + id + "‘,");

}

idsHql = StringUtils.substringBeforeLast(idBuffer.toString(), ",");

hqlBuffer.append(" (").append(idsHql).append(")");

}

return hqlBuffer.toString();

}

oracle   数据库一种新发现的写法 这种写法超过1000条也不会报错

SELECT t.* FROM nbz_policy_main t WHERE (t.policyno , 1) in ((‘1‘,1),(‘1‘,1));

原文：https://www.cnblogs.com/fyanhui/p/12054567.html