一起ORACLE数据库中数据查询结果不一致问题的排查过程

简介: 一、问题描述 在某软件开发项目中,需要在ORACLE数据库中建立十张类型相同的员工信息表tb_employeeinfo0~tb_employeeinfo9,并建立向这十张表中插入数据的存储过程。

一、问题描述
在某软件开发项目中,需要在ORACLE数据库中建立十张类型相同的员工信息表tb_employeeinfo0~tb_employeeinfo9,并建立向这十张表中插入数据的存储过程。ORACLE数据库安装在Linux操作系统下。
为了操作上的方便性,开发人员在PL/SQL Developer软件(ORACLE数据库开发软件)上实现了建表和建存储过程的操作。之后,开发人员利用SQL语句在在PL/SQL Developer软件上实现了向某个数据表中插入数据的操作。利用select语句查询到数据被成功插入到数据库中。
一段时间之后,该开发人员在Linux下以命令行方式登录到数据库中,并利用select语句从员工信息表中查询数据,发现数据条数为0。“难道是数据被删除了?”该开发人员一头雾水。他在PL/SQL Developer软件上利用select语句从员工信息表中查询数据,发现数据是存在的。
到底是哪里出了问题呢?

二、问题排查
我们在开发小组的自测环境上还原了问题出现的整个过程。下面让我们一步一步来看。
员工信息表的建表语句如下:

