是这样的,我要在一个触发器中实现一个功能:IF select num from counter where key=aaa 存在 THEN 执行操作; ELSE insert into counter values(xxx,xxx); 执行操作; END IF;上面语句中的if语句的条件表达式怎么写,我知道可以通过游标来判断,但想找一个更简洁的方法, 不知有没有?
定义一个变量 n_count number();select count(*) into n_count from counter where key=aaa; if n_count > 0 then 执行操作; ELSE insert into counter values(xxx,xxx); 执行操作; END IF;
谢谢楼上,已经解决! 附代码: declare -- local variables here n integer; n2 integer; s string(50); begin select count(ssn) into n from userinfo where nickname=:new.nickname; if n>0 then begin select num into n2 from counter where key=:new.nickname; exception when no_data_found then insert into counter values(:new.nickname,1); n2:=0; end; update counter set num=num+1 where key=:new.nickname; n:=n2+1; s:=:new.nickname||'_'||n; else s:=:new.nickname; end if; insert into userinfo values(:new.ssn,s,:new.credit,:new.state,:new.mobile,:new.type,:new.kind); end
declare -- local variables here n integer; n2 integer; s string(50); begin select count(ssn) into n from userinfo where nickname=:new.nickname; if n >0 then update counter set num=num+1 where key=:new.nickname; if sql%notfound then insert into counter values(:new.nickname,1); n2:=0; end if; n:=n2+1; s:=:new.nickname ¦ ¦'_' ¦ ¦n; else s:=:new.nickname; end if; insert into userinfo values(:new.ssn,s,:new.credit,:new.state,:new.mobile,:new.type,:new.kind); end;
执行操作;
ELSE
insert into counter values(xxx,xxx);
执行操作;
END IF;上面语句中的if语句的条件表达式怎么写,我知道可以通过游标来判断,但想找一个更简洁的方法, 不知有没有?
n_count number();select count(*) into n_count from counter where key=aaa;
if n_count > 0 then
执行操作;
ELSE
insert into counter values(xxx,xxx);
执行操作;
END IF;
附代码:
declare
-- local variables here
n integer;
n2 integer;
s string(50);
begin
select count(ssn) into n from userinfo where nickname=:new.nickname;
if n>0 then
begin
select num into n2 from counter where key=:new.nickname;
exception
when no_data_found then
insert into counter values(:new.nickname,1);
n2:=0;
end;
update counter set num=num+1 where key=:new.nickname;
n:=n2+1;
s:=:new.nickname||'_'||n;
else
s:=:new.nickname;
end if;
insert into userinfo values(:new.ssn,s,:new.credit,:new.state,:new.mobile,:new.type,:new.kind);
end
declare
-- local variables here
n integer;
n2 integer;
s string(50);
begin
select count(ssn) into n from userinfo where nickname=:new.nickname;
if n >0 then
update counter set num=num+1 where key=:new.nickname;
if sql%notfound then
insert into counter values(:new.nickname,1);
n2:=0;
end if;
n:=n2+1;
s:=:new.nickname ¦ ¦'_' ¦ ¦n;
else
s:=:new.nickname;
end if;
insert into userinfo values(:new.ssn,s,:new.credit,:new.state,:new.mobile,:new.type,:new.kind);
end;