eno is already in DB begin return; end; else -- the employeeno is not in DB begin insert into tb_employeeinfo5(employeeno, employeeage) values(v_employeeno, v_employeeage); end; end if; end; elsif v_tableindex = '6' then begin select count(*) into v_employeecnt from tb_employeeinfo6 where employeeno = v_employeeno; if v_employeecnt > 0 then -- the employeeno is already in DB begin return; end; else -- the employeeno is not in DB begin insert into tb_employeeinfo6(employeeno, employeeage) values(v_employeeno, v_employeeage); end; end if; end; elsif v_tableindex = '7' then begin select count(*) into v_employeecnt from tb_employeeinfo7 where employeeno = v_employeeno; if v_employeecnt > 0 then -- the employeeno is already in DB begin return; end; else -- the employeeno is not in DB begin insert into tb_employeeinfo7(employeeno, employeeage) values(v_employeeno, v_employeeage); end; end if; end; elsif v_tableindex = '8' then begin select count(*) into v_employeecnt from tb_employeeinfo8 where employeeno = v_employeeno; if v_employeecnt > 0 then -- the employeeno is already in DB begin return; end; else -- the employeeno is not in DB begin insert into tb_employeeinfo8(employeeno, employeeage) values(v_employeeno, v_employeeage); end; end if; end; elsif v_tableindex = '9' then begin select count(*) into v_employeecnt from tb_employeeinfo9 where employeeno = v_employeeno; if v_employeecnt > 0 then -- the employeeno is already in DB begin return; end; else -- the employeeno is not in DB begin insert into tb_employeeinfo9(employeeno, employeeage) values(v_employeeno, v_employeeage); end; end if; end; end if; commit; exception when others then begin rollback; return; end; end; / prompt 'create procedure pr_insertdata ok'
三、循环创建的实现方式
在该实现方式中，我们采用循环的方法建立员工信息表及存储过程。具体SQL语句如下：
建表语句：
-- 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 ) as v_employeecnt int; begin select count(*) into v_employeecnt from '||v_employeeinfotbl||' where employeeno = v_employeeno; if v_employeecnt > 0 then -- the employeeno is already in DB begin return; end; else -- the employeeno is not in DB begin insert into '||v_employeeinfotbl||'(employeeno, employeeage) values(v_employeeno, v_employeeage); end; end if; commit; exception when others then begin rollback; return; end; end;'; execute immediate strsql; end loop; end; end; /
四、总结
当相同类型的表的个数较多时(如有上百个)，显然用循环创建的实现方式可以节约大量的工作时间，提高工作效率。但是，在使用该方法的时候，要特别仔细，尤其要注意单引号的使用，避免为了省事而引入代码逻辑问题。