Oracle 存储过程
功能要求,更新账号字段(账号有唯一约束)
如果“用户”表“姓名”字段不重复,则“帐号”字段改为“姓名”字段值,否则改为“英文名”字段值
注意:如果存在跨用户查询会报错,需要先授权
GRANT SELECT ON dc.ry TO wd;
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;
/