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;