Re: PL/SQL question

From: Matt Foster <matt_foster_uk_at_yahoo.co.uk>
Date: Mon, 20 Nov 2000 12:32:47 +0000
Message-ID: <3A1919EF.6F9F76B6@yahoo.co.uk>

Eric Bycer wrote:
>
> 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

Not sure if I've got the full gist of this but is this the kind of thing you are looking for?

 create or replace procedure days_calculator(v_status IN varchar2)  AS
 DECLARE     CURSOR c1 IS
    SELECT *
    FROM wstr

    CURSOR c2 (p_wstr IN NUMBER, p_status IN VARCHAR2) IS     SELECT *
    FROM status_history
    WHERE wstr = p_wstr
    AND status = p_status
    ORDER BY statusdate

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

 BEGIN
    for r1 in c1 loop

       days_count := 0;

       for r2 in c2(r1.wstr, v_status) loop

                temp_start := statusdate from the current row in c2;
       /* next row in statushistory has different status by definition

*/

                SELECT MIN(statusdate)
                INTO temp_end
                FROM status_history
                WHERE wstr = r1.wstr
                AND   statusdate > temp_start;

                days_count := days_count + (trunc(temp_end) -

TRUNC(temp_start));

       end loop;
       insert into output_table(wstr, days, status)
              values (r1.wstr, days_count, v_status);

       commit;

    end loop;
 END; Regards,

Matt

-- 
matt_foster_uk_at_yahoo.co.uk

Received on Mon Nov 20 2000 - 06:32:47 CST