create procedure pro as cursor t_sor is select hs_no,hs_no_ser,lag(hs_no,1,hs_no) over(order by hs_no,hs_no_ser) lag_hs_no,lag(hs_no_ser,1,hs_no_ser) over(order by hs_no,hs_no_ser) lag_hs_no_ser from tabname order by hs_no,hs_no_ser; num number:=0; begin for v_sor in t_sor loop if hs_no=lag_hs_no and hs_no_ser=lag_hs_no_ser then update tabname set QUOTA_SER=num where hs_no,hs_no_ser in (v_sor.hs_no,v_sor.hs_no_ser); else num:=num+1; update tabname set QUOTA_SER=num where hs_no,hs_no_ser in (v_sor.hs_no,v_sor.hs_no_ser); end if; end loop; end; /
to beckhambobo(beckham)select hs_no,hs_no_ser,lag(hs_no,1,hs_no) over(order by hs_no,hs_no_ser) lag_hs_no,lag(hs_no_ser,1,hs_no_ser) over(order by hs_no,hs_no_ser) lag_hs_no_ser from tabname order by hs_no,hs_no_ser;调试的时候说这句话“over(order” 这里有问题!我也不是很明白这句话的意思?
具体HS_NO,HS_NO_SER 的条件我上面说的应该很清楚了阿
as
cursor t_sor is
select hs_no,hs_no_ser,lag(hs_no,1,hs_no) over(order by hs_no,hs_no_ser) lag_hs_no,lag(hs_no_ser,1,hs_no_ser) over(order by hs_no,hs_no_ser) lag_hs_no_ser from tabname order by hs_no,hs_no_ser;
num number:=0;
begin
for v_sor in t_sor loop
if hs_no=lag_hs_no and hs_no_ser=lag_hs_no_ser then
update tabname set QUOTA_SER=num where hs_no,hs_no_ser in (v_sor.hs_no,v_sor.hs_no_ser);
else
num:=num+1;
update tabname set QUOTA_SER=num where hs_no,hs_no_ser in (v_sor.hs_no,v_sor.hs_no_ser);
end if;
end loop;
end;
/