AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2015-06-03 1289
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 一、概述 在实际的软件开发项目中，我们经常会遇到需要创建多个相同类型的数据库表或存储过程的时候。

一、概述
在实际的软件开发项目中，我们经常会遇到需要创建多个相同类型的数据库表或存储过程的时候。例如，如果按照身份证号码的尾号来分表，那么就需要创建10个用户信息表，尾号相同的用户信息放在同一个表中。
对于类型相同的多个表，我们可以逐个建立，也可以采用循环的方法来建立。与之相对应的，可以用一个存储过程实现对所有表的操作，也可以循环建立存储过程，每个存储过程实现对某个特定表的操作。
本文中，我们建立10个员工信息表，每个表中包含员工工号(8位)和年龄字段，以工号的最后一位来分表。同时，我们建立存储过程实现对员工信息的插入。本文中的SQL语句基于ORACLE数据库实现。

二、一般的实现方式
在该实现方式中，我们逐个建立员工信息表，并在一个存储过程实现对所有表的操作。具体SQL语句如下：
建表语句：

-- tb_employeeinfo0
begin
    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_employeeinfo1
begin
    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_employeeinfo2
begin
    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_employeeinfo3
begin
    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_employeeinfo4
begin
    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_employeeinfo5
begin
    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_employeeinfo6
begin
    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_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 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~9
begin
     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，请扫描以下二维码：
这里写图片描述

文章标签：
SQL
关系型数据库
索引
数据库
存储
关键词：
SQL数据库
数据库sql
oracle数据库
数据库oracle
Oracle数据库一键上云数据库
周兆熊
目录
相关文章
1436047922066202
|
29天前
|
存储 Oracle 关系型数据库
数据库数据恢复—ORACLE常见故障的数据恢复方案
Oracle数据库常见故障表现： 1、ORACLE数据库无法启动或无法正常工作。 2、ORACLE ASM存储破坏。 3、ORACLE数据文件丢失。 4、ORACLE数据文件部分损坏。 5、ORACLE DUMP文件损坏。
1436047922066202
92 11
1436047922066202
|
2月前
|
Oracle 关系型数据库 数据库
Oracle数据恢复—Oracle数据库文件有坏快损坏的数据恢复案例
一台Oracle数据库打开报错，报错信息： “system01.dbf需要更多的恢复来保持一致性，数据库无法打开”。管理员联系我们数据恢复中心寻求帮助，并提供了Oracle_Home目录的所有文件。用户方要求恢复zxfg用户下的数据。 由于数据库没有备份，无法通过备份去恢复数据库。
1436047922066202
53 1
1436047922066202
|
2月前
|
存储 Oracle 关系型数据库
oracle数据恢复—Oracle数据库文件大小变为0kb的数据恢复案例
存储掉盘超过上限，lun无法识别。管理员重组存储的位图信息并导出lun，发现linux操作系统上部署的oracle数据库中有上百个数据文件的大小变为0kb。数据库的大小缩水了80%以上。 取出&并分析oracle数据库的控制文件。重组存储位图信息，重新导出控制文件中记录的数据文件，发现这些文件的大小依然为0kb。
1436047922066202
32 1
1436047922066202
|
1月前
|
存储 Oracle 关系型数据库
服务器数据恢复—华为S5300存储Oracle数据库恢复案例
服务器存储数据恢复环境： 华为S5300存储中有12块FC硬盘，其中11块硬盘作为数据盘组建了一组RAID5阵列，剩下的1块硬盘作为热备盘使用。基于RAID的LUN分配给linux操作系统使用，存放的数据主要是Oracle数据库。 服务器存储故障： RAID5阵列中1块硬盘出现故障离线，热备盘自动激活开始同步数据，在同步数据的过程中又一块硬盘离线，RAID5阵列瘫痪，上层LUN无法使用。
1436047922066202
24 0
sunrr
|
2月前
|
SQL Oracle 关系型数据库
Oracle数据库优化方法
【10月更文挑战第25天】Oracle数据库优化方法
sunrr
58 7
sunrr
|
2月前
|
Oracle 关系型数据库 数据库
oracle数据库技巧
【10月更文挑战第25天】oracle数据库技巧
sunrr
37 6
sunrr
|
2月前
|
存储 Oracle 关系型数据库
Oracle数据库优化策略
【10月更文挑战第25天】Oracle数据库优化策略
sunrr
37 5
1436047922066202
|
3月前
|
存储 Oracle 关系型数据库
数据库数据恢复—Oracle ASM磁盘组故障数据恢复案例
Oracle数据库数据恢复环境&故障： Oracle ASM磁盘组由4块磁盘组成。Oracle ASM磁盘组掉线 ，ASM实例不能mount。 Oracle数据库故障分析&恢复方案： 数据库数据恢复工程师对组成ASM磁盘组的磁盘进行分析。对ASM元数据进行分析发现ASM存储元数据损坏，导致磁盘组无法挂载。
1436047922066202
78 1
sunrr
|
3月前
|
监控 Oracle 关系型数据库
Oracle数据库性能优化
【10月更文挑战第16天】Oracle数据库性能优化是
sunrr
44 1
蓝易云
|
2月前
|
存储 Oracle 关系型数据库
oracle服务器存储过程中调用http
通过配置权限、创建和调用存储过程，您可以在Oracle数据库中使用UTL_HTTP包发起HTTP请求。这使得Oracle存储过程可以与外部HTTP服务进行交互，从而实现更复杂的数据处理和集成。在实际应用中，根据具体需求调整请求类型和错误处理逻辑，以确保系统的稳定性和可靠性。
蓝易云
95 0

