两个表间考试成绩同步

create or replace procedure bat_bay_jb_kstb2 is
  --获取所有的记
  cursor cur is
    select bay.*
      from bat_bay_jb bay
     where bay.kstbbz = 0
       and bay.yxbz = '1'
       and bay.bmzt = '1'
          union
          select bay.*
            from bat_bay_jb bay, bat_pxjg_zyjnksbm b
           where bay.kstbbz = 0
             and bay.yxbz = '1'
             and bay.bmzt is null
             and bay.gmsfhm = b.gmsfhm;
      --and bay.gmsfhm in ('362329197505097413'); --'321321197302287234', '330125195205054611');
  --cursor cur is select * from bat_bay_jb j where rownum < 10;
  cur_test sys_refcursor;

  item_bay   bat_bay_jb%rowtype;
  item_ksbm  bat_pxjg_zyjnksbm%rowtype;
  item_sgzj  bat_bay_sgzj%rowtype;
  bayzdyzt   number(1); --0未打印，1已打印
  count_ksbm number(1) := 0; --报名记录
  v_zkzh       varchar2(30) :='';

begin

  if cur%isopen = false then
    open cur;
  end if;

  fetch cur
    into item_bay;

  while cur%found loop

    select z.zkzh
      into v_zkzh
      from (select z.zkzh
              from bat_pxjg_zkz z
             where z.sfzh = item_bay.gmsfhm
             order by z.kssj desc) z
     where rownum = 1;

    select count(*)
      into count_ksbm
      from Bat_Pxjg_Zyjnksbm b
     where b.gmsfhm = item_bay.gmsfhm
       and b.kszt = '3';

    if count_ksbm > 0 then
      --防止没有记录
      OPEN cur_test FOR 'select b.* from Bat_Pxjg_Zyjnksbm b where b.kszt = ''3'' and b.gmsfhm = ''' || item_bay.gmsfhm || '''';
      --考试通过的记录
      loop
        --存在通过的记录
        fetch cur_test
          into item_ksbm;
        exit when cur_test%notfound;
        if item_ksbm.bayzdyzt = '1' --已打印
         then
          select b.*
            into item_sgzj
            from Bat_Bay_Sgzj b
           where b.gmsfhm = item_bay.gmsfhm;
          update bat_bay_jb j
             set --j.scgajg = item_ksbm.
                   j.zkzh = v_zkzh,
                 j.bmrq    = substr(item_ksbm.bmrq, 0, 8),
                 j.ksdd    = item_ksbm.ksdd,
                 j.kssj    = substr(item_ksbm.kssj, 0, 8),
                 j.kscj    = item_ksbm.kscj,
                 j.bazgzbh = item_sgzj.sgzjbh,
                 --j.bayzydj = item_ksbm
                 j.zgzfzrq = item_sgzj.fzrq,
                 j.zgzfzjg = item_sgzj.glm,
                 j.kstbbz  = 1
           where j.ryywbm = item_bay.ryywbm;
          commit;
        else
          --未打印
          update bat_bay_jb j
             set --j.zkzh = item_ksbm.j.scgajg = item_ksbm.
                 j.zkzh = v_zkzh,
                 j.bmrq = substr(item_ksbm.bmrq, 0, 8),
                 j.ksdd = item_ksbm.ksdd,
                 j.kssj = substr(item_ksbm.kssj, 0, 8),
                 j.kscj = item_ksbm.kscj
           where j.ryywbm = item_bay.ryywbm;
          commit;
        end if;
      end loop;

    else
      select count(*)
        into count_ksbm
        from Bat_Pxjg_Zyjnksbm b
       where b.gmsfhm = item_bay.gmsfhm
         and b.kszt = '2';
      if count_ksbm > 0 then
        --是否有不通过的记录存在
        OPEN cur_test FOR 'select b.* from(select b.* from Bat_Pxjg_Zyjnksbm b where b.kszt = ''2'' and b.gmsfhm = ''' || item_bay.gmsfhm || ''' order by b.cjsj desc) b where rownum = 1';
        loop
          --存在记录
          fetch cur_test
            into item_ksbm;
          exit when cur_test%notfound;
          update bat_bay_jb j
             set --j.zkzh = item_ksbm.j.scgajg = item_ksbm.
                 j.zkzh = v_zkzh,
                 j.bmrq = substr(item_ksbm.bmrq, 0, 8),
                 j.ksdd = item_ksbm.ksdd,
                 j.kssj = substr(item_ksbm.kssj, 0, 8),
                 j.kscj = item_ksbm.kscj
           where j.ryywbm = item_bay.ryywbm;
          commit;
        end loop;
      else
        select count(*)
          into count_ksbm
          from Bat_Pxjg_Zyjnksbm b
         where b.gmsfhm = item_bay.gmsfhm
           and b.kszt = '1';
        if count_ksbm > 0 then
          --是否有报名的信息
          OPEN cur_test FOR 'select b.* from(select b.* from Bat_Pxjg_Zyjnksbm b where b.kszt = ''1'' and b.gmsfhm = ''' || item_bay.gmsfhm || ''' order by b.cjsj desc) b where rownum = 1';
          loop
            --存在记录
            fetch cur_test
              into item_ksbm;
            exit when cur_test%notfound;
            update bat_bay_jb j
               set --j.zkzh = item_ksbm.j.scgajg = item_ksbm.
                   j.zkzh = v_zkzh,
                   j.bmrq = substr(item_ksbm.bmrq, 0, 8),
                   j.ksdd = item_ksbm.ksdd,
                   j.kssj = substr(item_ksbm.kssj, 0, 8),
                   j.kscj = item_ksbm.kscj
             where j.ryywbm = item_bay.ryywbm;
            commit;
          end loop;
        end if;
      end if;
    end if;

    fetch cur
      into item_bay;

  end loop;
  close cur;

Exception

  when others then
    insert into bat_bay_tb_err
      (id, gmsfhm, create_date, yxbz)
    values
      (bat_bay_tb_err_seq.nextval, item_bay.gmsfhm, sysdate, '1');
    update bat_bay_jb j
       set j.kstbbz = 2
     where j.gmsfhm = item_bay.gmsfhm
       and j.yxbz = '1';
    commit;

    close cur;
    bat_bay_jb_kstb2(); --递归

end bat_bay_jb_kstb2;

oracle存储过程变量名注意最好用"v_"符号开头，不然可能导致编译无法识别该变量名称（原因应该是和表中原来存在字段的名称相同），而将这个变量使用的地方滤去。

CREATE OR REPLACE PROCEDURE test_05_04_01(v_cust_id in number) IS

  a       number := 0;
  b number :=0;
  --cust_id number := 1000037;

begin

  SELECT count(*)
    into a
    FROM ins_prod_normal_prod pp
   WHERE pp.acct_id = v_cust_id;
  dbms_output.put_line(a);

  SELECT count(*)
    into a
    FROM ins_prod_normal_prod pp
   WHERE pp.acct_id = 1157766;
  dbms_output.put_line(a);

end;