一、问题描述
在某软件开发项目中，需要在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;”语句查询数据，结果如下：