1324人阅读
软件项目实践中的SQL语言一瞥（28）
在实际的软件开发项目中，我们经常会遇到需要创建多个相同类型的数据库表或存储过程的时候。例如，如果按照身份证号码的尾号来分表，那么就需要创建10个用户信息表，尾号相同的用户信息放在同一个表中。
对于类型相同的多个表，我们可以逐个建立，也可以采用循环的方法来建立。与之相对应的，可以用一个存储过程实现对所有表的操作，也可以循环建立存储过程，每个存储过程实现对某个特定表的操作。
本文中，我们建立10个员工信息表，每个表中包含员工工号(8位)和年龄字段，以工号的最后一位来分表。同时，我们建立存储过程实现对员工信息的插入。本文中的SQL语句基于ORACLE数据库实现。
二、一般的实现方式
在该实现方式中，我们逐个建立员工信息表，并在一个存储过程实现对所有表的操作。具体SQL语句如下：
建表语句：
execute immediate 'drop table tb_employeeinfo0 cascade constraints';
exception when others then
create table tb_employeeinfo0
employeeno
varchar2(10)
employeeage
create unique index idx1_tb_employeeinfo0 on tb_employeeinfo0(employeeno);
prompt 'create table tb_employeeinfo0 ok';
execute immediate 'drop table tb_employeeinfo1 cascade constraints';
exception when others then
create table tb_employeeinfo1
employeeno
varchar2(10)
employeeage
create unique index idx1_tb_employeeinfo1 on tb_employeeinfo1(employeeno);
prompt 'create table tb_employeeinfo1 ok';
execute immediate 'drop table tb_employeeinfo2 cascade constraints';
exception when others then
create table tb_employeeinfo2
employeeno
varchar2(10)
employeeage
create unique index idx1_tb_employeeinfo2 on tb_employeeinfo2(employeeno);
prompt 'create table tb_employeeinfo2 ok';
execute immediate 'drop table tb_employeeinfo3 cascade constraints';
exception when others then
create table tb_employeeinfo3
employeeno
varchar2(10)
employeeage
create unique index idx1_tb_employeeinfo3 on tb_employeeinfo3(employeeno);
prompt 'create table tb_employeeinfo3 ok';
execute immediate 'drop table tb_employeeinfo4 cascade constraints';
exception when others then
create table tb_employeeinfo4
employeeno
varchar2(10)
employeeage
create unique index idx1_tb_employeeinfo4 on tb_employeeinfo4(employeeno);
prompt 'create table tb_employeeinfo4 ok';
execute immediate 'drop table tb_employeeinfo5 cascade constraints';
exception when others then
create table tb_employeeinfo5
employeeno
varchar2(10)
employeeage
create unique index idx1_tb_employeeinfo5 on tb_employeeinfo5(employeeno);
prompt 'create table tb_employeeinfo5 ok';
execute immediate 'drop table tb_employeeinfo6 cascade constraints';
exception when others then
create table tb_employeeinfo6
employeeno
varchar2(10)
employeeage
create unique index idx1_tb_employeeinfo6 on tb_employeeinfo6(employeeno);
prompt 'create table tb_employeeinfo6 ok';
execute immediate 'drop table tb_employeeinfo7 cascade constraints';
exception when others then
create table tb_employeeinfo7
employeeno
varchar2(10)
employeeage
create unique index idx1_tb_employeeinfo7 on tb_employeeinfo7(employeeno);
prompt 'create table tb_employeeinfo7 ok';
execute immediate 'drop table tb_employeeinfo8 cascade constraints';
exception when others then
create table tb_employeeinfo8
employeeno
varchar2(10)
employeeage
create unique index idx1_tb_employeeinfo8 on tb_employeeinfo8(employeeno);
prompt 'create table tb_employeeinfo8 ok';
execute immediate 'drop table tb_employeeinfo9 cascade constraints';
exception when others then
create table tb_employeeinfo9
employeeno
varchar2(10)
employeeage
create unique index idx1_tb_employeeinfo9 on tb_employeeinfo9(employeeno);
prompt 'create table tb_employeeinfo9 ok';
存储过程创建语句：
create or replace procedure pr_insertdata
v_employeeno
v_employeeage
v_tableindex
varchar2(2);
v_tableindex
:= substr(v_employeeno, length(v_employeeno), 1);
if v_tableindex =
select count(*) into v_employeecnt from tb_employeeinfo0 where employeeno = v_
if v_employeecnt & 0 then
-- the employeeno is already in DB
-- the employeeno is not in DB
insert into tb_employeeinfo0(employeeno, employeeage) values(v_employeeno, v_employeeage);
elsif v_tableindex =
select count(*) into v_employeecnt from tb_employeeinfo1 where employeeno = v_
if v_employeecnt & 0 then
-- the employeeno is already in DB
-- the employeeno is not in DB
insert into tb_employeeinfo1(employeeno, employeeage) values(v_employeeno, v_employeeage);
elsif v_tableindex =
select count(*) into v_employeecnt from tb_employeeinfo2 where employeeno = v_
if v_employeecnt & 0 then
-- the employeeno is already in DB
-- the employeeno is not in DB
insert into tb_employeeinfo2(employeeno, employeeage) values(v_employeeno, v_employeeage);
elsif v_tableindex =
select count(*) into v_employeecnt from tb_employeeinfo3 where employeeno = v_
if v_employeecnt & 0 then
-- the employeeno is already in DB
-- the employeeno is not in DB
insert into tb_employeeinfo3(employeeno, employeeage) values(v_employeeno, v_employeeage);
elsif v_tableindex =
select count(*) into v_employeecnt from tb_employeeinfo4 where employeeno = v_
if v_employeecnt & 0 then
-- the employeeno is already in DB
-- the employeeno is not in DB
insert into tb_employeeinfo4(employeeno, employeeage) values(v_employeeno, v_employeeage);
elsif v_tableindex =
select count(*) into v_employeecnt from tb_employeeinfo5 where employeeno = v_
if v_employeecnt & 0 then
-- the employeeno is already in DB
-- the employeeno is not in DB
insert into tb_employeeinfo5(employeeno, employeeage) values(v_employeeno, v_employeeage);
elsif v_tableindex =
select count(*) into v_employeecnt from tb_employeeinfo6 where employeeno = v_
if v_employeecnt & 0 then
-- the employeeno is already in DB
-- the employeeno is not in DB
insert into tb_employeeinfo6(employeeno, employeeage) values(v_employeeno, v_employeeage);
elsif v_tableindex =
select count(*) into v_employeecnt from tb_employeeinfo7 where employeeno = v_
if v_employeecnt & 0 then
-- the employeeno is already in DB
-- the employeeno is not in DB
insert into tb_employeeinfo7(employeeno, employeeage) values(v_employeeno, v_employeeage);
elsif v_tableindex =
select count(*) into v_employeecnt from tb_employeeinfo8 where employeeno = v_
if v_employeecnt & 0 then
-- the employeeno is already in DB
-- the employeeno is not in DB
insert into tb_employeeinfo8(employeeno, employeeage) values(v_employeeno, v_employeeage);
elsif v_tableindex =
select count(*) into v_employeecnt from tb_employeeinfo9 where employeeno = v_
if v_employeecnt & 0 then
-- the employeeno is already in DB
-- the employeeno is not in DB
insert into tb_employeeinfo9(employeeno, employeeage) values(v_employeeno, v_employeeage);
exception when others then
三、循环创建的实现方式
在该实现方式中，我们采用循环的方法建立员工信息表及存储过程。具体SQL语句如下：
建表语句：
tbname varchar2(50);strsql varchar2(1000);
while i&10 loop
tbname := 'tb_employeeinfo'||to_char(i);
select count(1) into tmpcount from user_tables where table_name = Upper(tbname);
if tmpcount&0 then
execute immediate 'drop table '||
strsql := 'create table '||tbname||
employeeno
varchar2(10)
employeeage
strsql := 'begin
execute immediate ''drop index idx1_'||tbname || ' '''
|| ';exception when others then null;
execute immediate 'create unique index idx1_'||tbname||' on '||tbname||'(employeeno)';
存储过程创建语句：
declare v_v_procname varchar(50);v_employeeinfotbl varchar(50);strsql varchar(4000);
while v_i & 10 loop
v_procname
v_employeeinfotbl :=
v_i := v_i + 1;
v_employeeno
v_employeeage
select count(*) into v_employeecnt from
if v_employeecnt & 0 then
-- the employeeno is already in DB
-- the employeeno is not in DB
insert into
exception when others then
当相同类型的表的个数较多时(如有上百个)，显然用循环创建的实现方式可以节约大量的工作时间，提高工作效率。但是，在使用该方法的时候，要特别仔细，尤其要注意单引号的使用，避免为了省事而引入代码逻辑问题。
本人微信公众号：zhouzxi，请扫描以下二维码：
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
与本人单独交流~~
访问：945918次
积分：17257
积分：17257
排名：第403名
原创：433篇
评论：1461条
文章：22篇
阅读：93489
文章：30篇
阅读：45851
文章：42篇
阅读：94774
(7)(7)(8)(5)(11)(11)(8)(6)(1)(3)(6)(4)(8)(7)(2)(7)(3)(14)(15)(14)(12)(16)(1)(5)(13)(11)(17)(19)(14)(14)(13)(11)(2)(3)(2)(1)(2)(1)(2)(5)(1)(5)(2)(1)(3)(2)(4)(5)(2)(8)(18)(16)(17)(7)(9)(15)(25)怎么用查询分析器循环建表 - Sql Server当前位置:& &&&怎么用查询分析器循环建表怎么用查询分析器循环建表&&网友分享于：&&浏览：20次如何用查询分析器循环建表？不知道表名如何定义
我用create & table & bbs '+ '好象不行（刚开始学的，可能笑话了）
代码如下：
declare & @i & int
set & @i=3
while & @i &5
create & table & bbs '+ '
title & varchar(100),
Content & varchar(8000)
create & table & report '+ '
topid & int,
Content & varchar(8000),
ip & varchar(15),
end------解决方案--------------------declare @i int
declare @sql nvarchar(200)
while @i &5
set @sql= 'create table bbs '+CONVERT(nvarchar(20),@i)+ '
title varchar(100),
Content varchar(8000)
create table report '+CONVERT(nvarchar(20),@i)+ '
topid int,
Content varchar(8000),
ip varchar(15),
exec (@sql)
12345678910
12345678910
12345678910 上一篇：下一篇：文章评论相关解决方案 12345678910 Copyright & &&版权所有