oracle带游标的存储过程

create or replace procedure XS_TEST_ADD19
is
  bachelor_edu varchar2(2000);
  new_bachelor_edu varchar2(2000);
  aa varchar2(2000);
  bb varchar2(2000);
  edu_length int;
  str_bachelor varchar2(2000);
  cursor curTestAlumniDep19 is

      select *   from   dcp_apps.TEST_alumni_user tau       
       where
       1=1 and substr(tau.bachelor_edu,1,1)='从'  and  substr(tau.bachelor_edu,2,1)  in('9','8','7','6','5','4')
        and substr(tau.bachelor_edu,5,1)='到' and substr(tau.bachelor_edu,6,1) in ('9','8','7','6','5','4');
    --校友用户类型
    rec_curTestAlumniDep19 curTestAlumniDep19%rowtype;
begin
   --判断游标是否被打开,如果已经打开，首先关闭
  if curTestAlumniDep19%isopen then
    --关闭游标
    close curTestAlumniDep19;
  end if;
  --打开游标
  open curTestAlumniDep19;
  --进行循环
    loop
     --打开游标里的一条记录，赋到游标变量里
    fetch curTestAlumniDep19
      into rec_curTestAlumniDep19;
    --判断游标里是否还有记录，如果没有记录，关闭游标，退出循环
    if curTestAlumniDep19%notfound then
      close curTestAlumniDep19;
      exit;
    else
        bachelor_edu:=rec_curTestAlumniDep19.Bachelor_Edu;
        if(bachelor_edu is not null)then
         --判断
         bachelor_edu:=trim(bachelor_edu);
         edu_length:=length(bachelor_edu);
         new_bachelor_edu:=substr(bachelor_edu,1,1);
         aa:=substr(bachelor_edu,2,4);
         bb:=substr(bachelor_edu,6,edu_length);
         str_bachelor:=new_bachelor_edu||'19'||aa||'19'||bb;
         update dcp_apps.TEST_alumni_user tau set tau.bachelor_edu=str_bachelor where tau.id=rec_curTestAlumniDep19.id;
       
        end if;
       
       
    end if;
         
      commit;

    end loop;
exception
  when others then
    rollback;
end XS_TEST_ADD19;