BEGIN
declare yf varchar(6);
declare pingfenyfcount int;
declare cur_pingfenyf cursor for
select pingfenyf from zh_pingfensjglb where zhuangtai='2';
select count(rid) into pingfenyfcount from zh_pingfensjglb where zhuangtai='2';
open cur_pingfenyf;
while pingfenyfcount >=1 do
fetch cur_pingfenyf into yf;
begin
create or replace view view_pingfentj
as select beipingr,sum(defen) as zhongfen,sj.pingfenyf,gz.beipingjs
from zh_pingfenjl left join zh_pingfensjglb sj on pingfensjglid=sj.rid left join zh_pingfenfpgzb gz on pingfenfpgzid=gz.rid
where sj.pingfenyf=yf
group by pingfensjglid,beipingr,pingfenr;
update zh_pingfensjglb set zhuangtai='3' where pingfenyf=pingfenyf;
set pingfenyfcount=pingfenyfcount-1;
end;
end while;
close cur_pingfenyf;
END问题就在yf上面 ,where sj.pingfenyf=yf中的yf不能这样用
报错,说yf是未知变量
该怎么解决呢
declare yf varchar(6);
declare pingfenyfcount int;
declare cur_pingfenyf cursor for
select pingfenyf from zh_pingfensjglb where zhuangtai='2';
select count(rid) into pingfenyfcount from zh_pingfensjglb where zhuangtai='2';
open cur_pingfenyf;
while pingfenyfcount >=1 do
fetch cur_pingfenyf into yf;
begin
create or replace view view_pingfentj
as select beipingr,sum(defen) as zhongfen,sj.pingfenyf,gz.beipingjs
from zh_pingfenjl left join zh_pingfensjglb sj on pingfensjglid=sj.rid left join zh_pingfenfpgzb gz on pingfenfpgzid=gz.rid
where sj.pingfenyf=yf
group by pingfensjglid,beipingr,pingfenr;
update zh_pingfensjglb set zhuangtai='3' where pingfenyf=pingfenyf;
set pingfenyfcount=pingfenyfcount-1;
end;
end while;
close cur_pingfenyf;
END问题就在yf上面 ,where sj.pingfenyf=yf中的yf不能这样用
报错,说yf是未知变量
该怎么解决呢
不知道 什么意思 ....为什么不能
where sj.pingfenyf=yf
这样写呢
把这个 VIEW 拼接成一个字符串 而不是直接执行
然后使用 execute 执行这个字符串是吧 我考虑下