存储过程

CREATE OR REPLACE procedure proc_tj_region
as
  v_communitycode    varchar2(500); -- 社区编号
  v_communityname varchar2(500); -- 社区名称
  v_streetcode  varchar2(500); -- 街道编号
  v_streetname  varchar2(500); -- 街道名称
  v_deptid    varchar2(500); -- 部门编号
  v_regionid    varchar2(500); -- 小区id
  v_regionname    varchar2(500); -- 小区id
  v_zhs    number(11); --总户数
  v_ypchs  number(11); --已普查户数
  v_dpchs  number(11); --待普查户数
  v_jhhs   number(11); --拒核户数
  v_bhs    number(11); --闭户数
  v_khs    number(11); --空户数
  v_zrs    number(11); --总人数
  v_ypcrs    number(11); --已人数
  v_dpcrs    number(11); --待人数
  v_ndrs    number(11); --内地人口
  v_xgrs    number(11); --香港人口
  v_wjrs    number(11); --外籍人口
  v_amrs    number(11); --澳门人口
  v_twrs    number(11); --台湾人口
  v_hjrs    number(11); --户籍数
  v_fhjrs    number(11); --非户籍数
  v_count    number(11);
  v_jrpcrs   number(11); --今日人数
  v_jrpchs   number(11); --今日户数
begin
  --for v_c in (select communityid,communitycode,communityname,streetcode,streetname
  -- from ns_census_street_community)
  --loop
  --    v_communitycode := v_c.communitycode;
  --    v_communityname := v_c.communityname;
  --    v_streetcode  := v_c.streetcode;
  --    v_streetname  := v_c.streetname;
  --    v_deptid    := v_c.communityid;
      for v_r in (select * from ns_census_region where status = '1') --where deptid=v_c.communityid)
      loop
          select count(1) zhs,
          count(case when f.status = '1' then 1 else null end) ypchs
          into v_zhs,v_ypchs
            from ns_census_build b,ns_census_region_build br,ns_census_room r,ns_census_family f
          where b.id = br.buildid and b.id = r.buildid and br.regionid=v_r.id and r.status = '1'
            and br.status = '1' and b.status = '1' and r.id = f.roomid;
          select count(1)into v_jrpchs
            from ns_census_build b,ns_census_region_build br,ns_census_room r,ns_census_family f
          where b.id = br.buildid and b.id = r.buildid and br.regionid=v_r.id and r.id = f.roomid
            and br.status = '1' and b.status = '1' and f.status = '1' and r.status = '1'
            and to_char(sysdate, 'yyyy-mm-dd') = to_char(f.utime, 'yyyy-mm-dd');
          v_dpchs := v_zhs - v_ypchs;
          select count(1) into v_jhhs
            from ns_census_build b,ns_census_region_build br,ns_census_family f,ns_census_room r
          where b.id = br.buildid and b.id = r.buildid and br.regionid=v_r.id and r.id = f.roomid
            and r.id = f.roomid and r.sfkf = '3' and r.status = '1'
            and br.status = '1' and b.status = '1';
          select count(1) into v_bhs
            from ns_census_build b,ns_census_region_build br,ns_census_room r,ns_census_family f
          where b.id = br.buildid and b.id = r.buildid and br.regionid=v_r.id and r.id = f.roomid
            and r.id = f.roomid and r.sfkf = '2' and r.status = '1'
            and br.status = '1' and b.status = '1';
          select count(1) into v_khs
            from ns_census_build b,ns_census_region_build br,ns_census_room r,ns_census_family f
          where b.id = br.buildid and b.id = r.buildid and br.regionid=v_r.id and r.id = f.roomid
            and r.id = f.roomid and r.sfkf = '2' and r.status = '1'
            and br.status = '1' and b.status = '1';
          select count(1) into v_zrs
           from ns_census_build b,ns_census_region_build br,ns_census_room r,ns_census_family f,
                ns_census_people p
          where b.id = br.buildid and b.id = r.buildid and br.regionid=v_r.id and r.id = f.roomid
            and p.familyid=f.id and br.status = '1' and b.status = '1' and r.status != '2'
            and f.status != '2' and f.status != '3' and p.status != '2' and p.status != '3';
          select count(1) into v_ypcrs
           from ns_census_build b,ns_census_region_build br,ns_census_room r,ns_census_family f,
                ns_census_people p
          where b.id = br.buildid and b.id = r.buildid and br.regionid=v_r.id and r.id = f.roomid
            and p.familyid=f.id and br.status = '1' and b.status = '1' and r.status = '1'
            and f.status = '1' and p.status = '1';
          --人口性质统计
          for v_xz in (select p.rkxz,count(1) sl
           from ns_census_build b,ns_census_region_build br,ns_census_room r,ns_census_family f,
                ns_census_people p
          where b.id = br.buildid and b.id = r.buildid and br.regionid=v_r.id and r.id = f.roomid
            and p.familyid=f.id and br.status = '1' and b.status = '1' and r.status = '1'
            and f.status = '1' and p.status = '1' group by p.rkxz)
              loop
                if v_xz.rkxz = '1' then
                  v_ndrs := v_xz.sl;
                elsif v_xz.rkxz = '2' then
                  v_xgrs := v_xz.sl;
                elsif v_xz.rkxz = '3' then
                  v_wjrs := v_xz.sl;
                elsif v_xz.rkxz = '4' then
                  v_amrs := v_xz.sl;
                elsif v_xz.rkxz = '5' then
                  v_twrs:=v_xz.sl;
                end if;
              end loop;
          --户籍统计
          select count(case when p.hjdtydzbm = '1' then 1 else null end) hjrs,
                 count(case when p.hjdtydzbm != '1' or p.hjdtydzbm is null then 1 else null end) fhjrs
                 into v_hjrs,v_fhjrs
           from ns_census_build b,ns_census_region_build br,ns_census_room r,ns_census_family f,
                ns_census_people p
          where b.id = br.buildid and b.id = r.buildid and br.regionid=v_r.id and r.id = f.roomid
            and p.familyid=f.id and br.status = '1' and b.status = '1' and r.status = '1'
            and f.status = '1' and p.status = '1';
          --今日人数
          select count(1) into v_jrpcrs
           from ns_census_build b,ns_census_region_build br,ns_census_room r,ns_census_family f,
                ns_census_people p
          where b.id = br.buildid and b.id = r.buildid and br.regionid=v_r.id and r.id = f.roomid
            and p.familyid=f.id and br.status = '1' and b.status = '1' and r.status = '1'
            and f.status = '1' and p.status = '1'
            and to_char(sysdate, 'yyyy-mm-dd') = to_char(p.utime, 'yyyy-mm-dd');
          v_dpcrs := v_zrs - v_ypcrs;

          select count(1) into v_count from NS_CENSUS_TJ_REGION where regionid = v_r.id;
          if v_count > 0 then
             update ns_census_tj_region
                set --communitycode = v_r.communitycode,
                    communityname = v_r.communityname,
                    --streetcode = v_r.streetcode,
                    streetname = v_r.streetname,
                    deptid = v_r.deptid,
                    regionname = v_r.name,
                    zhs = v_zhs,
                    ypchs = v_ypchs,
                    dpchs = v_dpchs,
                    jhhs = v_jhhs,
                    bhs = v_bhs,
                    khs = v_khs,
                    zrs = v_zrs,
                    ypcrs = v_ypcrs,
                    dpcrs = v_dpcrs,
                    jrpcrs = v_jrpcrs,
                    jrpchs = v_jrpchs,
                    ndrs = v_ndrs,
                    xgrs = v_xgrs,
                    wjrs = v_wjrs,
                    amrs = v_amrs,
                    twrs = v_twrs,
                    hjrs = v_hjrs,
                    fhjrs = v_fhjrs
              where regionid = v_r.id;
          else
              insert into ns_census_tj_region
                (communityname, streetname, deptid, regionid, regionname,
                 zhs, ypchs, dpchs, jhhs, bhs, khs, zrs, ypcrs, dpcrs,jrpcrs,
                 jrpchs,ndrs,xgrs,wjrs,amrs,twrs,hjrs,fhjrs)
              values
                ( v_r.communityname, v_r.streetname, v_deptid, v_r.id,
                 v_r.name, v_zhs, v_ypchs, v_dpchs, v_jhhs, v_bhs, v_khs,
                 v_zrs, v_ypcrs, v_dpcrs,v_jrpcrs,v_jrpchs,v_ndrs,v_xgrs,v_wjrs,
                 v_amrs,v_twrs,v_hjrs,v_fhjrs);
          end if;
          commit;
          v_ndrs := 0;
          v_xgrs := 0;
          v_wjrs := 0;
          v_amrs := 0;
          v_twrs := 0;
      end loop;
  --end loop;

