CREATE TABLE My_toys
(
ID VARCHAR2 (5),
name VARCHAR2,
Price Number
);
--Insert test data
INSERT into my_toys values (' G001 ', ' electric blanket ', 145);
INSERT into my_toys values (' G002 ', ' bikes ', 327);
INSERT into my_toys values (' G003 ', ' toothpaste ', 14.5);
INSERT into my_toys values (' G004 ', ' quilts ',);
INSERT into my_toys values (' G005 ', ' thermos ',);
INSERT into my_toys values (' G006 ', ' slippers ', 22.5);
--Package specification (the package must first be declared to create the package)
Create or Replace package Toyspack
as
procedure Updatetoyprice;
function Avgtoyprice return number;
End Toyspack;
--Package body
Create or Replace package body Toyspack
as
procedure Updatetoyprice
as
avgprice Number: = Avgtoyprice;
begin
While (Avgprice <=) Loop
--Cyclic update
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 the v_avg from My_toys;
return V_avg;
End Avgtoyprice;
End Toyspack;
--Call
Execute toyspack. Updatetoyprice;
Select Toyspack. Avgtoyprice from dual;
Reprint Address: http://wenwen.soso.com/z/q363392651.htm