Hello ,

Can anyone help with the stored procedures code for the below plsql query..Just need to insert stored procedures for the query and hopefully have it run..
Thanks









QU
List the full name of each player, the tournaments they played in, and their overall average score.

set serveroutput on;

declare

cursor dept is

select distinct score.fname ||' '|| score.lname full_name,
substr(e.event_name, 1, 30) event_name,
score.avg_score avg_score
from
player_tournament pt, event e,
(select distinct p.fname, p.lname, p.player_id, avg_score
from player p, player_stats ps
where p.player_id = ps.player_id
and ps.year = '2002') score
where score.player_id = pt.player_id
and pt.event_id = e.event_id
and substr(sched_date, 8, 2) = '02'
order by 1 ;

deptvar dept%rowtype;

num1 number := 25;
num2 number := 30;
num3 number := 15;
var1 varchar2(30);
var2 varchar2(30);
count1 number;
begin
dbms_output.enable(1000000000000000000);
dbms_output.put_line('Golf Database Group');

open dept;
fetch dept into deptvar;
var1:=deptvar.full_name;

while dept%found

loop

if (var1=var2) then
count1:=count1+1;
dbms_output.put_line( rpad(' - ',25)||' '||deptvar.event_name);
else
dbms_output.put_line('');
dbms_output.put_line(rpad('Fullname',25)||' '||rpad('EventName',40)||' '||rpad('AverageScore',15));
dbms_output.put_line(rpad('-',25,'-')||' '||rpad('-',40,'-')||' '||rpad('-',15,'-'));
dbms_output.put_line(rpad(deptvar.full_name,num1)| |' '||rpad(deptvar.event_name,40)||' '||rpad(deptvar.avg_score,num3));
end if;

if (var2 is not null) then
var1:=var2;
end if;

fetch dept into deptvar;
var2:=deptvar.full_name;
end loop;
close dept;
end;
/