在oracle中用like模糊搜索时如何使其能搜到空值

WBOY

Libérer： 2016-06-07 15:45:09

original

2287 Les gens l'ont consulté

oracle中查询关键字 like，当我用like执行模糊查找是，发现数据量不对，表的结构如下： -- Create table create table NEOERP( ID NUMBER ( 22 ) not null , PRODUCTNAME NVARCHAR2( 100 ) default ' ' , CT_SMP_SPECIFICATION NVARCHAR2( 100 ) default ' '

oracle中查询关键字 like，当我用like执行模糊查找是，发现数据量不对，表的结构如下：

在oracle中用like模糊搜索时如何使其能搜到空值

<span>--</span><span> Create table</span>
<span>create</span> <span>table</span><span> NEOERP
(
  ID                   </span><span>NUMBER</span>(<span>22</span>) <span>not</span> <span>null</span><span>,
  PRODUCTNAME          NVARCHAR2(</span><span>100</span>) <span>default</span> <span>'</span> <span>'</span><span>,
  CT_SMP_SPECIFICATION NVARCHAR2(</span><span>100</span>) <span>default</span> <span>'</span> <span>'</span><span>,
  TYPENAME             NVARCHAR2(</span><span>50</span><span>),
  UNIT                 NVARCHAR2(</span><span>30</span>) <span>default</span> <span>'</span> <span>'</span><span>,
  STORECODE            NVARCHAR2(</span><span>100</span>) <span>default</span> <span>'</span> <span>'</span><span>,
  ROOMCODE             NVARCHAR2(</span><span>100</span>) <span>default</span> <span>'</span> <span>'</span><span>,
  SAMPTMPLID           NVARCHAR2(</span><span>100</span><span>),
  CT_BELONG            NVARCHAR2(</span><span>100</span>) <span>default</span> <span>'</span> <span>'</span><span>,
  CT_MANUFACTURE       NVARCHAR2(</span><span>100</span><span>),
  CT_EXPORTER_PLACE    NVARCHAR2(</span><span>100</span><span>),
  REMARK               NVARCHAR2(</span><span>200</span><span>),
  LOGINDATE            DATE </span><span>default</span><span> sysdate,
  CT_SMP_TYPE          NVARCHAR2(</span><span>100</span><span>),
  SAMPLENAME           NVARCHAR2(</span><span>200</span><span>),
  SAMPLEDESCRIPTION    NVARCHAR2(</span><span>200</span><span>),
  STARTDATE            DATE,
  STATUS               NVARCHAR2(</span><span>2</span>) <span>default</span> <span>'</span><span>F</span><span>'</span><span>,
  REMOVE_DATE          DATE,
  TIMETYPE             NVARCHAR2(</span><span>10</span><span>),
  REPORTDATE           </span><span>NUMBER</span>(<span>22</span><span>),
  TIMEVALUE            </span><span>NUMBER</span>(<span>22</span><span>)
)
tablespace VGSM
  pctfree </span><span>10</span><span>
  initrans </span><span>1</span><span>
  maxtrans </span><span>255</span><span>
  storage
  (
    initial 64K
    minextents </span><span>1</span><span>
    maxextents unlimited
  );
</span><span>--</span><span> Add comments to the columns </span>
comment <span>on</span> <span>column</span><span> NEOERP.PRODUCTNAME
  </span><span>is</span> <span>'</span><span>产品名称</span><span>'</span><span>;
comment </span><span>on</span> <span>column</span><span> NEOERP.CT_SMP_SPECIFICATION
  </span><span>is</span> <span>'</span><span>规格</span><span>'</span><span>;
comment </span><span>on</span> <span>column</span><span> NEOERP.TYPENAME
  </span><span>is</span> <span>'</span><span>类别名称</span><span>'</span><span>;
comment </span><span>on</span> <span>column</span><span> NEOERP.UNIT
  </span><span>is</span> <span>'</span><span>单位</span><span>'</span><span>;
comment </span><span>on</span> <span>column</span><span> NEOERP.STORECODE
  </span><span>is</span> <span>'</span><span>存货编码</span><span>'</span><span>;
comment </span><span>on</span> <span>column</span><span> NEOERP.ROOMCODE
  </span><span>is</span> <span>'</span><span>物料编码</span><span>'</span><span>;
comment </span><span>on</span> <span>column</span><span> NEOERP.SAMPTMPLID
  </span><span>is</span> <span>'</span><span>样品模版id</span><span>'</span><span>;
comment </span><span>on</span> <span>column</span><span> NEOERP.CT_BELONG
  </span><span>is</span> <span>'</span><span>归属地</span><span>'</span><span>;
comment </span><span>on</span> <span>column</span><span> NEOERP.CT_MANUFACTURE
  </span><span>is</span> <span>'</span><span>生产地</span><span>'</span><span>;
comment </span><span>on</span> <span>column</span><span> NEOERP.CT_EXPORTER_PLACE
  </span><span>is</span> <span>'</span><span>出口地</span><span>'</span><span>;
comment </span><span>on</span> <span>column</span><span> NEOERP.REMARK
  </span><span>is</span> <span>'</span><span>备注</span><span>'</span><span>;
comment </span><span>on</span> <span>column</span><span> NEOERP.CT_SMP_TYPE
  </span><span>is</span> <span>'</span><span>样品类型</span><span>'</span><span>;
comment </span><span>on</span> <span>column</span><span> NEOERP.SAMPLENAME
  </span><span>is</span> <span>'</span><span>样品名称</span><span>'</span><span>;
comment </span><span>on</span> <span>column</span><span> NEOERP.SAMPLEDESCRIPTION
  </span><span>is</span> <span>'</span><span>样品描述</span><span>'</span><span>;
comment </span><span>on</span> <span>column</span><span> NEOERP.STARTDATE
  </span><span>is</span> <span>'</span><span>起始时间</span><span>'</span><span>;
comment </span><span>on</span> <span>column</span><span> NEOERP.STATUS
  </span><span>is</span> <span>'</span><span>f可用 状态</span><span>'</span><span>;
comment </span><span>on</span> <span>column</span><span> NEOERP.REMOVE_DATE
  </span><span>is</span> <span>'</span><span>删除时间</span><span>'</span><span>;
comment </span><span>on</span> <span>column</span><span> NEOERP.TIMETYPE
  </span><span>is</span> <span>'</span><span>年月日</span><span>'</span><span>;
comment </span><span>on</span> <span>column</span><span> NEOERP.REPORTDATE
  </span><span>is</span> <span>'</span><span>要求报告日期</span><span>'</span><span>;
comment </span><span>on</span> <span>column</span><span> NEOERP.TIMEVALUE
  </span><span>is</span> <span>'</span><span>有效期</span><span>'</span><span>;
</span><span>--</span><span> Create/Recreate primary, unique and foreign key constraints </span>
<span>alter</span> <span>table</span><span> NEOERP
  </span><span>add</span> <span>constraint</span> PK_ERP_ID <span>primary</span> <span>key</span><span> (ID)
  using </span><span>index</span><span> 
  tablespace VGSM
  pctfree </span><span>10</span><span>
  initrans </span><span>2</span><span>
  maxtrans </span><span>255</span><span>
  storage
  (
    initial 64K
    minextents </span><span>1</span><span>
    maxextents unlimited
  );</span>

Copier après la connexion

在oracle中用like模糊搜索时如何使其能搜到空值

当我用SELECT * FROM NEOERP WHERE ROOMCODE LIKE '%%',来查询时候，数据一直存在问题，查处的数据量与期望的数据量不同，

经查，是当roomcode为null造成的，

当我用SELECT * FROM NEOERP WHERE ROOMCODE IS NULL 来查询发现这个查询的结果是我所缺少的数据信息。

null的含义，在我们不知道具体有什么数据，也即未知，称他为空，oracle中，含有空值的表列长度为零。

等价于没有任何值，是未知数，null与0，空字符串，空格不同，对空值做运算，结果仍然是空值，oracle提供了处理空值函数nvl，比较时候用 is null或者 is not null.

通过以上说明，通过SELECT * FROM NEOERP WHERE ROOMCODE LIKE '%%'查询不到值为null的数据。

对上面sql语句进行修改后，既可以完成所需功能：

SELECT * FROM NEOERP WHERE NVL(ROOMCODE,0) LIKE '%%';

注释：

当ROOMCODE为null时，NVL(ROOMCODE,0)的返回值为0，而0正好符合匹配LIKE '%%'这个匹配模式，所以，ROOMCODE为null的数据行可以被查询出来。