oracle存储过程

create or replace procedure XS_TEST_UPDATE_BAC is
  cursor curTestUpdateBac is
    select id,
           substr(bachelor_edu,
                  instr(bachelor_edu, '从') + 1,
                  instr(bachelor_edu, '年到') - 2) as startYear,
           substr(substr(bachelor_edu, instr(bachelor_edu, '年到') + 2),
                  1,
                  instr(substr(bachelor_edu, instr(bachelor_edu, '年到') + 2),
                        '年') - 1) as endYear,
           substr(substr(bachelor_edu, instr(bachelor_edu, '年到') + 2),
                  instr(substr(bachelor_edu, instr(bachelor_edu, '年到') + 2),
                        '年') + 1) as yuanxi,
           bachelor_edu
      from test_alumni_user
     where bachelor_edu <> '从年到年'
       and bachelor_edu <> '无'
       and instr(bachelor_edu, '年到') > 0;

  --校友用户类型
  rec_curTestUpdateBac curTestUpdateBac%rowtype;

begin
  --判断游标是否被打开,如果已经打开，首先关闭
  if curTestUpdateBac%isopen then
    --关闭游标
    close curTestUpdateBac;
  end if;
  --打开游标
  open curTestUpdateBac;
  --进行循环
  loop
    --打开游标里的一条记录，赋到游标变量里
    fetch curTestUpdateBac
      into rec_curTestUpdateBac;
    --判断游标里是否还有记录，如果没有记录，关闭游标，退出循环
    if curTestUpdateBac%notfound then
      close curTestUpdateBac;
      exit;
    else
      update test_alumni_user u
         set u.bachelor_edu_begin_day=rec_curTestUpdateBac.Startyear,
             u.bachelor_edu_end_day = rec_curTestUpdateBac.Endyear,
             u.bachelor_departments = rec_curTestUpdateBac.Yuanxi
       where u.id = rec_curTestUpdateBac.Id;
    end if;
    commit;
  end loop;
exception
  when others then
    rollback;
end XS_TEST_UPDATE_BAC;