当前位置:首页 > Oracle > 正文内容

因数据类型不规范导致查询结果异常的案例分享

管理员2年前 (2018-06-27)Oracle276

某报表开发人员,说遇到了一个比较诡异的问题,对两张表做关联查询时,部分数据行有重复的情况发生,但是,先把一张表的数据查询出来,再去到第二张表中查询,数据就是正确的,接到这个问题后,第一反应,应该是数据变化过快,两次查询的基础数据不一样,遂与该同事进行沟通,得到的回复是,这是一个报表系统,并且新做的报表还没有发布,数据也是从后台手工生成,不存在数据变化的情况,进行再分析。

首先分析用户提供的语句,提供了两个语句,一个是关联查询,一个是先查询第一表的数据,再到第二表中查询,语句如下(因数据涉密,现已做脱敏处理):

SQL> select t1.id, t1.v, t2.remark
  2  from t1, t2
  3  where t1.v between t2.min_value and t2.max_value;

SQL> declare
  2      v_value int;
  3      v_count int;
  4  begin
  5      select v into v_value from t1 where id = 12;
  6      select count(*) into v_count
  7      from t2
  8      where v_value between t2.min_value and t2.max_value;
  9      dbms_output.put_line('v_count = ' || v_count);
 10  end;
 11  /

第一个语句是关联查询,但出的结果有翻倍情况,第二个语句仅仅是打印出匹配的行数,两个语句从表面上看着,并无异常,desc 两张表的结构,发现了其中的端倪。

SQL> desc t1;
Name Type         Nullable Default
---- ------------ -------- -------
ID   INTEGER      Y
V    VARCHAR2(10) Y
SQL> desc t2;
Name      Type         Nullable Default
--------- ------------ -------- -------
MIN_VALUE VARCHAR2(10) Y
MAX_VALUE VARCHAR2(10) Y
REMARK    VARCHAR2(30) Y

两张表的所有的数值内容,全部定义成了VARCHAR类型,我们知道,当两个字符串比较大小时,会逐个字符比较,这样就会有 ‘11’ < ‘9’ 这样的结果,对实际的查询结果分析后,也确实是这个原因,把列的类型调整后,返回结果正确。

第二个语句,由于在定义变量时,使用的是数值型(这里用的是int)类型,select into语句,如果查询出来的结果,与变量的类型不一致,会把结果转换成变量一致,如果转换失败,则会抛出异常,也就是常说的“隐性数据转换”。第二步查询,也就是between 比较操作,三个值的类型也不一样,这里也存在一个隐性数据转换,转换的原则是如三个值中存在至少一个数值型,会尝试把另外两个(或一个)非数值型转换成数值型,同样,如果转换失败,则抛出异常。

下面模拟一个场景,还原当时的状态,步骤如下:

1、创建两个测试表,并填充部分测试数据

SQL> create table t1(id int, v varchar(10), v_int int);
Table created
SQL> create table t2
  2  (
  3    min_value VARCHAR2(10),
  4    max_value VARCHAR2(10),
  5    min_value_int int,
  6    max_value_int int,
  7    remark    VARCHAR2(30)
  8  );
Table created
SQL> begin
  2    insert into t1 values(12, 9, 9);
  3    insert into t1 values(13, 11, 11);
  4    insert into t2 values(-1, 9, -1, 9, '0-9');
  5    insert into t2 values(10, 20, 10, 20, '11-20');
  6    insert into t2 values(21, 40, 21, 40, '21-40');
  7    insert into t2 values(41, 99, 41, 99, '41-99');
  8  end;
  9  /

2、原来的查询语句示例。

SQL> select t1.id, t1.v, t2.remark
  2  from t1, t2
  3  where t1.v between t2.min_value and t2.max_value;
   ID V          REMARK
----- -------- ------------------------------
   12 9          0-9
   12 9          41-99
   13 11         0-9
   13 11         11-20

3、调整后的语句示例

SQL> select t1.id, t1.v_int, t2.remark
  2  from t1, t2
  3  where t1.v_int between t2.min_value_int and t2.max_value_int;
  4
   ID  V_INT     REMARK
----- -------- ------------------------------
   12 9          0-9
   13 11         11-20
打赏 支付宝打赏 微信打赏
    扫描二维码至手机访问

    扫描二维码推送至手机访问。

    版权声明:本文由卖水果的net发布,如需转载请注明出处。

    转载请注明出处:http://www.msgde.net/oracle/oracle_error_datatype.html

    分享给朋友:

    发表评论

    访客

    ◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。