多看几个例子，对游标有更深了解
乘法表
declare
i pls_Integer;
j pls_Integer;
begin
for i in 1..9 loop
for j in 1..i loop
dbms_output.put(i||'乘以'||j||'等于'||i*j||' ');
end loop for_j;
dbms_output.new_line;
end loop for_i;
end;
隐式游标
begin
update fw.math set n1 = 1 where n1 = 5;
if sql%found then
dbms_output.put_line('成功更新');
else
dbms_output.put_line('编号未找到');
end if;
end;
declare
begin
update fw.math set n1=111 where n2=222;
if sql%found then
dbms_output.put_line('已经更新');
else
dbms_output.put_line('没找到对应的n2的值，更新失败');
end if;
end;
begin
update fw.math set n1 = 333 where n2>1;
dbms_output.put_line('更新了'||sql%rowcount||'行');
rollback;
end;
declare
n1_var fw.math.n1%type;
n2_input fw.math.n2%type;
begin
n2_input := '&请输入n2的值';
select n1 into n1_var from fw.math where n2=n2_input;
if sql%rowcount> 0 then
dbms_output.put_line('根据n2的值查找的记录中的n1的值是:'||n1_var);
end if;
exception when others then
dbms_output.put_line(sqlerrm);
end;
显式游标
declare
n1_var fw.math.n1%type;
cursor math_cur is
select n1 from fw.math where max <10;
begin
open math_cur;
loop
fetch math_cur into n1_var;
exit when math_cur%notfound;
dbms_output.put_line(math_cur%rowcount||'-'||'n1的值是:'||n1_var);
end loop;
close math_cur;
rollback;
end;
declare
cursor math_cur is
select * from fw.math where max<10;
math_var fw.math%rowtype;
begin
open math_cur;
dbms_output.put_line('max<10的记录如下:
n1 n2 max');
loop
fetch math_cur into math_var;
exit when math_cur%notfound;
dbms_output.put_line(math_cur%rowcount||'--'||math_var.n1||' '||math_var.n2||' '||math_var.max);
end loop;
close math_cur;
end;
用显式游标删除或更新行，修改将max<10的记录中所有n1的值加1
declare
cursor math_cur is
select n1 from fw.math where max<10
for update of n1;
new_n1 fw.math.n1%type;
begin
open math_cur;
loop
fetch math_cur into new_n1;
exit when math_cur%notfound;
update fw.math set n1=new_n1+1
where current of math_cur;
dbms_output.put_line(math_cur%rowcount||'-'||'n1更新前的值是:'||new_n1);
dbms_output.put_line(math_cur%rowcount||'-'||'n1更新后的值是:'||(new_n1+1));
end loop;
close math_cur;
commit;
end;
删除
declare
n1_var fw.math.n1%type;
cursor math_cur is
select n1 from fw.math where max<4
for update of n1;
begin
open math_cur;
loop
fetch math_cur into n1_var;
exit when math_cur%notfound;
delete from fw.math where current of math_cur;
dbms_output.put_line('已经将n1='||n1_var||'的记录删除');
end loop;
dbms_output.put_line('删除了'||math_cur%rowcount||'行');
close math_cur;
commit;
end;
带参数的显示游标
根据输入参数按where max=?查找对应信息
declare
cursor math_cur(max_var fw.math.max%type) is
select n1,n2 from fw.math where max=max_var;
n1_var fw.math.n1%type;
n2_var fw.math.n2%type;
max_input varchar2(20);
begin
max_input := '&请输入max的值';
open math_cur(max_input);
loop
fetch math_cur into n1_var,n2_var;
exit when math_cur%notfound;
dbms_output.put_line('根据max='||max_input||'得到的n1的值是:'||n1_var||' '
||'n2的值是:'||n2_var);
end loop;
dbms_output.put_line('记录有'||math_cur%rowcount||'条');
close math_cur;
end;
循环游标
注：math_rec是记录索引，相当于在declare部分做如下声明
type math_type is record
(
n1_var fw.math.n1%type,
n2_var fw.math.n2%type
)
math_rec math_type;
declare
cursor math_cur is
select n1,n2 from fw.math;
begin
for math_rec in math_cur
loop
dbms_output.put_line('n1的值是:'
||math_rec.n1||'n2的值是:'||math_rec.n2);
end loop;
end;
带参数循环游标
根据输入参数按where n1=?查找对应信息
declare
cursor math_cur(n1_var fw.math.n1%type) is
select n1,n2,max from fw.math where n1=n1_var;
n1_input fw.math.n1%type;
begin
n1_input := '&请输入n1的值';
for math_rec in math_cur(n1_input) loop
dbms_output.put_line('根据n1='||n1_input||'查出的记录里的n1的值是:'||math_rec.n1
||' n2的值是:'||math_rec.n2||' max的值是:'||math_rec.max);
end loop;
end;
REF游标变量
根据输入参数查找2张表的信息
declare
type math_ref is ref cursor;
math_cur math_ref;
input_str varchar2(20);
math_var fw.math%rowtype;
math2_var fw.math2%rowtype;
begin
input_str := '&请输入表名查看math表或math2表的信息';
if input_str = 'math' then
dbms_output.put_line('====以下是math表的信息===');
dbms_output.put_line('n1 n2 max');
open math_cur for
select * from fw.math;
loop
fetch math_cur into math_var;
exit when math_cur%notfound;
dbms_output.put_line(math_var.n1||' '||math_var.n2||' '||math_var.max);
end loop;
close math_cur;
elsif input_str = 'math2' then
dbms_output.put_line('====以下是math2表的信息===');
dbms_output.put_line('m1 m2');
open math_cur for
select * from fw.math2;
loop
fetch math_cur into math2_var;
exit when math_cur%notfound;
dbms_output.put_line(math2_var.m1||' '||math2_var.m2);
end loop;
close math_cur;
end if;
end;
动态SQL+REF游标变量
根据客户输入的能接受的最高价格查找低于这个价格的商品信息
declare
price_var fw.product.price%type;
type pro_ref is ref cursor;
pro_cur pro_ref;
pro_rec fw.product%rowtype;
begin
price_var := '&请输入能让你接受的价位';
open pro_cur for 'select * from fw.pro
where price < :high_price order by price desc'
using price_var;
dbms_output.put_line('价格低于'||price_var||'的商品有:');
loop
fetch pro_cur into pro_rec;
exit when pro_cur%notfound;
dbms_output.put_line('商品ID:'||pro_rec.pid||' 商品价格:'||pro_rec.price);
end loop;
close pro_cur;
end;
