游标使用
游标中定义的参数只要定义类型，不用定义长度，精度等；
游标使用一
把SCOTT.EMP 中的数据打印出来。
declare
cursor mycur is --声明游标
select * from scott.emp;
myrec scott.emp%rowtype; --声明与表字段相同的记录
begin
open mycur;
fetch mycur
into myrec;
while mycur%found loop
dbms_output.put_line(myrec.empno || ', ' || myrec.job || ', ' || myrec.sal);
fetch mycur
into myrec;
end loop;
close mycur;
end;
游标使用二: 使用参数
declare
cursor mycur(id varchar) is
select a.empno from scott.emp a where a.job=id;
myrec scott.emp.empno%type;
begin
open mycur('SALESMAN');
loop
fetch mycur into myrec;
exit when mycur%notfound;
dbms_output.put_line(myrec);
end loop;
close mycur;
end;
游标使用二: 使用参数二for循环，在for循环中不用声明游标，也不用打开关闭
declare
cursor mycur(ejob varchar) is
select e.ename from scott.emp e where e.job = ejob;
begin
for myrec in mycur('SALESMAN') loop
dbms_output.put_line(myrec.ename);
end loop;
end;
判断游标是否打开，如果没有，打开
declare
myrec scott.emp.job%type;
cursor mycur(ejob varchar) is
select scott.emp.ename from scott.emp where scott.emp.job = ejob;
begin
if mycur%isopen then
dbms_output.put_line('游标打开了');
else
open mycur('SALESMAN');
end if;
fetch mycur
into myrec;
close mycur;
dbms_output.put_line(myrec);
end;
--利用游标UPDATE数据 将所有的名字都改成了 老张_why
---Where Current Of语句允许你更新或者是删除最后由cursor取的记录
declare
cursor cur is
select scott.emp.ename from scott.emp for update;
temp varchar(10);
begin
open cur;
fetch cur
into temp;
while cur%found loop
update scott.emp
set scott.emp.ename = '老张' || '_why'
where current of cur;
fetch cur
into temp;
end loop;
close cur;
end;
--隐式游标
begin
for cur in (select * from scott.emp) loop
dbms_output.put_line(cur.ename);
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;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29828180/viewspace-1258023/ ，如需转载，请注明出处，否则将追究法律责任。
