小计一次oracle存储过程的使用

Oracle 存储过程

功能要求，更新账号字段(账号有唯一约束）
如果“用户”表“姓名”字段不重复，则“帐号”字段改为“姓名”字段值，否则改为“英文名”字段值

create or replace procedure wd.updateZh
is 
    CURSOR collection1 IS select yhid,nvl(YH.xm,RY.xm) xm,ywm from DC.RY,WD.YH where ryid=yhid and zh is null;
    sameNum number;
    existNum number;
begin  
    FOR rec IN collection1 LOOP 
		select count(xm) into sameNum from wd.yh where xm = rec.xm;
		if sameNum > 1 then/*存在姓名相同是要用ywm去更新账号*/
			if rec.ywm is null then/*存在ywm是空的情况*/
				select count(zh) into existNum from wd.yh where zh like rec.xm||'%';
				if existNum > 0 then
					existNum:=existNum+1;/*必须要加一存在第一次插入zh是zhangsan1，第二个人员的xm是zhangsan的情况*/
					update wd.yh set zh = rec.xm||existNum where yhid=rec.yhid;
				else
					update wd.yh set zh = rec.xm where yhid=rec.yhid;
				end if;
			else/*ywm不为空的情况*/
				select count(zh) into existNum from wd.yh where zh like rec.ywm||'%';
				if existNum > 0 then
					existNum:=existNum+1;/*必须要加一存在第一次插入zh是zhangsan1，第二个人员的xm是zhangsan的情况*/
					update wd.yh set zh = rec.ywm||existNum where yhid=rec.yhid;
				else
					update wd.yh set zh = rec.ywm where yhid=rec.yhid;
				end if;
			end if;
		else/*没有同名的情况用xm去更新账号*/
			select count(zh) into existNum from wd.yh where zh = rec.xm;
			if existNum > 0 then
				update wd.yh set zh = rec.xm||existNum where yhid=rec.yhid;
			else 
				update wd.yh set zh = rec.xm where yhid = rec.yhid;
			end if;
		end if;
	end LOOP;  
	commit;
end updateZh; 
/


begin
  WD.updateZh();
end;
/