由于单表数据量太大,达到千万级别,所以考虑采用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的容量后才没有报错。