_employeeinfo7
begin
execute immediate 'drop table tb_employeeinfo7 cascade constraints';
exception when others then commit;
end;
/
create table tb_employeeinfo7
(
employeeno varchar2(10) not null, -- employee number
employeeage int not null -- employee age
);
create unique index idx1_tb_employeeinfo7 on tb_employeeinfo7(employeeno);
prompt 'create table tb_employeeinfo7 ok';
commit;
-- tb_employeeinfo8
begin
execute immediate 'drop table tb_employeeinfo8 cascade constraints';
exception when others then commit;
end;
/
create table tb_employeeinfo8
(
employeeno varchar2(10) not null, -- employee number
employeeage int not null -- employee age
);
create unique index idx1_tb_employeeinfo8 on tb_employeeinfo8(employeeno);
prompt 'create table tb_employeeinfo8 ok';
commit;
-- tb_employeeinfo9
begin
execute immediate 'drop table tb_employeeinfo9 cascade constraints';
exception when others then commit;
end;
/
create table tb_employeeinfo9
(
employeeno varchar2(10) not null, -- employee number
employeeage int not null -- employee age
);
create unique index idx1_tb_employeeinfo9 on tb_employeeinfo9(employeeno);
prompt 'create table tb_employeeinfo9 ok';
commit;
存储过程创建语句：
create or replace procedure pr_insertdata ( v_employeeno in varchar2, v_employeeage in int ) as v_employeecnt int; v_tableindex varchar2(2); begin v_tableindex := substr(v_employeeno, length(v_employeeno), 1); if v_tableindex = '0' then begin select count(*) into v_employeecnt from tb_employeeinfo0 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_employeeinfo0(employeeno, employeeage) values(v_employeeno, v_employeeage); end; end if; end; elsif v_tableindex = '1' then begin select count(*) into v_employeecnt from tb_employeeinfo1 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_employeeinfo1(employeeno, employeeage) values(v_employeeno, v_employeeage); end; end if; end; elsif v_tableindex = '2' then begin select count(*) into v_employeecnt from tb_employeeinfo2 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_employeeinfo2(employeeno, employeeage) values(v_employeeno, v_employeeage); end; end if; end; elsif v_tableindex = '3' then begin select count(*) into v_employeecnt from tb_employeeinfo3 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_employeeinfo3(employeeno, employeeage) values(v_employeeno, v_employeeage); end; end if; end; elsif v_tableindex = '4' then begin select count(*) into v_employeecnt from tb_employeeinfo4 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_employeeinfo4(employeeno, employeeage) values(v_employeeno, v_employeeage); end; end if; end; elsif v_tableindex = '5' then begin select count(*) into v_employeecnt from tb_employeeinfo5 where employeeno = v_employeeno; if v_employeecnt > 0 then -- the employe