基于ORACLE数据库的循环建表及循环创建存储过程的SQL语句实现
本文共 12377 字，大约阅读时间需要 41 分钟。
一、概述
在实际的软件开发项目中，我们经常会遇到需要创建多个相同类型的数据库表或存储过程的时候。例如，如果按照身份证号码的尾号来分表，那么就需要创建10个用户信息表，尾号相同的用户信息放在同一个表中。 对于类型相同的多个表，我们可以逐个建立，也可以采用循环的方法来建立。与之相对应的，可以用一个存储过程实现对所有表的操作，也可以循环建立存储过程，每个存储过程实现对某个特定表的操作。 本文中，我们建立10个员工信息表，每个表中包含员工工号(8位)和年龄字段，以工号的最后一位来分表。同时，我们建立存储过程实现对员工信息的插入。本文中的SQL语句基于ORACLE数据库实现。二、一般的实现方式
在该实现方式中，我们逐个建立员工信息表，并在一个存储过程实现对所有表的操作。具体SQL语句如下： 建表语句：-- tb_employeeinfo0begin execute immediate 'drop table tb_employeeinfo0 cascade constraints'; exception when others then commit;end;/create table tb_employeeinfo0( employeeno varchar2(10) not null, -- employee number employeeage int not null -- employee age);create unique index idx1_tb_employeeinfo0 on tb_employeeinfo0(employeeno);prompt 'create table tb_employeeinfo0 ok';commit;-- tb_employeeinfo1begin execute immediate 'drop table tb_employeeinfo1 cascade constraints'; exception when others then commit;end;/create table tb_employeeinfo1( employeeno varchar2(10) not null, -- employee number employeeage int not null -- employee age);create unique index idx1_tb_employeeinfo1 on tb_employeeinfo1(employeeno);prompt 'create table tb_employeeinfo1 ok';commit;-- tb_employeeinfo2begin execute immediate 'drop table tb_employeeinfo2 cascade constraints'; exception when others then commit;end;/create table tb_employeeinfo2( employeeno varchar2(10) not null, -- employee number employeeage int not null -- employee age);create unique index idx1_tb_employeeinfo2 on tb_employeeinfo2(employeeno);prompt 'create table tb_employeeinfo2 ok';commit;-- tb_employeeinfo3begin execute immediate 'drop table tb_employeeinfo3 cascade constraints'; exception when others then commit;end;/create table tb_employeeinfo3( employeeno varchar2(10) not null, -- employee number employeeage int not null -- employee age);create unique index idx1_tb_employeeinfo3 on tb_employeeinfo3(employeeno);prompt 'create table tb_employeeinfo3 ok';commit;-- tb_employeeinfo4begin execute immediate 'drop table tb_employeeinfo4 cascade constraints'; exception when others then commit;end;/create table tb_employeeinfo4( employeeno varchar2(10) not null, -- employee number employeeage int not null -- employee age);create unique index idx1_tb_employeeinfo4 on tb_employeeinfo4(employeeno);prompt 'create table tb_employeeinfo4 ok';commit;-- tb_employeeinfo5begin execute immediate 'drop table tb_employeeinfo5 cascade constraints'; exception when others then commit;end;/create table tb_employeeinfo5( employeeno varchar2(10) not null, -- employee number employeeage int not null -- employee age);create unique index idx1_tb_employeeinfo5 on tb_employeeinfo5(employeeno);prompt 'create table tb_employeeinfo5 ok';commit;-- tb_employeeinfo6begin execute immediate 'drop table tb_employeeinfo6 cascade constraints'; exception when others then commit;end;/create table tb_employeeinfo6( employeeno varchar2(10) not null, -- employee number employeeage int not null -- employee age);create unique index idx1_tb_employeeinfo6 on tb_employeeinfo6(employeeno);prompt 'create table tb_employeeinfo6 ok';commit;-- tb_employeeinfo7begin 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_employeeinfo8begin 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_employeeinfo9begin 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 employeeno 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~9begin 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;/
四、总结
当相同类型的表的个数较多时(如有上百个)，显然用循环创建的实现方式可以节约大量的工作时间，提高工作效率。但是，在使用该方法的时候，要特别仔细，尤其要注意单引号的使用，避免为了省事而引入代码逻辑问题。本人微信公众号：zhouzxi，请扫描以下二维码：
你可能感兴趣的文章
seajs
查看>>
python GUI编程tkinter示例之目录树遍历工具
查看>>
深入理解C++11 阅读笔记
查看>>
解方程 sqrt(x-sqrt(n))+sqrt(y)-sqrt(z)=0的所有自然数解
查看>>
JFrame 桌面右下角弹窗
查看>>
自动化测试系列：Selenium UI自动化解决iframe定位问题
查看>>
MySql计算两个日期的时间差函数
查看>>
安装Ubuntu虚拟机详解
查看>>
未来的移动领域就是android的世界了
查看>>
modifiers标量调节器插件的定义和使用
查看>>
永久生效环境变量
查看>>
CF 49E Common ancestor
查看>>
HDU 1257 最少拦截系统 (DP || 贪心)
查看>>
面试真题讲解
查看>>
RESTful接口设计原则和优点
查看>>
[c#]获取exchange中的图片
查看>>
Cookie
查看>>
PyCharm快捷键
查看>>
管道和FIFO
查看>>
JavaScript 事件的冒泡问题
查看>>