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;