end;

 

 

 

第二个存储过程

CREATE OR REPLACE procedure proc_tj_people as

  v_dcrs          varchar2(10); -- 底册人数
  v_djrs          varchar2(10); -- 底册人数
  v_xzrs          varchar2(10); -- 底册人数
  v_dsjrs         varchar2(10); -- 底册人数

  begin
    --删除之前统计数据
    delete from tj_people;

    for v_r in (select r.*, rownum
                from ns_census_region r
               where r.status = '1')
   loop

   select count(case when p.sjly = '0' then 1 else null end),
          count(case when r.status = '1' and f.status = '1' and p.status = '1' then 1 else null end),
          count(case when r.status = '1' and f.status = '1' and p.status = '1' and p.sjly = '2' then 1 else null end),
          count(case when r.status = '1' and f.status = '1' and p.status = '1' and p.sjly = '1' then 1 else null end)
      into v_dcrs,v_djrs,v_xzrs,v_dsjrs
      from ns_census_build        b,
           ns_census_region_build br,
           ns_census_room         r,
           ns_census_family       f,
           ns_census_people       p
     where b.id = br.buildid
       and b.id = r.buildid
       and br.regionid = v_r.id
       and r.id = f.roomid
       and p.familyid = f.id
       and br.status = '1'
       and b.status = '1';

    --插入新的统计数据
    insert into tj_people (
          id,
          name,
          communityname,
          regionname,
          deptid,
          dcrs,
          djrs,
          xzrs,
          dsjrs)
        values
         (v_r.id,
         v_r.streetname,
         v_r.communityname,
         v_r.name,
         v_r.deptid,
         v_dcrs,
         v_djrs,
         v_xzrs,
         v_dsjrs);
        if mod(v_r.rownum, 1000) = 0 then
          commit;
        end if;
      end loop;
  commit;
end;