存储过程 建表 分表

 

由于单表数据量太大，达到千万级别，所以考虑采用oracle的存储过程实施分表操作。

 

说明：schema表中存储指标信息

          store表中存放schema中指标指定的存储信息

          dic_info是字典表

 

          按照schema的id号建相同表结构的表，更新字典项，把原始的大表里的数据分摊到新建的小表中，实现分表。

 

create or replace procedure PROC_CREATE_TABLE_INSERT_DATA

(

  --用以输出的信息

  on_flag out number,            --SQL错误码

  out_reason out varchar2       --错误原因

)

is

  v_table_name varchar2(50);              --新生成的数据表表名

  v_table_num integer;                      --数据表的数据量

  v_create_sql varchar2(768);            --建表语句

  v_data_insert varchar2(256);           --数据插入语句

  v_old_table varchar2(50);               --原始表表名

  v_dic_sql varchar2(128);                --字典项查询语句

  v_dic_count integer;                      --字典项记数

  v_data_count_sql varchar2(128);       --数据统计语句

  v_data_count integer;                    --数据记数

 

begin

  --schema表中的数据存储信息放在store表中，而store表中的表id号是存储在字典表dic_info中的，现在将schema id作为新的表名，原始存储表名作为旧的表名

  --oracle循环查询的结果集到v_schemas变量中

  for v_schemas in (select a.id as newTable, c.code as oldTable

                      from schema a, store b, dic_info c

                     where b.tablenameid = c.id and a.storeid = b.id) loop

    v_table_name := v_schemas.newTable;           --oracle数据复制给变量

    v_old_table := v_schemas.oldTable;

   

    --检测是否已存在要建的表

    select count(*) into v_table_num from user_tables where table_name = upper(v_table_name);

    if (v_table_num < 1) then

      v_create_sql := 'create table '|| v_table_name || '(

        id            VARCHAR2(50) primary key,

        schemaid    VARCHAR2(50),

        columnvalue1  VARCHAR2(128),

        columnvalue2  VARCHAR2(128),

        columnvalue3  VARCHAR2(128)

      )';

 

      --建表

      execute immediate v_create_sql;

      commit;

     

      dbms_output.put_line('新建表：' || v_table_name || ' 成功。');      --输出记录

     

     

      --更新dic_info中的表信息

      v_dic_sql := 'select count(*) from dic_info where code = ' || chr(39) || v_table_name || chr(39);

      execute immediate v_dic_sql into v_dic_count;

      if (v_dic_count < 1) then

        insert into dic_info

        -- select sys_guid() from dual 是oracle中新建GUID的方式

        values ((select sys_guid() from dual), v_table_name, v_table_name,

              '48484a2730b1703c0130b5651e9a001e', null, 0, null, null);

        commit;

      end if;

     

     

      --将原始数据表中的采集数据导入新建表
      --oracle中chr(39)表示单引号

      v_data_insert := 'insert into ' || v_table_name || ' (select * from ' || v_old_table || ' where schemaid = ' || chr(39)|| v_table_name || chr(39) || ')'; 

      v_data_count_sql := 'select count(*) from ' || v_table_name;

      --由于v_table_name是动态生成的，所以需要用execute去执行

      execute immediate v_data_count_sql into v_data_count;

      if (v_data_count < 1) then

         execute immediate v_data_insert;

         commit;

         execute immediate v_data_count_sql into v_data_count;

         dbms_output.put_line('向表' || v_table_name || '中存入数据' || v_data_count || '条');

      end if;

     

     

      --更新存储定义

      update store a set a.tablenameid = (select id from dic_info where name = v_table_name)

       where id = (select storedeid from schema where id = v_table_name);

      commit;     

           

    else 

      dbms_output.put_line('指定表：' || v_table_name || ' 已存在。');

    end if;

   

  end loop;

 

--处理异常

  EXCEPTION

    WHEN OTHERS THEN

      on_flag := SQLCODE;

      out_reason := SUBSTR (SQLERRM, 1, 255);

      ROLLBACK;

     

end;

 

 

 

PS：在存储过程执行过程中抛出    

    ORA-20000:   buffer   overflow,   limit   of   10000   bytes异常，

    是因为有dbms_output.put_line语句，在pl/sql脚本中执行

      SQL> set serveroutput 100000;

       也不行。后来改为Test时手动修改 Buffer si的容量后才没有报错。