Home » SQL & PL/SQL » SQL & PL/SQL » Cursors
Cursors [message #2413] Fri, 12 July 2002 13:21 Go to next message
spat
Messages: 10
Registered: March 2002
Junior Member
I recently learned to use cursors for updates, and large inserts. I need some help getting the cursor below to generate the final results.

The cursor

TRUNCATE TABLE PROD_TA_VOUCHER_HRS_TEMP;
declare
cursor s1 is SELECT A.PAYNO , to_char(A.START_DATE, 'mm/dd/yyyy'), SUM(A.ELAPSED_TIME)
FROM VOUCHER A
WHERE to_char(A.START_DATE, 'mm/dd/yyyy') >= to_char(sysdate - 365 , 'mm/dd/yyyy')
GROUP BY A.PAYNO, to_char(A.START_DATE, 'mm/dd/yyyy');
my_badge VOUCHER.PAYNO%type;
my_sdt VOUCHER.START_DATE%type;
my_ttl VOUCHER.ELAPSED_TIME%type;
begin
open s1;
loop
fetch s1 into my_badge, my_sdt, my_ttl;
exit when s1%notfound;
INSERT INTO PROD_TA_VOUCHER_HRS_TEMP ( PAYNO, START_DATE, TTL ) VALUES
(my_badge, my_sdt, my_ttl );
commit;
end loop;
close s1;
end;
/

Voucher table contains data such as

Badge Start Date Elasped Time
H2230 05/02/2002 8:00 am 1.2
H2230 05/02/2002 9:20 am 4.2
H2230 05/02/2002 3:20 pm 4.2
H2231 etc.

Rusults required are

Badge Start Date Ttl
H2230 05/02/2002 9.6
H2231 05/02/2002 7.5
etc.

With the cursor above I get

Badge Start Date Ttl
H2230 05/02/2002 8:00 am 1.2
H2230 05/02/2002 9:20 am 4.2
H2230 05/02/2002 3:20 pm 4.2
H2231 etc.

Any help that is provided would be greatly appreciated.

Thank you.
Re: Cursors [message #2414 is a reply to message #2413] Fri, 12 July 2002 13:52 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
No need for a cursor here - if you are getting 'unable to extend' rollback segment errors, then make them the right size (larger) for the types of transactions you do.

This whole routine is simply:

insert into prod_ta_voucher_hrs_temp
  (payno, start_date, ttl)
  select payno, trunc(start_date), sum(elapsed_time)
    from voucher
   where start_date >= add_months(sysdate, -12)
   group by payno, trunc(start_date);


Note the removal of:
1) the alias
2) the to_char conversions

and the addition of:
1) trunc of the date
2) add_months (to handle leap years correctly)
Re: Cursors [message #2424 is a reply to message #2413] Mon, 15 July 2002 07:42 Go to previous message
spat
Messages: 10
Registered: March 2002
Junior Member
Thank you Todd

I am not an admin in order to change the rollback segment. But the trunc in the cursor worked perfectly for me.

Thanks again.
Previous Topic: update trigger....plz help me ....
Next Topic: Nested Loop Behaves Surprisingly
Goto Forum:
  


Current Time: Fri Oct 18 08:19:19 CDT 2019