oracle处理数据

create or replace procedure xs_test_BIRTHDAY(temp_name in varchar2,  temp_num  out number)
 is
 str_birthday      varchar2(200);
 new_birthday      varchar2(10);
   --校友用户
   cursor curTestAlumniUser is
         select *
           from dcp_apps.TEST_alumni_user;

  
    --校友用户行类型   
    rec_curTestAlumniUser curTestAlumniUser%rowtype;  

begin
    --判断游标是否被打开,如果已经打开，首先关闭
  if curTestAlumniUser%isopen then
    --关闭游标
    close curTestAlumniUser;
  end if;
  --打开游标
  open curTestAlumniUser;
  --进行循环
  loop
    --打开游标里的一条记录，赋到游标变量里
    fetch curTestAlumniUser
      into rec_curTestAlumniUser;
    --判断游标里是否还有记录，如果没有记录，关闭游标，退出循环
    if curTestAlumniUser%notfound then
      close curTestAlumniUser;
      exit;
    else
          str_birthday:= rec_curTestAlumniUser.Birshday;

          if (str_birthday is not null) then
              --判断6,7,8
              str_birthday:=trim(str_birthday);
              if length(str_birthday)='6' then
                 new_birthday:=substr(str_birthday,1,4) || '-' || '0' || substr(str_birthday,5,1) || '-' || '0' || substr(str_birthday,6,1);
                 update dcp_apps.TEST_alumni_user tau set tau.birshday_yuan=new_birthday where tau.id=rec_curTestAlumniUser.Id;    
              end if;
              if length(str_birthday)='7' then
                 new_birthday:=substr(str_birthday,1,4) || '-' || '0' || substr(str_birthday,5,1) || '-' || substr(str_birthday,6,2);
                 update dcp_apps.TEST_alumni_user tau set tau.birshday_yuan=new_birthday where tau.id=rec_curTestAlumniUser.Id;    
              end if;
              if length(str_birthday)='8' then
                 new_birthday:=substr(str_birthday,1,4) || '-' ||  substr(str_birthday,5,2) || '-' ||  substr(str_birthday,7,2);
                 update dcp_apps.TEST_alumni_user tau set tau.birshday_yuan=new_birthday where tau.id=rec_curTestAlumniUser.Id;    
              end if;             
          end if;
         
          -- rec_curTestAlumniUser.
          -- update B表 set B表的字段A=rec_source_table.字段b where zgh=rec_source_table.zgh;
      commit;
    end if;
  end loop;
  update test_alumni_user set sex=trim(sex);
 
  update test_alumni_user set sexID=1 where sex='男';
  update test_alumni_user set sexID=2 where sex='女';
  update test_alumni_user set sexID=3 where ( sex is null);
  update test_alumni_user set sexID=4 where (sex='无' or sex is null);
  update test_alumni_user set sexID=3 where sexID is null;
  commit;
 
exception
  when others then
    rollback;

end;