"swoop" <carl.barrett_at_newcastle.gov.uk> wrote in message
news:1117549118.377852.145030_at_g43g2000cwa.googlegroups.com...
> Hi folks, me again.
>
> I have taken on board what everyone suggested in my post last week and
> I really appreciate it.
>
> However, and there is always a however - I have been told by my
> colleague requesting this to use 2 cursors.
> I need to extract records from a table where certain criteria is met
> and then compare 4 fields from those records to 4 fields in another
> table. Where the records don't match I need them extracted into
> another table.
>
> For example.
>
> I want to select *
> >From INVOICE
> Where aa = 1, bb = No, cc=2001;
>
> I then want to compare fields test1, test2, test3 and test4 in table
> INVOICE with fields test1, test2, test3 and test4 in table PAYMENT to
> see if I get a matching record (This tells me if an invoice has been
> paid). If these records do not match then I want the record copied
> from table INVOICE to table NOTPAID.
>
> THE TABLE THAT IS TO HAVE THE RECORDS EXTRACTED TO IS NOT TO BE
> OVERWRITTEN. EVERYTIME THIS PROCEDURE/CODE IS RAN ONLY RECORDS THAT
> HAVE NOT BEEN CHECKED SHOULD BE ADDED TO THE NEW TABLE.
>
> As far as I understand I need 2 cursors, one to populate variables
> based on the Invoice table and the other based on the Payment table and
> then do a comparison on each. Then to Insert the non matching record
> from Invoice into Notpaid and keep looping until we reach the end of
> the records in the Invoice cursor.
>
> Any guidance and example code of something similar would be greatly
> appreciated. Here is code I have started with.
>
> DECLARE
>
> CURSOR tgb_cur IS
>
> SELECT cmpy, supp, entry_date, sysref
> FROM tgbtran
> WHERE tran_type = 1
> AND ordr_rltd_flag = 'Y'
> AND yy = 2005;
>
> v_cmpy tgbtran.cmpy%type;
> v_supp tgbtran.supp%type;
> v_entry_date tgbtran.entry_date%type;
> v_sysref tgbtran.sysref%type;
>
> v_py_cmpy tgbpaym.cmpy%type;
> v_py_supp tgbpaym.supp%type;
> v_py_tran_entry_date tgbpaym.tran_entry_date%type;
> v_py_tran_sysref tgbpaym.tran_sysref%type;
>
> CURSOR tgbp_cur IS
>
> SELECT cmpy, supp, tran_entry_date, tran_sysref
> FROM tgbpaym
> WHERE v_cmpy <> v_py_cmpy
> AND v_supp <> v_py_supp
> AND v_entry_date <> v_py_tran_entry_date
> AND v_sysref <> v_py_tran_sysref;
>
> BEGIN
>
> OPEN tgb_cur;
> LOOP
> FETCH tgb_cur INTO v_cmpy,
> v_supp,
> v_entry_date,
> v_sysref;
> EXIT WHEN tgb_cur%notfound;
>
>
> OPEN tgbp_cur;
> FETCH tgbp_cur INTO v_py_cmpy,
> v_py_supp,
> v_py_tran_entry_date,
> v_py_tran_sysref;
>
> INSERT INTO utgbtran
> VALUES (v_cmpy, v_supp, v_entry_date, v_sysref);
>
> CLOSE tgbp_cur;
> END LOOP;
> CLOSE tgb_cur;
> END;
> /
>
Is the objective to do it slow, why not just do it efficiently? (and easier)
I think your colleague just doesn't have the experience in using sets to
accomplish the task.
Please ask them to justify why they want to do it with 2 cursors? (instead
of 1 statement) I suspect they are used to other databases and are
concerned about "locking". (not something that is a problem in Oracle in
this situation)
Jim
Received on Tue May 31 2005 - 22:36:41 CDT