-- tb_employeeinfo0~9
begin
     declare i int;tmpcount int;tbname varchar2(50);strsql varchar2(1000);
     begin
         i:=0;
         while i<10 loop
         begin
             tbname := 'tb_employeeinfo'||to_char(i);
             i := i+1;

             select count(1) into tmpcount from user_tables where table_name = Upper(tbname);
             if tmpcount>0 then
             begin
                 execute immediate 'drop table '||tbname;
                 commit;
             end;
             end if;
             strsql := 'create table '||tbname||
             '(
                  employeeno      varchar2(10)  not null,         -- employee number
                  employeeage     int           not null          -- employee age
              )';
             execute immediate strsql;   
             strsql := 'begin 
                  execute immediate ''drop index idx1_'||tbname || ' '''
                  || ';exception when others then null;
                  end;';
             execute immediate strsql;

             execute immediate 'create unique index idx1_'||tbname||' on '||tbname||'(employeeno)';

         end;
         end loop;
     end;
end;
/

插入数据的存储过程语句如下:

begin
    declare v_i int;v_procname varchar(50);v_employeeinfotbl varchar(50);strsql varchar(4000);
begin
    v_i := 0;
    while v_i < 10 loop
        v_procname        := 'pr_insertdata'||substr(to_char(v_i),1,1);
        v_employeeinfotbl := 'tb_employeeinfo'||substr(to_char(v_i),1,1);

        v_i := v_i + 1;
        strsql := 'create or replace procedure '||v_procname||'(
            v_employeeno   in   varchar2,
            v_employeeage  in   int,
            v_retcode      out  int      -- 0_success, 1,2_fail
        )
        as
            v_employeecnt     int;

        begin
            v_retcode := 0;

            select count(*) into v_employeecnt from '||v_employeeinfotbl||' where employeeno = v_employeeno;
            if v_employeecnt > 0 then       -- the employeeno is already in DB
            begin
                v_retcode := 1;
                return;
            end;
            else                            -- the employeeno is not in DB
            begin
                insert into '||v_employeeinfotbl||'(employeeno, employeeage) values(v_employeeno, v_employeeage);
            end;
            end if;
        exception when others then
            begin
                rollback;
                v_retcode := 2;                
                return;
            end;
        end;';
        execute immediate strsql;
    end loop;
    end;
end;
/

我们在PL/SQL Developer软件上执行了以上SQL语句(注意:先建表,后建存储过程)之后,利用以下SQL语句向tb_employeeinfo6表中插入数据:

set serveroutput on
declare v_retcode     int;
begin
pr_insertdata6('123456', 25, v_retcode);
dbms_output.put_line( v_retcode);
end;
/

执行“select * from tb_employeeinfo6;”语句查询数据,结果如下:

SQL> select * from tb_employeeinfo6;
EMPLOYEENO      EMPLOYEEAGE
---------------------------
123456          25

可见,数据插入成功。

接着,我们利用以下命令行从Linux系统上登录到ORACLE数据库中(注意:username是指数据库用户名,password是指数据库密码,databaseservername是指数据库服务名):

sqlplus /nolog
connect username/password@databaseservername

然后执行如下查询语句:

select * from tb_employeeinfo6;

发现返回的值为空,即该数据表中没有数据。
真是奇怪了,为什么同样的查询语句,两边的执行结果不一致呢?
我们回过头来详细阅读了建表和建存储过程的代码,没看出有明显的问题。我们将该问题告诉了一位工作多年的老员工,请他来帮我们分析问题的原因所在。他详细看了我们的SQL语句之后,便指出存储过程的代码有点问题,在向表中插入数据之后忘记提交了。也就是说,存储过程中的“insert…”语句之后应该加上“commit;”。
难道就是这个“commit;”语句惹的祸吗?

三、问题原因
我们将存储过程的代码修改为如下:

begin
    declare v_i int;v_procname varchar(50);v_employeeinfotbl varchar(50);strsql varchar(4000);
begin
    v_i := 0;
    while v_i < 10 loop
        v_procname        := 'pr_insertdata'||substr(to_char(v_i),1,1);
        v_employeeinfotbl := 'tb_employeeinfo'||substr(to_char(v_i),1,1);

        v_i := v_i + 1;
        strsql := 'create or replace procedure '||v_procname||'(
            v_employeeno   in   varchar2,
            v_employeeage  in   int,
            v_retcode      out  int      -- 0_success, 1,2_fail
        )
        as
            v_employeecnt     int;

        begin
            v_retcode := 0;

            select count(*) into v_employeecnt from '||v_employeeinfotbl||' where employeeno = v_employeeno;
            if v_employeecnt > 0 then       -- the employeeno is already in DB
            begin
                v_retcode := 1;
                return;
            end;
            else                            -- the employeeno is not in DB
            begin
                insert into '||v_employeeinfotbl||'(employeeno, employeeage) values(v_employeeno, v_employeeage);
                commit;
            end;
            end if;
        exception when others then
            begin
                rollback;
                v_retcode := 2;                
                return;
            end;
        end;';
        execute immediate strsql;
    end loop;
    end;
end;
/

接着,我们在PL/SQL Developer软件上执行了以上SQL语句,并利用以下SQL语句向tb_employeeinfo9表中插入数据:

set serveroutput on
declare v_retcode     int;
begin
pr_insertdata9('123469', 25, v_retcode);
dbms_output.put_line( v_retcode);
end;
/

同样在该软件上执行“select * from tb_ employeeinfo9;”语句查询数据,结果如下:

SQL> select * from tb_employeeinfo9;
EMPLOYEENO     EMPLOYEEAGE
--------------------------
123469         25

然后在Linux系统上执行“select * from tb_employeeinfo9;”语句,结果如下:

SQL> select * from tb_employeeinfo9;
EMPLOYEENO   EMPLOYEEAGE
------------------------
123469       25

可见,数据被成功插入到员工信息表中。

四、总结
对于本次因为“commit;”而引发的问题,我们的总结如下:
第一,在动手编写代码之前,一定要对语法规则了然于心,不要让一个小小的问题引起整个软件功能的异常。
第二,在软件开发中,经验十分的重要。一个新人花几个小时不能解决的问题,一个老手可能几分钟就搞定了。因此,在遇到自己不能解决的问题的时候,我们一定要勤于开口,多多向有经验的老员工请教。


本人微信公众号:zhouzxi,请扫描以下二维码:
这里写图片描述

目录
相关文章
|
13天前
|
SQL Oracle 关系型数据库
【Oracle】玩转Oracle数据库(一):装上去,飞起来!
【Oracle】玩转Oracle数据库(一):装上去,飞起来!
54 7
|
13天前
|
SQL Oracle 关系型数据库
【Oracle】玩转Oracle数据库(七):RMAN恢复管理器
【Oracle】玩转Oracle数据库(七):RMAN恢复管理器
40 5
|
13天前
|
SQL 算法 数据库
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
78 6
|
5天前
|
存储 Oracle 关系型数据库
Oracle的模式与模式对象:数据库的“城市规划师”
【4月更文挑战第19天】在Oracle数据库中,模式是用户对象的集合,相当于数据库的城市规划,包含表、视图、索引等模式对象。模式对象是数据存储结构,如表用于存储数据,视图提供不同查看角度,索引加速数据定位。良好的模式与模式对象设计关乎数据效率、安全和稳定性。规划时需考虑业务需求、性能、安全和可扩展性,以构建高效数据库环境,支持企业业务发展。
|
13天前
|
存储 SQL Oracle
【Oracle】玩转Oracle数据库(二):体系结构、存储结构与各类参数
【Oracle】玩转Oracle数据库(二):体系结构、存储结构与各类参数
35 7
|
Oracle 关系型数据库 数据库
oracle数据库控制文件的备份和恢复之三RMAN自动备份和恢复
使用RMAN自动备份的控制文件向数据库中恢复控制文件
310 0
|
Oracle 关系型数据库 数据库
|
SQL Oracle 关系型数据库
oracle数据库控制文件的备份和恢复之一手动备份和恢复
实验步骤:手动备份和恢复oracle控制文件
517 0

推荐镜像

更多