基于ORACLE数据库的循环建表及循环创建存储过程的SQL语句实现 - 数据库编程

_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

基于ORACLE数据库的循环建表及循环创建存储过程的SQL语句实现(二)