一、問題描述
在某軟件開發項目中，需要在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;”而引發的問題，我們的總結如下：
第一，在動手編寫代碼之前，一定要對語法規則了然於心，不要讓一個小小的問題引起整個軟件功能的異常。
第二，在軟件開發中，經驗十分的重要。一個新人花幾個小時不能解決的問題，一個老手可能幾分鐘就搞定了。因此，在遇到自己不能解決的問題的時候，我們一定要勤於開口，多多向有經驗的老員工請教。
