oracle存储过程样板以及用法

有个业务逻辑：一次性将大量的数据（比如30w）导入两张表，要求对应关系，要求验重！

数据格式如下：

序号用户手机组长工号1张三丰12312345678呵呵6662张无忌12322345678哈哈777
现在要将“呵呵”和“哈哈”插入组长表，两张插入用户表：

create or replace procedure proc_exp_cust as flag varchar2(50); begin   for A in (       select t_d_Custmanager_Sequence.Nextval, telphonec,worktime,gooffwordtime,customermanager,backuptel,prov,        city,t_d_Custmanager_Sequence.Nextval classIndtify from (        select distinct(l.telphonec), l.worktime,l.gooffwordtime,l.customermanager,l.backuptel        ,prov,city, ROW_NUMBER() over(partition by  l.prov order by id desc)        as new_index from ex_log l where 1=1 and l.isvalid = 0 and l.telphonec is not null        )a     ) loop     flag := '1';     declare  cursor my_cursor is     select cu.prov,cu.telphone  from t_d_Custmanager cu where cu.prov = A.Prov and cu.telphone = A.Telphonec;    -- dbms_output.put_line(SQL%ROWCOUNT);     begin    for cur_row in my_cursor      loop     flag := '0';     if (cur_row.prov = A.Prov and cur_row.telphone = A.Telphonec) then       update   t_d_Custmanager cu set       cu.customermanager = A.CUSTOMERMANAGER,       cu.backuptel=A.Backuptel,       cu.worktime = A.Worktime,       cu.gooffwordtime=A.Gooffwordtime,       cu.city = A.City       where cu.prov = A.Prov and cu.telphone = A.Telphonec;    else      insert into t_d_Custmanager cu (id,cu.telphone, cu.worktime,         cu.gooffwordtime,customermanager,cu.backuptel,cu.prov,city,cu.custidentify)         values(t_d_Custmanager_Sequence.Nextval,A.Telphonec,A.Worktime,A.Gooffwordtime,A.Customermanager,A.Backuptel,A.Prov,         A.City,t_d_Custmanager_Sequence.Nextval);     end if;     end loop;     if(flag='1') then        insert into t_d_Custmanager cu (id,cu.telphone, cu.worktime,         cu.gooffwordtime,customermanager,cu.backuptel,cu.prov,city,cu.custidentify)         values(t_d_Custmanager_Sequence.Nextval,A.Telphonec,A.Worktime,A.Gooffwordtime,A.Customermanager,A.Backuptel,A.Prov,         A.City,t_d_Custmanager_Sequence.Nextval);    end if;     end;    end loop; end;

create or replace procedure proc_exp_vipuserasflag varchar2(50);begin  for A in (        select t_d_Vipuser_Sequence.Nextval,id,vipuname,viplevel,vipnum,prov,          city,telphonev,custidentify from (          select distinct(l.telphonev),l.id,l.vipuname,l.viplevel,l.vipnum,l.prov,l.city,          (select cu.custidentify from t_d_custmanager cu where cu.prov = l.prov and cu.telphone = l.telphonec and rownum =1) custidentify,          ROW_NUMBER() over(partition by  l.prov order by l.id desc)          as new_index from ex_log l where 1=1 and l.isvalid = 0 and l.telphonev is not null        )a  ) loop  flag := '1';  declare  cursor my_cursor is     select vp.prov,vp.telphone  from t_d_Vipuser vp where vp.prov = A.Prov and vp.telphone = A.Telphonev;    begin    for cur_row in my_cursor      loop       flag := '0';       if (cur_row.prov = A.Prov and cur_row.telphone = A.Telphonev) then          update t_d_Vipuser vp set          vp.vipuname = A.Vipuname,          vp.viplevel = A.Viplevel,          vp.vipnum = A.Vipnum,          vp.custphone=A.Custidentify,          vp.city = A.City          where vp.prov = A.Prov and vp.telphone = A.Telphonev;       else          insert into t_d_Vipuser vp(id,telphone,prov,city,vipuname,viplevel,vipnum,custphone)          values(t_d_Vipuser_Sequence.Nextval,A.Prov,A.City,A.Telphonev,A.Vipuname,A.VIPUNAME,A.Vipnum,A.CUSTIDENTIFY);       end if;     end loop;    if(flag='1') then        insert into t_d_Vipuser vp(id,telphone,prov,city,vipuname,viplevel,vipnum,custphone)        values(t_d_Vipuser_Sequence.Nextval,A.Telphonev,A.Prov,A.City,A.Vipuname,A.VIPUNAME,A.Vipnum,A.CUSTIDENTIFY);    end if;     --子表更新才算更新完处理状态    update ex_log l set l.isvalid = 1 where id = A.Id;    end;  end loop;end;

总结：

1，存储过程命名规则：proc_开头

2，语法：create or replace proc_存储过程名称

                  as

                  参数

                  begin

                 end;

3，循环的用法：for  别名 in (集合) loop  end  loop

4，赋值：存储过程中的赋值 :=

5，游标的用法：declare cursor 游标名  is sql语句

6，循环从游标中取出值：loop  end loop;