一起ORACLE資料庫中資料查詢結果不一致問題的排查過程，oracle排查

一起ORACLE資料庫中資料查詢結果不一致問題的排查過程，oracle排查

一、問題描述
在某軟體開發項目中，需要在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~9begin     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 ondeclare v_retcode     int;beginpr_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 /nologconnect 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 ondeclare v_retcode     int;beginpr_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，請掃描以下二維碼：