存储过程 建表 分表
?
由于单表数据量太大，达到千万级别，所以考虑采用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;
?????