create table my_toys
(
id varchar2(5),
name varchar2(20),
price number
);
--插入测试数据
insert into my_toys values('G001','电热毯',145);
insert into my_toys values('G002','自行车',327);
insert into my_toys values('G003','牙膏',14.5);
insert into my_toys values('G004','棉被',105);
insert into my_toys values('G005','热水瓶',65);
insert into my_toys values('G006','拖鞋',22.5);
--包规范(必须先声明包在创建包体)
create or replace package toyspack
as
procedure UpdateToyPrice;
function AvgToyPrice return number;
end toyspack;
--包主体
create or replace package body toyspack
as
procedure UpdateToyPrice
as
avgPrice number := AvgToyPrice;
begin
while (avgPrice <= 400) loop
--循环更新
update my_toys set price=
case
when price*1.1<500 then price*1.1
else price
end;
avgPrice := AvgToyPrice;
commit;
end loop;
end UpdateToyPrice;
function AvgToyPrice return number
as
v_avg number;
begin
select avg(price) into v_avg from my_toys;
return v_avg;
end AvgToyPrice;
end toyspack;
--调用
execute toyspack.UpdateToyPrice;
select toyspack.AvgToyPrice from dual;
转载地址:http://wenwen.soso.com/z/q363392651.htm