--游标(fetch在循环外面)
--静态游标(fetch在循环里面,用loop包裹)
--静态游标(使用行变量)
--动态游标(强类型)
--动态游标(弱类型):弱类型不需要指定返回的类型
declare cursor cu_pro is
select id,pname,price from product;
pid product.id%type;
pname product.pname%type;
price product.price%type;
begin
open cu_pro;
fetch cu_pro into pid,pname,price;
while cu_pro%found loop
dbms_output.put_line('编号:'||pid||',名称:'||pname||',价格:'||price);
fetch cu_pro into pid,pname,price;
end loop;
close cu_pro;
end;
--静态游标(fetch在循环里面,用loop包裹)
declare cursor cu_product is
select id,pname,price from product;
pid product.id%type;
pname product.pname%type;
price product.price%type;
begin
open cu_product;
loop
fetch cu_product into pid,pname,price;
exit when cu_product%notfound;--exit必须存在于exit中
dbms_output.put_line('编号:'||pid||',名称:'||pname||',价格:'||price);
end loop;
close cu_product;
end;
--静态游标(使用行变量)
declare cursor cu_product is
select * from product;
pro product%rowtype;
begin
open cu_product;
loop
fetch cu_product into pro;
exit when cu_product%notfound;
dbms_output.put_line('编号:'||pro.id||',名称:'||pro.pname||',价格:'||pro.price);
end loop;
close cu_product;
end;
--动态游标(强类型)
begin
declare
type pro_cu is ref cursor
return product%rowtype;--返回类型(强制类型)
e_count number;
product_cu pro_cu;--游标变量
products product%rowtype;--行变量
begin
select count(*) into e_count from product where pname='ronaldo';
case
when e_count>0 then
open product_cu for select * from product where pname='ronaldo'; --游标打开指定的sql查询
when e_count>2 then
open product_cu for select * from product;
else
open product_cu for select * from product;
end case;
loop
fetch product_cu into products;
exit when product_cu%notfound;
dbms_output.put_line('编号:'||products.id||',名称:'||products.pname||',价格:'||products.price);
end loop;
end;
end;
--动态游标(弱类型):弱类型不需要指定返回的类型
begin
declare
type pro_cus is ref cursor;
e_count number;
emps emp%rowtype;
products product%rowtype;
pro_cu pro_cus;
begin
select count(*) into e_count from product where pname='ronaldo';
case
when e_count>0 then
open pro_cu for select * from product where pname='ronaldo';
loop
fetch pro_cu into products;
exit when pro_cu%notfound;
dbms_output.put_line('编号:'||products.id||',名称:'||products.pname||',价格:'||products.price);
end loop;
else
open pro_cu for select * from emp;
loop
fetch pro_cu into emps;
exit when pro_cu%notfound;
dbms_output.put_line('姓名:'||emps.ename||'工作:'||emps.job);
end loop;
end case;
end;
end;