2008/03/07
skate
游标中可以定义变量,下面是一个例子,关于我更新栏目排序的procedure
create or replace
PROCEDURE TEST AS
-- v_id varchar2;
-- v_num number;
-- v_x number;
i number(10,0) :=1;
v_id varchar2(32);
v_num number(10,0);
v_x number(10,0);
cursor cur_ids is
select id,sum from (
select count(id) sum,id from m_category_t where ORDERNUMBER = 0 group by id)
where sum >1;
cursor cur_ids1 is
select categoryid from m_category_t where id=v_id;
begin
open cur_ids;
loop
fetch cur_ids into v_id,v_num;
exit when cur_ids%notfound;
open cur_ids1;
loop
fetch cur_ids1 into v_x;
exit when cur_ids1%notfound;
update m_category_t set ORDERNUMBER=i where categoryid=v_x;
i:=i+1;
end loop;
close cur_ids1;
i:=1;
end loop;
close cur_ids;
END TEST;
-- end --