SQL Stored Procedure Code Example

—SQL Stored Procedure Code Example





create or replace procedure projtask (custnum in customer.custno%type) is

--************************************************
-- This procedure lists projects and tasks
-- belonging to a given customer.
--************************************************

-- program variables used for retrieving table data

   cname char(20);
   projnum number(4);
   temp_projnum number(4);
   projtitle varchar2(25);
   tasknum number(4);
   description varchar2(25);
   hours number(4,1);
   sdate date;
   tcount number;
   temp_tcount number;
   hrcount number(4,1);
   totalprojects number;
   totaltasks number;
   totalhours number;

-- Define retrieval cursor to get single customer's projects and tasks
    cursor nexttask is
    select c.custname, p.pjno, p.pjtitle, t.taskno, t.descrip, t.hrs, t.start_date
    from customer c left join project p on custnum=p.custno left join task t on p.pjno=t.pjno
    where c.custno=custnum
    order by p.pjno, t.taskno;
 
begin

   open nexttask;

-- Retrieve first task row
   fetch nexttask into cname, projnum, projtitle, tasknum, description, hours, sdate;

-- Start output display
   dbms_output.put_line('****************************************');
   dbms_output.put_line(cname||' Projects:');
   dbms_output.put_line(chr(10));

-- Retrieve the task count to determine if a project exists in project table
   select count(taskno) into temp_tcount from task where pjno=projnum;

-- test %notfound cursor attribute to see if row returned
   if nexttask%notfound or temp_tcount=0 then
      dbms_output.put_line('No projects currently on file');
      dbms_output.put_line(chr(10));

-- list first project with task descriptions
   else
       dbms_output.put_line('Project: '||projnum||' '||projtitle);
       dbms_output.put_line(chr(10));
       dbms_output.put_line('TaskNo  Description Hours  StartDate');
       dbms_output.put_line('________________________________________');

   -- set task count equal to zero
       tcount:=0;
   -- set total tasks equal to zero
       totaltasks:=0;
   -- temp storage for project number
       temp_projnum:=projnum;
   --set total projects to zero
       totalprojects:=0;
   -- add 1 to project total
       totalprojects:=totalprojects+1;
   -- set total hours to zero
       totalhours:=0;

       loop
           dbms_output.put_line(tasknum|| '  ' ||description||'  '||hours||' '||sdate);
           fetch nexttask into cname, projnum, projtitle, tasknum, description, hours, sdate;
       
           -- test %notfound cursor attribute to see if row returned
           if nexttask%notfound then
              dbms_output.put_line('________________________________________');
              dbms_output.put_line('No tasks currently on file');
              dbms_output.put_line('________________________________________');
           end if;
           exit when nexttask%notfound;

           --increment task counter
           tcount:=tcount+1;
       
           -- if the project number changes, start new task description table
           if temp_projnum!=projnum then
              -- bottom of task description table for previous project
              dbms_output.put_line('________________________________________');
              dbms_output.put_line(chr(10));
         
              -- totals for previous project
              dbms_output.put_line('Number of Tasks: '||tcount);
              dbms_output.put_line('Total Project Hours: '||hrcount);
              dbms_output.put_line('________________________________________');
              dbms_output.put_line(chr(10));
         
     --get total tasks for previous project
              totaltasks:=totaltasks+tcount;
              -- calculate total hours
              totalhours:=totalhours+hrcount;
              -- reset task counter for next project
              tcount:=0;
              -- assign new project number to temp storage variable for projnum
              temp_projnum:=projnum;
              --add 1 to project total
              totalprojects:=totalprojects+1;
         
              -- display next project number and title
              dbms_output.put_line('Project: '||projnum||' '||projtitle);
              dbms_output.put_line(chr(10));

              --determine if tasks exist for project
 
              select count(taskno) into temp_tcount from task where pjno=projnum;

              if temp_tcount>0 then
                 dbms_output.put_line('TaskNo  Description Hours  StartDate');
                 dbms_output.put_line('________________________________________');
              end if;
         
              dbms_output.put_line(tasknum|| '  ' ||description||'  '||hours||' '||sdate);
              fetch nexttask into cname, projnum, projtitle, tasknum, description, hours, sdate;
         
              -- test %notfound cursor attribute to see if row returned
              if nexttask%notfound then
                 dbms_output.put_line('________________________________________');
                 dbms_output.put_line(chr(10));
                 dbms_output.put_line('No tasks currently on file');
                 dbms_output.put_line('________________________________________');
              end if;
              exit when nexttask%notfound;
         
              -- increment task counter for next project   
              tcount:=tcount+1;
           end if;
           select sum(hrs) into hrcount
           from task
           where projnum=pjno;
       end loop;
   end if;
-- add up totals and close display
   dbms_output.put_line('****************************************');

-- only display totals if projects exist
   if totalprojects>0 then
      dbms_output.put_line('Number of Projects: '||totalprojects);
      dbms_output.put_line('Number of Tasks: '||totaltasks);
      dbms_output.put_line('Total Hours: '||totalhours);
      dbms_output.put_line(chr(10));
   end if;
   close nexttask;
end projtask;
/