oracle存储过程0009

create or replace procedure XS_TEST_UPDATE_BAC is
  str_BeginStr varchar2(20);
  str_EndStr   varchar2(20);
  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 dcp_apps.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
      if (rec_curTestUpdateBac.Startyear != 'undefined' or
         rec_curTestUpdateBac.Endyear != 'undefined' or
         rec_curTestUpdateBac.Yuanxi != 'undefined') then
         --修改本科字段全角字符为半角字符
         update dcp_apps.TEST_alumni_user a1 set a1.bachelor_edu=to_single_byte(a1.bachelor_edu)  where a1.id=rec_curTestUpdateBac.Id;
         str_BeginStr := substr(rec_curTestUpdateBac.Startyear, 1, 1);
         str_EndStr:=substr(rec_curTestUpdateBac.Endyear,1,1);
       if (str_BeginStr!='0' and str_BeginStr!='1' and str_BeginStr!='2') then
            update test_alumni_user u
            set u.bachelor_edu_begin_day = '19'||rec_curTestUpdateBac.Startyear,
                u.bachelor_edu_end_day   = rec_curTestUpdateBac.Endyear,              
                u.bachelor_departments   = rec_curTestUpdateBac.Yuanxi
                where u.id = rec_curTestUpdateBac.Id;          
         else
             if(str_BeginStr='0')then
              update dcp_apps.test_alumni_user u
                 set u.bachelor_edu_begin_day = '20'||rec_curTestUpdateBac.Startyear,
                 u.bachelor_edu_end_day   = rec_curTestUpdateBac.Endyear,
                 u.bachelor_departments   = rec_curTestUpdateBac.Yuanxi
                 where u.id = rec_curTestUpdateBac.Id;           
             else
             update dcp_apps.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;   
         end if;
        
         if(str_EndStr!='0' and str_EndStr!='1' and str_EndStr!='2')then
           update dcp_apps.test_alumni_user u
           set
               u.bachelor_edu_end_day   = '19'||rec_curTestUpdateBac.Endyear,
               u.bachelor_departments   = rec_curTestUpdateBac.Yuanxi
             where u.id = rec_curTestUpdateBac.Id;
         else
             if(str_EndStr='0')then
               update dcp_apps.test_alumni_user u
                 set
                     u.bachelor_edu_end_day   = '20'||rec_curTestUpdateBac.Endyear,
                     u.bachelor_departments   = rec_curTestUpdateBac.Yuanxi
                     where u.id = rec_curTestUpdateBac.Id;
             end if;    
         end if;      
      end if;
    end if;
    commit;
  end loop;
exception
  when others then
    rollback;
end XS_TEST_UPDATE_BAC;