Implementation of SQL statements for ORACLE database-based cyclic table creation and cyclic storage Creation

Implementation of SQL statements for ORACLE database-based cyclic table creation and cyclic storage Creation

I. Overview
In actual software development projects, we often encounter the need to create multiple database tables or stored procedures of the same type. For example, if the table is divided by the ending number of the ID card number, you need to create 10 user information tables. The user information with the same ending number is placed in the same table.
For multiple tables of the same type, we can create one by one or use a circular method. Correspondingly, you can use a stored procedure to perform operations on all tables, or create a stored procedure cyclically. Each stored procedure can perform operations on a specific table.
In this article, we create 10 employee information tables. Each table contains the employee ID (8 digits) and age fields, which are grouped by the last employee ID. At the same time, we establish a stored procedure to insert employee information. The SQL statements in this article are implemented based on the ORACLE database.

II. General implementation methods
In this implementation method, we create employee information tables one by one and perform operations on all tables in a stored procedure. The specific SQL statement is as follows:
Table creation statement:

-- 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;

Statement for creating a stored procedure:

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'

Iii. Implementation of cyclic Creation
In this implementation method, we use a circular method to establish employee information tables and stored procedures. The specific SQL statement is as follows:
Table creation statement:

-- 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;/

Statement for creating a stored procedure:

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;/

Iv. Summary
When the number of tables of the same type is large (if there are hundreds), it is clear that the implementation of circular creation can save a lot of work time and improve work efficiency. However, you should be careful when using this method, especially when using single quotes, to avoid introducing code logic problems for convenience.