Using collections instead of individual cursors 1

[Welshbird]

Hi guys - its me again!

I'm trying to write a procedure that goes and calculates a 'score' based on records in two different tables.

At the moment, whilst it does do what I need, it seems to be lots of code, and I've only performed the calculations on two fields at the moment rather than the 13 from each table that I need to use, but I was just testing the process.

I understand that I might be able to use collections to do this, but I just can't get my head around the syntax to do it at the moment.

This is the code I have written so far, it as I say it does appear to work:

CREATE OR REPLACE procedure test_more_calc authid current_user
as
  CURSOR C_TVF IS
   SELECT destcode FROM test_tvf_fuzz;
  CURSOR C_SURG is
   select surgcount from test_tvf_fuzz;
  CURSOR C_MILES1 is
   select miles1 from test_tvf_fuzz;
  Cursor C_RSURG is
   select surgcount from test_rx_fuzz;
  CURSOR C_RMILES1 is
   select miles1 from test_rx_fuzz;
  v_tvf number;
  v_surg float;
  v_rsurg float;
  v_miles1 float;
  v_rmiles1 float;
  v_score float;
  v_score1 float;
BEGIN
   OPEN C_TVF;
   OPEN C_SURG;
   OPEN C_RSURG;
   OPEN C_MILES1;
   OPEN C_RMILES1;
-- temp loop is a function which counts how many
-- times I think I need to loop
   for loop_index in 1..temp_loop
loop
   FETCH C_TVF into v_tvf;
   fetch C_SURG into v_surg;
   Fetch C_RSURG into v_rsurg;
   Fetch C_MILES1 into v_miles1;
   Fetch C_RMILES1 into v_rmiles1;
-- Max_Surg_Count is a function to calculate that figure
-- and so is max_miles
   v_score:=  1-(abs(v_surg - v_rsurg)/max_surg_count);
   v_score1:= 1-(abs(v_miles1 - v_rmiles1)/max_miles);
   DBMS_OUTPUT.put_line ('Record is now ' || v_tvf || ' and score is '|| v_score || ' and ' || v_score1);
END LOOP;
   CLOSE C_TVF;
   CLOSE C_SURG;
   CLOSE C_RSURG;
   CLOSE C_MILES1;
   CLOSE C_RMILES1;
END;
/

I'm just thinking that it will become a little difficult to follow (and probably not as efficient!) to end up with 26 cursors so I'd really appreciate any help.



Fee.

"The question should be, is it worth trying to do, not can it be done"

 

[Thargy]

Dima,

er, yes of course, but I didn't think to try and translate it - doh!

You have both elegantly revealed my ignorance of just what google is capable of.

I shall retire hurt, and sulk in a corner somewhere.

Tharg

Grinding away at things Oracular

 

[Advocate]

ok, its the season to be off-thread ...

'Nollaig Shona Duit'

spot the similarity and language ... us celts stick together ...




regards, david - no such thing as problems - only solutions.