create function secfunc(v_pric in number, v_qty in number) return number is
begin
if v_qty > 30 then
return(v_pric * 0.75);
else
return(v_pric * 0.9);
end if;
end;
drop function secfunc
select * from productinfo
select p.proname, p.proprice, p.quantity, secfunc(p.proprice, p.quantity)
from productinfo p
-------------静态游标--------------------------
声明
declare cursor my_cursor is select * from productinfo;
打开
open my_cursor
读取数据
fetch my_cursor into ****;
--实例------------------------loop ---------------------
declare
cursor cur_product is
select * from productinfo;
cur_prorow productinfo%rowtype;
begin
open cur_product;
loop
fetch cur_product
into cur_prorow;
if cur_product%found then
Dbms_Output.put_line('产品:' || cur_prorow.proid || ' ' ||
cur_prorow.proname);
else
exit;
end if;
end loop;
close cur_product;
end;
-------------------集合类型取多条记录---------------
declare
cursor cur_collect is
select * from productinfo;
type pro_tab is table of productinfo%rowtype;
pro_tabrd pro_tab;
begin
open cur_collect;
loop
fetch cur_collect bulk collect
into pro_tabrd;
for i in 1 .. pro_tabrd.count loop
Dbms_Output.put_line('产品:' || pro_tabrd(i).proid || ' ' || pro_tabrd(i)
.proname);
end loop;
exit when cur_collect%notfound;
end loop;
close cur_collect;
end;
-----for loop--------- 更简单的方式----建议使用此种形式-----------------------
declare
cursor cur_product is
select * from productinfo;
begin
for cur_product1 in cur_product
loop
Dbms_Output.put_line('产品:' || cur_product1.proid || ' ' ||
cur_product1.proname);
Dbms_Output.put_line(cur_product%rowcount);
end loop;
end;
--------------------游标的属性用来实现对脚本的流程控制---------------------------------------------------------
-----------隐式游标---------------------------------------------------------------------------------------------
/* 隐式游标为PL/SQL自动管理
名字默认为SQL
select和dml操作产生隐式游标
隐式游标的属性值始终为最新执行的sql语句*/
属性:
%isopen 始终为false
%found 是否影响到了数据
%notfound 与%found相反
%rowcount 影响到的数据的函数