基於ORACLE數據庫的循環建表及循環創建存儲過程的SQL語句實現

基於ORACLE數據庫的循環建表及循環創建存儲過程的SQL語句實現

一、概述
在實際的軟件開發項目中，我們經常會遇到需要創建多個相同類型的數據庫表或存儲過程的時候。例如，如果按照身份證號碼的尾號來分表，那麼就需要創建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;
/

四、總結
當相同類型的表的個數較多時(如有上百個)，顯然用循環創建的實現方式可以節約大量的工作時間，提高工作效率。但是，在使用該方法的時候，要特別仔細，尤其要注意單引號的使用，避免為了省事而引入代碼邏輯問題。