Re: Complex Structures within PL/SQL

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Wed, 23 Apr 2008 11:12:42 -0700 (PDT)
Message-ID: <45c7cc6a-fb82-4759-a74f-dc715c28de5b@z72g2000hsb.googlegroups.com>

On Apr 23, 8:31 pm, Le Tubs <dlaw..._at_yahoo.co.uk> wrote:
> Hi
>
> Apologies if this gets posted twice
>
> I am relatively new to PL/SQL and want to do is find a list of
> duplicate records, then recording the duplicate c_rec id's in some
> sort of structure which I can extract at a later date
> this is what I have got so far ....
>
> TYPE DupID as Table of (
>
> wID NUMBER;
> AID NUMBER;
> BID Number;
> )
>
> select ???? = A.PAYROLL_ID, A.C_REC, B.C_REC from FMAPPING A where
> (SELECT * FROM FMAPPING B where
> A.PAYROLL_ID = B.PAYROLL_ID AND A.C_REC <> B.C_REC);
>
> -- at this point can just extract each row, and I will have access to
> the payroll_id, and the unique c_rec
>
> So is this correct, but how do I load the DupID table with the
> contents of the select statment (I am assuming that the sql is
> correct)
>
> I would ideally like to learn what I'm doing right and wrong, so
> ideally what I'm after are pointers to reference sites, decent example
> sites or books rather than just the answer. Any tips or pointers ectra
> will be greatly appreciated.
>
> Thanking you in advance for your time and consideration.
>
> LeTubs

DECLARE
   TYPE DupIDRow IS RECORD(

        wID FMAPPING.PAYROLL_ID%TYPE,
        AID FMAPPING.C_REC%TYPE,
        BID FMAPPING.C_REC%TYPE

 );
   TYPE DupIDTab IS TABLE OF DupIDRow INDEX BY BINARY_INTEGER;

   DupIDs DupIDTab;

BEGIN
SELECT A.PAYROLL_ID, A.C_REC, B.C_REC
  BULK COLLECT INTO DupIDs
  FROM FMAPPING A, FMAPPING B
 WHERE A.PAYROLL_ID = B.PAYROLL_ID AND A.C_REC <> B.C_REC;  ...
END; Note that if the number of rows returned by the query is large, you may want to use LIMIT clause of the BULK COLLECT and iteratively process collected results in a loop to conserve memory. Also note that your query will return two rows for each pair of duplicates. For example,

(payroll_id,c_rec)
(1,1)
(1,2)

will return

1,1,2
1,2,1

and

(1,1)
(1,2)
(1,3)

will return 6 rows:

1,1,2
1,1,3
1,2,1
1,2,3
1,3,1
1,3,2

Not sure this is what you really need. :) If you only want the "duplicate" rows themselves, you can do it this way:

SELECT * FROM FMAPPING
 WHERE PAYROLL_ID IN
   (SELECT PAYROLL_ID FROM FMAPPING
      GROUP BY PAYROLL_ID HAVING COUNT(*) > 1) and if you only need PAYROLL_IDs that have duplicates, you can use just the query inside the IN ().

Another way to approach the problem (probably more efficient and definitely simpler):

CREATE TABLE MY_DUPLICATES_TABLE
AS
SELECT A.PAYROLL_ID WID, A.C_REC AID, B.C_REC BID   FROM FMAPPING A, FMAPPING B
 WHERE A.PAYROLL_ID = B.PAYROLL_ID AND A.C_REC <> B.C_REC;

No PL/SQL required, single SQL statement does it all, you can then process the results any way you please. If this is going to be a frequent operation and you don't need to keep the query results between sessions, you can make MY_DUPLICATES_TABLE a GLOBAL TEMPORARY table with either ON COMMIT PRESERVE ROWS if you want the data to survive commits in session, or ON COMMIT DELETE ROWS if the data is truly temporary.

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Wed Apr 23 2008 - 13:12:42 CDT