游标基本语法
数据准备
create table t_pricetable
(
id number primary key,
price number(10, 2),
ownertypeid number,
minnum number,
maxnum number
);
insert into t_pricetable
values (1, 2.45, 1, 0, 5);
insert into t_pricetable
values (2, 3.45, 1, 5, 10);
insert into t_pricetable
values (3, 4.45, 1, 10, null);
有参数游标
declare
v_pricetable t_pricetable%rowtype;
cursor cur_pricetable is
select * from t_pricetable where ownertypeid=1;
begin
open cur_pricetable;
loop
fetch cur_pricetable into v_pricetable;
exit when cur_pricetable%notfound;
DBMS_OUTPUT.PUT_LINE('价格:'||v_pricetable.price||'吨位:'||v_pricetable.minnum||v_pricetable.maxnum);
end loop;
close cur_pricetable;
end;
无参数游标
declare
v_pricetable t_pricetable%rowtype;
cursor cur_pricetable(o_type number) is
select * from t_pricetable where ownertypeid=o_type;
begin
open cur_pricetable(2);
loop
fetch cur_pricetable into v_pricetable;
exit when cur_pricetable%notfound;
DBMS_OUTPUT.PUT_LINE('价格:'||v_pricetable.price||'吨位:'||v_pricetable.minnum||v_pricetable.maxnum);
end loop;
close cur_pricetable;
end;
for 简化游标使用
declare
cursor cur_pricetable(o_type number) is
select * from t_pricetable where ownertypeid=o_type;
begin
for i in cur_pricetable(1) loop
DBMS_OUTPUT.PUT_LINE('价格:'||i.PRICE||', 吨位:'||i.MINNUM||'--'||i.MAXNUM);
end loop;
end;