游标使用

最新推荐文章于 2022-07-08 17:33:18 发布

cuimeng6687

最新推荐文章于 2022-07-08 17:33:18 发布

阅读量67

点赞数

声明，此示例多来源于网上牛人所赐，我只是照猫画虎的临摹。
游标中定义的参数只要定义类型，不用定义长度，精度等；

游标使用一
把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;