oracle游标的定义使用

游标中定义的参数只要定义类型，不用定义长度，精度等；

游标使用一:

declare
cursor mycur is --声明游标
select * from a;
myrec a%rowtype; --声明与表字段相同的记录
begin
open mycur;--打开游标
fetch mycur into myrec;
while mycur%found loop
dbms_output.put_line(myrec.col1||':'||myrec.col2);
fetch mycur into myrec;
end loop;
close mycur;
end;
/

游标使用二: 使用参数

declare
cursor mycur(id varchar) is
select col2 from a where col1=id;
myrec a.col2%type; 
begin
open mycur('1');
loop
fetch mycur into myrec;
exit when mycur%notfound;
dbms_output.put_line(myrec);
end loop;
close mycur;
end;
/

游标使用二: 使用参数二for循环，在for循环中不用声明游标，也不用打开关闭

declare
cursor mycur(id varchar) is
select col2 from a where col1=id;
begin
for myrec in mycur('1') loop
dbms_output.put_line(myrec.col2);
end loop;
end;
/

判读游标是否打开，如果没有打开

declare
myrec a.col2%type;
cursor mycur(id varchar) is
select col2 from a where col1=id;
begin
if mycur%isopen then//判读打开
dbms_output.put_line('游标打开了');
else 
open mycur('1'); 
end if;
fetch mycur into myrec;
close mycur;
dbms_output.put_line(myrec);
end;
/
利用游标获得数据行数
declare
t_name varchar(10);
cursor mycur is
select col2 from a;
begin
open mycur;
loop
fetch mycur into t_name;//数据填充到游标
exit when mycur%NOTFOUND or mycur%NOTFOUND IS NULL;
END LOOP;
DBMS_OUTPUT.PUT_LINE(MYCUR%ROWCOUNT);
CLOSE MYCUR;
END;
/

利用游标update数据

declare
cursor cur is
select col1 from a for UPDATE;//for update 是必须的
temp varchar(10);
begin
open cur;
fetch cur into temp;
while cur%FOUND loop
update a set col1=col1||'_why' where current of cur;//current of cur也是必须的
fetch cur into temp;
end loop;
close cur;
end;
/

 

游标中定义的参数只要定义类型，不用定义长度，精度等；

游标使用一:

declare
cursor mycur is --声明游标
select * from a;
myrec a%rowtype; --声明与表字段相同的记录
begin
open mycur;--打开游标
fetch mycur into myrec;
while mycur%found loop
dbms_output.put_line(myrec.col1||':'||myrec.col2);
fetch mycur into myrec;
end loop;
close mycur;
end;
/

游标使用二: 使用参数

declare
cursor mycur(id varchar) is
select col2 from a where col1=id;
myrec a.col2%type; 
begin
open mycur('1');
loop
fetch mycur into myrec;
exit when mycur%notfound;
dbms_output.put_line(myrec);
end loop;
close mycur;
end;
/

游标使用二: 使用参数二for循环，在for循环中不用声明游标，也不用打开关闭

declare
cursor mycur(id varchar) is
select col2 from a where col1=id;
begin
for myrec in mycur('1') loop
dbms_output.put_line(myrec.col2);
end loop;
end;
/

判读游标是否打开，如果没有打开

declare
myrec a.col2%type;
cursor mycur(id varchar) is
select col2 from a where col1=id;
begin
if mycur%isopen then//判读打开
dbms_output.put_line('游标打开了');
else 
open mycur('1'); 
end if;
fetch mycur into myrec;
close mycur;
dbms_output.put_line(myrec);
end;
/
利用游标获得数据行数
declare
t_name varchar(10);
cursor mycur is
select col2 from a;
begin
open mycur;
loop
fetch mycur into t_name;//数据填充到游标
exit when mycur%NOTFOUND or mycur%NOTFOUND IS NULL;
END LOOP;
DBMS_OUTPUT.PUT_LINE(MYCUR%ROWCOUNT);
CLOSE MYCUR;
END;
/

利用游标update数据

declare
cursor cur is
select col1 from a for UPDATE;//for update 是必须的
temp varchar(10);
begin
open cur;
fetch cur into temp;
while cur%FOUND loop
update a set col1=col1||'_why' where current of cur;//current of cur也是必须的
fetch cur into temp;
end loop;
close cur;
end;
/
隐式游标

begin
for cur in(select * from a) loop
dbms_output.put_line(cur.col1||':'||cur.col2);
end loop;
end;
/

游标嵌套

declare
      cursor mycur_all_orders is --声明所有单据头的游标
      select * from tmp_inf_delivery_order t ;
      my_order_rec inf_delivery_order%rowtype; --声明与表字段相同的记录  
      my_rec_item inf_delivery_order_item%rowtype; --声明与表字段相同的记录
      begin
        --先循环单据头的游标
        open mycur_all_orders;--打开单据头游标
          fetch mycur_all_orders into my_order_rec;
          while mycur_all_orders%found loop
          --dbms_output.put_line(my_rec_item.order_number||':'||my_rec_item.client_id);
           /* for cur in(select * from inf_delivery_order_item) loop
                dbms_output.put_line(cur.order_number||':'||cur.client_id);
            end loop;*/
            begin --单独处理一个单据中的行
              declare
                cursor mycur_new_Items is --声明所有新增行项目的游标
                  select * from inf_delivery_order_item t;
              begin
                open mycur_new_Items; --打开游标
                fetch mycur_new_Items into my_rec_item;
                while mycur_new_Items%found loop
                  dbms_output.put_line(my_rec_item.order_number || ':' || my_rec_item.client_id);
                  fetch mycur_new_Items into my_rec_item;
                end loop;
                close mycur_new_Items;
              end;
            end;
          fetch mycur_all_orders into my_order_rec;
          end loop;
          close mycur_all_orders; 
      end;