批量inset和update的存储过程实现

前提：
【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
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
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
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
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个步骤做为一个事物进行处理，整个流程就执行完毕。


 下面是存储过程的实现：

CREATE   OR   REPLACE   PROCEDURE  V3_SUB_FETCH_111
--  完成功能：将用户文件中字段存储到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