oracle游标中可以定义变量

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  --