oracle procedure demo

CREATE OR REPLACE PACKAGE BODY SPK_A2_SPLOG IS

  /**
   生成企业评价信息表，由明细表汇总而来
  **/
  PROCEDURE INIT_SPLOG IS
    tmp_kf       number(15);
    n_number     number(6) := 0;
    jbf          number(3) := 100; --基本分，暂定为100
    pjdj1        varchar2(50); --评价等级1
    pjdj2        varchar2(50); --评价等级2
    pjdj3        varchar2(50); --评价等级3
    pjdj4        varchar2(50); --评价等级4

  BEGIN
    -- 初始化
    --update qiyeshixxx a set a.koufengzhi = xingzhcf_fz; ----行政处罚分数
    --  update qiyeryxx a set a.jiafengzhi = rongyubz_fz; ----荣誉表彰分数

    update qiyeshixxx a
       set a.koufengzhi =
           (select b.fengzhi
              from pingjiagcz b
             where a.lx = b.lx)
     where exists (select 1
              from pingjiagcz q
             where a.lx = q.lx);
    commit;
    delete from QIYEPJ;
    ---- 初始化企业评价管理表QIYEPJ，分三种情况
    --1:荣誉信息表和处罚信息表中都有 bf1:1
    --初始化处罚得分
    insert into QIYEPJ
      (qyid, QIYMC, PINGJATZZ, PINGJIASJ, bf1)
      select qyid, QIYMC, PINGJATZZ, PINGJIASJ, bf1
        from (select max(a.qyid) qyid,a.bf4 QIYMC,
                     nvl(sum(a.KOUFENGZHI), 0) PINGJATZZ,
                     sysdate PINGJIASJ,
                     1 bf1
                from qiyeshixxx a
               where exists (select 1 from qiyeryxx b where b.bf4 = a.bf4)
               group by a.bf4);
    commit;
    --加上荣誉表彰得分
    for cd in (select qiymc, qyid, PINGJATZZ from QIYEPJ where bf1 = 1) loop
      select count(1) into n_number from qiyeryxx a where a.bf4 = cd.qiymc;
      if n_number > 0 then
        select nvl(sum(JIAFENGZHI), 0)
          into tmp_kf
          from qiyeryxx a
         where a.bf4 = cd.qiymc
         group by a.bf4;

        update QIYEPJ a
           set PINGJATZZ = cd.pingjatzz + tmp_kf
         where a.qyid = cd.qyid
           and a.bf1 = 1;
      end if;
    end loop;
    commit;
    --加上基本分
    update QIYEPJ a set a.pingjatzz = jbf + a.pingjatzz where a.bf1 = 1;
   /*** 
   --2：处罚信息表里有，荣誉信息表里没有 bf1:2
    insert into QIYEPJ
      (qyid, QIYMC, PINGJATZZ, PINGJIASJ, bf1)
      select qyid, QIYMC, PINGJATZZ, PINGJIASJ, bf1
        from (select max(a.qyid) qyid,a.bf4 QIYMC,
                     nvl(sum(a.KOUFENGZHI), 0) PINGJATZZ,
                     sysdate PINGJIASJ,
                     2 bf1
                from qiyeshixxx a
               where not exists
               (select 1 from qiyeryxx b where b.bf4 = a.bf4)
               group by a.bf4);
    commit;
    --加上基本分
    update QIYEPJ a set a.pingjatzz = jbf - a.pingjatzz where a.bf1 = 2;
    --3:荣誉信息表里有，处罚信息表里没有 bf1:3
    insert into QIYEPJ
      (qyid, QIYMC, PINGJATZZ, PINGJIASJ, bf1)
      select qyid, QIYMC, PINGJATZZ, PINGJIASJ, bf1
        from (select max(a.qyid) qyid,a.bf4 QIYMC,
                     nvl(sum(a.JIAFENGZHI), 0) PINGJATZZ,
                     sysdate PINGJIASJ,
                     3 bf1
                from qiyeryxx a
               where not exists
               (select 1 from qiyeshixxx b where b.bf4 = a.bf4)
               group by a.bf4);
    commit;
    --加上基本分
    update QIYEPJ a set a.pingjatzz = jbf + a.pingjatzz where a.bf1 = 3;
    commit;
    ****/
    --算等级,回写处罚信息表和荣誉信息表的qyid
    select a.pingjiadj into pjdj1 from pingjiagdenj a where a.lx = '1';
    select a.pingjiadj into pjdj2 from pingjiagdenj a where a.lx = '2';
    select a.pingjiadj into pjdj3 from pingjiagdenj a where a.lx = '3';
    select a.pingjiadj into pjdj4 from pingjiagdenj a where a.lx = '4';
    for cur in (select q.qyid, q.pingjatzz, q.qiymc from QIYEPJ q) loop
      if cur.pingjatzz >= 90 then
        update QIYEPJ a set a.pingjiadj = pjdj1 where a.qyid = cur.qyid;
      elsif cur.pingjatzz >= 80 and cur.pingjatzz < 90 then
        update QIYEPJ a set a.pingjiadj = pjdj2 where a.qyid = cur.qyid;
      elsif cur.pingjatzz >= 70 and cur.pingjatzz < 80 then
        update QIYEPJ a set a.pingjiadj = pjdj3 where a.qyid = cur.qyid;
      elsif cur.pingjatzz < 70 then
        update QIYEPJ a set a.pingjiadj = pjdj4 where a.qyid = cur.qyid;
      end if;
      update qiyeshixxx z1 set z1.qyid = cur.qyid where z1.bf4 = cur.qiymc;
      update qiyeryxx z2 set z2.qyid = cur.qyid where z2.bf4 = cur.qiymc;
    end loop;

    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
  END;

  --生成企业处罚详细信息
  PROCEDURE INIT_QIYEPINGJIACF IS
    qymc   varchar2(500);
    shiyou varchar2(500);
    jiguan varchar2(100);
    v_flag number(3) := 0;
    cfdj   varchar2(10);
    v_id   number(15);
  begin
    --先清空QIYESHIXXX
    delete from QIYESHIXXX;
    for a in (select id, NAME from c_logic_table) loop
      for col in (select cc.name_desc, cc.name
                    from c_logic_column cc
                   where cc.logic_table_id = a.id) loop
        if col.name_desc = '处罚事由' then
          shiyou := col.name;
          v_flag := 2;
        end if;
        if col.name_desc = '处罚机关' then
          jiguan := col.name;
        end if;
        if col.name_desc = '企业名称' then
          qymc := col.name;
        end if;
        if col.name_desc = '处罚等级' then
          cfdj := col.name;
        end if;
        --生成行政处罚详细信息  lx: 1:刑事处罚  2：行政处罚
        if v_flag = 2 then
          select SEQ_QIYESHIXXX_ID.Nextval into v_id from dual;
          execute immediate ('insert
    into QIYESHIXXX(ID,BF4, CHUFASHIY,CHUFAJG,BF3,lx) select' || v_id || ',' || qymc || ',' ||
                            shiyou || ',' || jiguan || ',' || cfdj || ',' || 2 ||
                            'from mbk_' || a.name ||
                            ' c,SSBZ_DM_LBXX b  where c.rwid = b.tableid and b.lbdm=2');
        end if;
      end loop;
    end loop;

    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
  END;

END;