SQL & PL/SQL

Hi,

i have a stored procedure which checks for a specified user and a specified timespan (from - to) its allocations on tasks. The timespan is cut into 30-minute fragments by the SP.

Very abstract model

TASK (zero to many) REQUIRED_RESOURCE (zero to many) ALLOCATED_USER

The stored procedure returns a ref cursor with the following columns. The TASK_ID can be null which means there is no allocation between that START_TIME and STOP_TIME. Otherwise the user is allocated on that specific task. Because the data is cut into 30-minute fragments, STOP_TIME is always 30 minutes after START_TIME.

START_TIME, STOP_TIME, USER_ID, TASK_ID

All good so far.

I would like to store the data returned by the SP in a table. This allows me for more flexible use of the data. I'm thinking of storing the data as follows (STOP_TIME isn't really needed for storing...):

START_TIME   (pk)
USER_ID        (pk)
TASK_ID       (null)

**How can I somehow 'process' the ref-cursor returned by the SP, so that data in my new table is inserted if the START_TIME + USER_ID combination doesn't exist yet or updated if it already exists?**

Thanks for thinking along!

This is the SP for reference (TRUNC30 is a function that Truncates a DateTime value to the nearest 30-minute part):

create or replace
PROCEDURE REPORT_PLAN_AV_USER
(
from_dt IN date,
to_dt IN date,
sysur_key IN number,
v_reservations OUT INTRANET_PKG.CURSOR_TYPE
)
IS
BEGIN

OPEN v_reservations FOR

  with  
      MONTHS as (select FROM_DT + ((ROWNUM-1) / (24*2)) as DT from DUAL connect by ROWNUM <= ((TO_DT - FROM_DT) * 24*2) + 1), 
      TIMES as (select DT as START_TIME,(DT + 1/48) as STOP_TIME from MONTHS where TO_NUMBER(TO_CHAR(DT,'HH24')) between 8 and 15 and TO_NUMBER(TO_CHAR(DT,'D')) not in (1,7))
  select 
    TIMES.START_TIME,
    TIMES.STOP_TIME,
    T.TASK_ID,
    sysur_key USER_ID,
  from 
    TIMES 
    left outer join (ALLOCATED_USER u INNER JOIN REQUIRED_RESOURCE r ON u.AU_ID = r.RR_ID INNER JOIN TASK t ON r.TASK_ID = t.TASK_ID)
      ON u.USER_ID = sysur_key AND t.PLAN_TYPE = 3 AND TIMES.start_time >= TRUNC30(t.START_DATE) AND TIMES.start_time < TRUNC30(t.FINISH_DATE)
      
  where u.USER_ID is null OR u.USER_ID = sysur_key
  order by START_TIME ASC;
END;