前提:
【1】存在susr_basic和susr_info数据表,他们之间是1对N的关系,其中susr_info的customerindex鍵和susr_basic中的外键customerindex关联,susr_basic和susr_info表中的主键分别是userindex和customerindex,其生成方式是调用函数sp_getmaxvalue_func生成唯一序列值。
【2】已经利用sqlload工具将外部的用户号码文件导入到了v3_sub_1表中。
目的:
通过调用该存储过程将v3_sub_1表中的不带86开头的用户号码insert到susr_basic表中生成对应的用户记录,同时要生成对应相同数量的susr_info客户记录,带86开头的号码以及sql异常需要记录log。(期间要利用自定义的v3_sub_2和v3_sub_4做数据缓冲区)
中间表sql脚本:
create table V3_SUB_1
(
USERCODE VARCHAR2 ( 40 ) not null -- 用户号码
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create table V3_SUB_2
(
USERCODE VARCHAR2 ( 40 ), -- 用户号码
CUSTOMERINDEX NUMBER -- 和v3_sub_4以及susr_info中的customerindex对应
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create unique index CUSTOMERINDEX_V2 on V3_SUB_2 (CUSTOMERINDEX) -- 在customerindex上建建立索引
tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
create table V3_SUB_4
(
USERINDEX NUMBER , -- 调用sp_getmaxvalue_func函数生成唯一值
USERCODE VARCHAR2 ( 100 ), -- 用户号码
CUSTOMERINDEX NUMBER -- 和v3_sub_2以及susr_info中的customerindex对应
)
tablespace SYSTEM
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create/Recreate indexes
create unique index CUSTOMERINDEX_V4 on V3_SUB_4 (CUSTOMERINDEX) -- 在customerindex上建立索引
tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
create table V3_SUB_1_LOG
(
TABLENAME VARCHAR2 ( 100 ), -- 表名称
MESSAGE VARCHAR2 ( 1000 ), -- 异常信息
CREATETIME DATE, -- 异常发生时间
USERCODE VARCHAR2 ( 100 ) -- 异常的号码
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
程序思路:
【1】先利用sql将v3_sub_1表中的不以86开头以及是新号码的用户号码insert到v3_sub_2,同时生成customerindex值,带86以及已经存在的号码插到v3_sub_1_log表。
【2】将第1步生成的customerindex作为susr_info的主键insert到susr_info中,记录数和v3_sub_2一致。
【3】将第2步中生成的customerindex字段的值insert到v3_sub_4中,记录数和v3_sub_2一致。
【4】将v3_sub_2表中生成的usercode字段值insert到v3_sub_4用户表,利用v3_sub_2和v3_sub_4的customerindex关联来进行批量update表v3_sub_4中的usercode字段。
【5】将第4步中v3_sub_4表中数据copy一份到zxdbm_ismp.susr_basic表中。
【6】将上面5个步骤做为一个事物进行处理,整个流程就执行完毕。
下面是存储过程的实现:
-- 完成功能:将用户文件中字段存储到temp表,再将temp表数据转到用户表
(
v_retvalue out varchar2
)
AS
v_message varchar2 ( 100 );
begin
begin
-- 先将v3_sub_1表中的不带86以及是新号码的insert到v3_sub_2,同时生customerindex
insert into v3_sub_2(usercode,customerindex)
select t1.usercode,sp_getmaxvalue_func( ' susr_info ' , 1 ) from v3_sub_1 t1
where not exists ( select t2.usercode from susr_basic t2
where t1.usercode = t2.usercode ) and substr(t1.usercode, 1 , 2 ) <> ' 86 ' ;
-- 将带86以及已经存在的号码插到log表
-- distinct过滤掉重复的86号码记录,'susr_basic','fail',sysdate做特定值insert到v3_sun_1_log表
insert into v3_sub_1_log
select distinct ' susr_basic ' , ' fail ' ,sysdate,t1.usercode from v3_sub_1 t1,susr_basic t2
where t1.usercode = t2.usercode or substr(t1.usercode, 1 , 2 ) = ' 86 ' ;
-- 添加客户表信息,插入的记录数和v3_sub_2用户表中号码记录数一致
insert into susr_info(customerindex,customerid,userpwd,provid)
select customerindex,customerindex, ' 111111 ' , ' 074 ' from v3_sub_2;
-- 插入v3_sub_4的记录数和v3_sub_2用户表中号码记录数一致
insert into v3_sub_4(customerindex) select customerindex from v3_sub_2;
-- 将v3_sub_2表中余下usercode字段值insert到v3_sub_4用户表,批量update
update v3_sub_4 v4 set
(v4.userindex,v4.usercode) =
( select sp_getmaxvalue_func( ' susr_basic ' , 1 ),v2.usercode
from v3_sub_2 v2 where v2.customerindex = v4.customerindex);
-- 将v3_sub_4表中数据copy一份到susr_basic表中
insert into susr_basic(userindex,usercode,customerindex)
select userindex,usercode,customerindex from v3_sub_4;
commit ;
exception when others then -- 执行sql异常情况
rollback ;
v_message : = sqlcode || ' : ' || sqlerrm; -- 记录异常日志
insert into v3_sub_1_log values ( ' sql ' ,v_message,sysdate, ' commit sqls fail ' );
commit ;
end ;
execute immediate ' truncate table v3_sub_1 ' ;
execute immediate ' truncate table v3_sub_2 ' ;
execute immediate ' truncate table v3_sub_4 ' ;
v_retvalue : = ' the end of importing userinfo ' ;
end V3_SUB_FETCH_111;
建议:以上过程的insert into susr_basic(userindex,usercode,customerindex) select userindex,usercode,customerindex from v3_sub_4;语句在执行的时候可以先将susr_basic表中索引drop掉,执行完该语句后再创建上该表的索引,可以用动态sql实现,这样效率会进一步得到提升,但做与否要看具体的环境而定。
测试结果:110w的用户号码在PC机上成功导入完毕所花时间约为32min
PC机配置:OS Windows XP CPU AMD 3800+(双核) 内存1G
http://www.blogjava.net/cheneyfree/archive/2008/02/03/179227.html