create or replace procedure discount
as
cursor c_group_discount
is
select distinct s.course_no, c.description
from SECTION s, ENROLLMENT e, COURSE c
where s.SECTION_ID = e.SECTION_ID
and c.COURSE_NO = s.COURSE_NO
group by s.course_no, c.description, e.SECTION_ID, s.SECTION_ID
having count(*) >= 8;
begin
for r_group_discount in c_group_discount
loop
update COURSE
set cost = cost * .95
where COURSE_NO = r_group_discount.COURSE_NO;
DBMS_OUTPUT.PUT_LINE('a 5% discount has been given to '||
r_group_discount.COURSE_NO||' '||
r_group_discount.DESCRIPTION);
end loop;
end;
--这样执行
begin
discount;
end;
参考
https://stackoverflow.com/questions/1854427/how-to-execute-an-oracle-stored-procedure