基于ORACLE数据库的循环建表及循环创建存储过程的SQL语句实现
来源:互联网 发布:ubuntu 卸载软件 编辑:程序博客网 时间:2024/06/11 18:26
一、概述
在实际的软件开发项目中,我们经常会遇到需要创建多个相同类型的数据库表或存储过程的时候。例如,如果按照身份证号码的尾号来分表,那么就需要创建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,请扫描以下二维码:
1 0
- 基于ORACLE数据库的循环建表及循环创建存储过程的SQL语句实现
- 存储过程的循环语句
- oracle 存储过程循环执行update语句
- Oracle 存储过程中的循环语句写法
- for语句实现循环的执行过程
- 使用简单的存储过程,实现数据库表的循环插入
- mysql的存储过程中的语句if case 循环语句
- oracle存储过程循环
- SQL存储过程循环
- sql 存储过程 循环
- Oracle数据库:如何创建约束及创建约束的SQL语句,正则约束的SQL语句
- SQL 语句创建数据库、表与存储过程的样例
- MySQL存储过程中使用 WHILE 循环语句的用法
- oracle数据库循环语句
- SQL基本编程,分支语句,循环语句,存储过程,触发器
- 基于MySQL游标和存储过程的循环插入
- 常用的sql语句及存储过程
- 常用的SQL语句及存储过程
- CVE-2015-3202 exploit demo
- java hashCode
- mysql存储过程循环语句简单例子
- 比较两个DataTable内容是否相等,先是比数量,数量相等就比内容
- zookeeper原理(转)
- 基于ORACLE数据库的循环建表及循环创建存储过程的SQL语句实现
- CGGeometry
- Nginx 反向代理、负载均衡、页面缓存、URL重写及读写分离详解
- 黑马程序员---Java 装饰设计模式
- ajax
- BC 2015年百度之星程序设计大赛 - 初赛(1)(矩形面积-旋转卡壳)
- iOS开发UI基础—30在UItableview中实现加载更多功能
- C++实现事件机制
- SAT阅读:态度题型解答方法指导