热门文章

最新文章

  • 1
    Oracle 从 DMP 文件中恢复指定表的步骤
  • 2
    从ORACLE源进行批量数据迁移到GBase8a参考示例
  • 3
    服务器数据恢复—华为S5300存储Oracle数据库恢复案例
  • 4
    Flask 框架防止 SQL 注入攻击的方法
  • 5
    Vanna：开源 AI 检索生成框架，自动生成精确的 SQL 查询
  • 6
    SQL自学笔记（3）：SQL里的DCL,DQL都代表什么？
  • 7
    Flink SQL Deduplication 去重以及如何获取最新状态操作
  • 8
    MySQL进阶突击系列(02)一条更新SQL执行过程 | 讲透undoLog、redoLog、binLog日志三宝
  • 9
    MySQL 高级(进阶) SQL 语句
  • 10
    MySQL进阶突击系列(01)一条简单SQL搞懂MySQL架构原理 | 含实用命令参数集
  • 1
    云原生一体化数据库技术是一个具有潜力的领域
    119
  • 2
    构建高性能数据库的秘诀
    102
  • 3
    新一代数据库技术：融合AI的智能数据管理系统
    339
  • 4
    新一代数据库技术：融合人工智能与分布式系统的未来前景
    367
  • 5
    使用 Python 访问数据库的基本方法
    76
  • 6
    数据库解决方案
    110
  • 7
    mysql 设置环境变量与未设置环境变量连接数据库的区别
    173
  • 8
    服务器Linux系统配置mysql数据库主从自动备份
    234
  • 9
    SQLite数据库介绍以及安装
    116
  • 10
    sprinboot+vue集成neo4j图数据库
    189

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
  • Oracle云上最佳实践
  • 迁移 ORACLE 最佳实践

    • 相关实验场景

    更多
  • 使用DAS实现数据库SQL优化
  • 自建数据库迁移到云数据库
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 云原生HTAP数据库，让你的交易和分析一库搞定
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 快速体验PolarDB开源数据库

    • 推荐镜像

    更多
  • oracle
  • mysql
  • postgresql
    • 下一篇
    DataWorks智能交互式数据开发与分析之旅