oracle中通过基表建立月表的存储过程

某些系统需要按月分表来保存数据。下面的存储过程演示了如何使用基表来建立每个月的月表。

处理思路是：

    1：首先，为基表建立好表和对应的索引。

    2：将基表保存到一个存储过程需要的表中。

    3：存储过程读取配置表，根据配置表中的名字，去数据库中查询对应的表的建表语句，然后，用月表的表名去替换语句中的表名，接着就建表。再去查询基表的表是不是有索引，如果有，则从数据库中得到索引的建表语句，然后，替换建表语句中的索引名称和表名称，最后执行建立索引的语句。

-- oracle环境的sql
-- 基础表准备
drop table base_config_monthly_table;
create table base_config_monthly_table
(
base_table_name varchar2(50),
valid_flag number(1) default 1,
remark varchar2(1000)
);

select * from base_config_monthly_table ;
insert into base_config_monthly_table(base_table_name,valid_flag,remark)
select 'aa_wanglc_test',1,'王路长提供的样例' from dual;
commit;

-- 待用函数的测试
SELECT REPLACE( '15800003367', substr('1580003367',4,4), '****') from dual;
select instr('abcdef','d') from dual;
select length('abdf') from dual;
select to_char(add_months(trunc(sysdate),-1),'yyyy') from dual;
select to_char(1,'fm00') from dual;

CREATE TABLE aa_wanglc_test AS SELECT * FROM dual;
SELECT * FROM aa_wanglc_test;
CREATE INDEX idx_wt_d009091 ON aa_wanglc_test(dummy);

-- 通过基表建立月表，且通过基表的索引建立月表的索引
CREATE OR REPLACE procedure proc_create_monthly_tables
 AUTHID CURRENT_USER IS
v_base_monthly_table base_config_monthly_table%rowtype; -- 配置表
v_base_ddl varchar2(4000); -- 保存基表的sql
v_sql varchar2(4000);   -- 最终需要使用的sql
v_new_tableName varchar2(50);   -- 月表的表名
v_yyyy varchar2(10);    --月表的年变量
v_month number := 0;    --月表的月变量
v_current_month varchar2(10);   -- 月份的循环变量
v_exists_flag number := 0;
v_base_table_name_upper varchar2(50);   -- 大写格式的基表名字

cursor v_cur_get_basetable is
select * from base_config_monthly_table where valid_flag = 1;

type cur_get_index is ref cursor; --声明一个动态游标类型，因为游标不是类型所以要声明一个动态游标类型，需要查询每个基表的索引
v_cur_get_index cur_get_index;  --声明一个动态游标变量

v_index_ddl varchar2(4000); -- 建立索引的sql
v_index_name_str varchar2(30);
v_index_name_num NUMBER;
v_index_name varchar2(30);
v_old_index_name varchar2(30);

begin
    open v_cur_get_basetable;
    loop
        fetch v_cur_get_basetable
            into v_base_monthly_table;
        exit when v_cur_get_basetable%notfound;

        select to_char(sysdate,'yyyy') into v_yyyy from dual;

        select upper(v_base_monthly_table.base_table_name) into v_base_table_name_upper from dual;
        --dbms_output.put_line(v_base_table_name_upper);
        v_exists_flag := 0;
        select count(*) into v_exists_flag from  user_tables where table_name = v_base_table_name_upper ;

        -- 如果基表不存在，则不处理这条配置记录
        if v_exists_flag = 0 then
            continue;
        end if;

        -- 在数据库系统取得基表的建表语句
        SELECT DBMS_METADATA.GET_DDL('TABLE', v_base_table_name_upper)
        into v_base_ddl
        from dual;

        --dbms_output.put_line(v_base_ddl);
        v_month := 0;

        loop
            v_month := v_month +1;
            exit when v_month > 12;
            select to_char(v_month,'fm00') into v_current_month from dual;
            v_new_tableName := v_base_table_name_upper||'_'||v_yyyy||v_current_month;
            -- dbms_output.put_line(v_new_tableName);

            v_exists_flag := 0;
            select count(*) into v_exists_flag from  user_tables where table_name = upper(v_new_tableName)
            or table_name = '"'||v_new_tableName||'"';

            -- dbms_output.put_line(v_exists_flag);
            -- 如果月表已经创建，则不再创建
            if v_exists_flag > 0 then
                continue;
            end if;

            -- 用月表的表名来替换建表语句中的基表表名
            SELECT REPLACE(v_base_ddl,
                v_base_table_name_upper,
                v_new_tableName)
            into v_sql
            from dual;

            --dbms_output.put_line(v_sql);
            execute IMMEDIATE v_sql;

            -- 查找索引
            v_sql := 'SELECT DBMS_METADATA.GET_DDL('''||CHR(73)||'NDEX'', index_name),index_name
                    FROM USER_INDEXES WHERE table_name = '''||v_base_table_name_upper||'''
                    AND UNIQUEness = ''NONUNIQUE''';
            open v_cur_get_index for v_sql;    -- 打开游标，并且SQL执行结果存放到游标
            LOOP
                fetch v_cur_get_index into v_index_ddl,v_old_index_name;
                exit when v_cur_get_index%notfound;  -- 退出循环

                -- 为索引取一个名字，名字是随机取得
                SELECT dbms_random.string ('x', 8) INTO v_index_name_str FROM dual;
                select trunc(dbms_random.value(0,100000)) INTO v_index_name_num from dual;
                v_index_name := 'idx_'||v_index_name_str||'_'||to_char(v_index_name_num);

                -- 把建立索引的语句中的索引名字改掉
                SELECT REPLACE(v_index_ddl,
                    v_old_index_name,
                    v_index_name)
                into v_sql
                from dual;

                --dbms_output.put_line(v_sql);
                -- 把建立索引的语句中的表名改掉
                SELECT REPLACE(v_sql,
                    v_base_table_name_upper,
                    v_new_tableName)
                into v_sql
                from dual;

                --dbms_output.put_line(v_sql);
                execute IMMEDIATE v_sql;

            END LOOP; -- 创建索引的动态游标结束
        end loop;
    end loop;
    close v_cur_get_basetable;
end ;

-- 查看存储过程是不是有错误
select * from user_errors;

-- 执行存储过程
begin
proc_create_monthly_tables;
end;

-- 查看效果
SELECT * FROM user_tables WHERE table_name LIKE upper('aa_wanglc_test%');
SELECT * FROM USER_indexes WHERE table_name LIKE upper('aa_wanglc_test%');

-- 删除测试表的语句
select 'drop table '|| table_name || ';' from user_tables where table_name like upper('aa_wanglc_test%');

结果说明

    这个存储过程可以通过一个表来建其他表，并且能建立源表的主键和索引等对象。