Oracle存储过程导入，判断已有数据更新，没有的数据导入，统计导入成功与失败数，返回一个表

  1 create or replace procedure Proc_PX_GWBMImport(PlanTimes nvarchar2,BMDates nvarchar2,PWDs nvarchar2,cur_arg out sys_refcursor)
  2 as
  3 successc number;
  4 failc number;
  5 chongf number;
  6 counts number;
  7 begin
  8   delete from PROC_PXBAOMLS where identitynum=' ' or identitynum is null;
  9 
 10 select  count(1) into counts  from PROC_PXBAOMLS;
 11 
 12 update PROC_PXBAOMLS set PlanTime=PlanTimes;
 13 /*身份证重复的，更新标记*/
 14 update PROC_PXBAOMLS set Flag='1' where identitynum in (
 15 select PROC_PXBAOMLS.identitynum from PROC_PXBAOMLS right join (
 16 select identitynum From PROC_PXBAOMLS Group by identitynum having Count(identitynum) > 1 ) T on PROC_PXBAOMLS.identitynum = T.identitynum );
 17 
 18 /*身份证不符合规则的，更新标记*/
 19 
 20 update PROC_PXBAOMLS set Flag='0' where (select CHECK_IDCARD(PROC_PXBAOMLS.identitynum) as IsRight from dual )='0';
 21 
 22 /*插入到人员表*/
 23    insert into pk_user(
 24                         RowGuid,
 25                         Name,
 26                         SEX,
 27                         IdentityNum,
 28                         LoginID,
 29                         Password,
 30                         DanWeiName,
 31                         MobilePhone,
 32                         IsEnable,
 33                         Status,
 34                         USERTYPE
 35                         )
 36                  (select
 37                         LOWER(sys_guid()),
 38                         a.UserName,
 39                         (case a.Sex when cast('男' as nvarchar2(10)) then cast('0' as nvarchar2(10))  else cast('1' as nvarchar2(10))  end) as Sex,
 40                         a.identitynum,
 41                         a.identitynum,
 42                         PWDs,
 43                         a.DANWEINAME,
 44                         a.MobilePhone,
 45                         '0',
 46                         '0',
 47                         '0'
 48                         from PROC_PXBAOMLS a --where a.identitynum not in (select identitynum from pk_user) and (a.flag is null or a.flag!='1')
 49                         where not exists (select identitynum from pk_user where a.identitynum=pk_user.IDENTITYNUM) and  a.flag is null
 50                         );
 51     /*更新人员表 */
 52     update pk_user set Name=(select UserName from PROC_PXBAOMLS where PROC_PXBAOMLS.identitynum=pk_user.identitynum ),
 53                        SEX=(select (case PROC_PXBAOMLS.Sex when cast('男' as nvarchar2(10)) then cast('0' as nvarchar2(10))  else cast('1' as nvarchar2(10))  end) as Sex from PROC_PXBAOMLS where PROC_PXBAOMLS.identitynum=pk_user.identitynum  ),
 54                        LoginID=(select IdentityNum from PROC_PXBAOMLS where PROC_PXBAOMLS.identitynum=pk_user.identitynum ),
 55                        DanWeiName=(select danweiname from PROC_PXBAOMLS where PROC_PXBAOMLS.identitynum=pk_user.identitynum  ),
 56                        MobilePhone=(select MobilePhone from PROC_PXBAOMLS where PROC_PXBAOMLS.identitynum=pk_user.identitynum ) 
 57                        --where identitynum in(select identitynum from PROC_PXBAOMLS where (PROC_PXBAOMLS.flag is null or PROC_PXBAOMLS.flag!='1'));
 58                         where identitynum in(select identitynum from PROC_PXBAOMLS where (PROC_PXBAOMLS.flag is null));
 59 
 60 
 61 
 62     /*插入到报名表*/
 63      insert into px_baom(
 64                         RowGuid,
 65                         IsPay,
 66                         IsDel,
 67                         Status,
 68                         AddDate,
 69                         Name,
 70                         LoginID,
 71                         UserGuid,
 72                         PXPrograms,
 73                         DanWeiName,
 74                         PlanTime,
 75                         IsImport,
 76                         IsCurrent,
 77                         TrainType,
 78                         IsResit
 79                         )
 80                   (select
 81                           LOWER(sys_guid()),
 82                           '0',
 83                           '0',
 84                           '0',
 85                           to_date(BMDates,'yyyy-mm-dd HH24:MI:SS'),
 86                           a.name,
 87                           a.loginid,
 88                           a.rowguid,
 89                           '01',
 90                           a.danweiname,
 91                           PlanTimes,
 92                           '1',
 93                           '1',
 94                           (select ItemValue from VIEW_CodeMain_CodeItems where CodeName='PX_培训类型' and ItemText=b.TrainType) as TrainType,
 95                           (select ItemValue from VIEW_CodeMain_CodeItems where CodeName='PX_是否' and ItemText=b.IsResit) as IsResit
 96                           --from pk_user a inner join PROC_PXBAOMLS b on a.identitynum=b.identitynum  where (b.flag is null or b.flag!='1')
 97                           from pk_user a inner join PROC_PXBAOMLS b on a.identitynum=b.identitynum  where (b.flag is null)
 98                           );
 99 
100        /*插入到报名子表   */
101        insert into px_baomdetail(
102                                  RowGuid,
103                                  IsDel,
104                                  ItemName,
105                                  ItemGuid,
106                                  ParentGuid,
107                                  IsConfirm
108                                  )
109                          （select
110                                   LOWER(sys_guid()),
111                                   '0',
112                                   a.BKTYPE,--b.BKTYPE,
113                                   (select RowGuid from px_zhuanye where zhuanyname=a.BKTYPE and gangwnum='01') as ItemGuid,
114                                   a.rowguid,
115                                   '0'
116                            from 
117                            (--select * from px_baom a inner join PROC_PXBAOMLS b on a.loginid=b.identitynum where (b.flag is null or b.flag!='1') order by row_id desc)a where rownum <=counts
118                            select * from px_baom a inner join PROC_PXBAOMLS b on a.loginid=b.identitynum where (b.flag is null) order by row_id desc)a where rownum <=counts
119                            --px_baom a inner join PROC_PXBAOMLS b on a.loginid=b.identitynum where rownum <=counts
120                                   ）;
121 
122 
123   --更新导入成功标记
124   
125   update PROC_PXBAOMLS set flag='2' where flag is null  and IdentityNum in (select IdentityNum  from pk_user  );
126   update PROC_PXBAOMLS set flag='0' where not exists (select identitynum from pk_user where PROC_PXBAOMLS.identitynum=pk_user.IDENTITYNUM) and PROC_PXBAOMLS.flag is null;
127   --where (flag is null or flag!='1') and IdentityNum not in (select IdentityNum  from pk_user  );
128    commit;
129 
130    select  count(1) into successc  from PROC_PXBAOMLS where  Flag='2';
131    select count(1) into failc  from PROC_PXBAOMLS where  Flag='0';--nvl(flag,0)=1;
132    select count(1) into chongf from PROC_PXBAOMLS where  Flag='1';
133 
134    open cur_arg for  select successc as successc,failc as failc,chongf as chongf from dual;
135   end;
posted on 2017-04-21 16:12 努力向上的小蜗牛阅读(842) 评论(0) 编辑收藏举报
刷新页面返回顶部
导航

Oracle存储过程导入，判断已有数据更新，没有的数据导入，统计导入成功与失败数，返回一个表
