PL/SQL question

From: Eric Bycer <ebycer_at_mediaone.net>
Date: Fri, 17 Nov 2000 23:54:42 GMT
Message-ID: <B63B2FED.CC7%ebycer@mediaone.net>

Hey,

I have to write a procedure in Oracle 7.1.31 that uses the following two tables:

table tbl_wstr(

   wstr         number,
   status       varchar2,

   statuscode varchar2,
   ....
)

and

table statushistory(

   wstr         number,
   status       varchar2,

   statuscode varchar2,
   statusdate date
)

Where table tbl_wstr is joined in a one-to-many relationship with statushistory. Each time a wstr changes status or statuscode, a new row is appended to the statushistory table.

What I need to do is write a procedure to calculate the days each wstr remains at each status.

I have absolutely no idea how to write it for PL/SQL syntax-wise, but the basic pseudocode would be as follows:

create or replace procedure days_calculator(v_status IN varchar2) AS
DECLARE

   days_count   number := 0;
   temp_start   date;
   temp_date    date;

BEGIN
   for wstr in tbl_wstr loop

      for wstr in statushistory
         where tbl_wstr.wstr = statushistory.wstr loop
         day_count  := 0;
         if statushistory.status = v_status
            then
               temp_start := statusdate from the current row;
      /* next row in statushistory has different status by definition */
               temp_end   := statusdate from the next row;
               days_count := days_count + trunc(temp_end - temp_start);
         end if;
      end loop;
      insert into output_table(wstr, days, status)
             values (wstr, days_count, v_status);

   end loop;
END; then I would call

days_calculator('NEW')
days_calculator('APPROVED')
etc., etc.

and then

select round(avg(days)) from output_table group by status

to get my final output.

Obviously, I would enhance this with code for the statuscode, but I think you get the gist of what I am trying to do. If not, and you want to help, please do not hesitate to e-mail me.

I've looked into cursors, PL/SQL tables, records, and I can't figure out how to this. I spent all day trying to figure out how to even get started. Any help would be greatly appreciated.

Again, if you have any questions, please feel free to e-mail me.          

Thank you very much in advance.

Eric Bycer Received on Fri Nov 17 2000 - 17:54:42 CST