How to create an oracle procedure to find out the distinct records from duplicate and update-CodePudding

I want to know the how can i create an oracle procedure to find out the distinct records from whole table and insert into same table by updating value of another column Suppose the table name is temp and below is the structure of the table

| id         | address              |  key  |ver_id
| 1          | 242  Street          |  123  | 1 
| 2          | 242 Street           | 123   |2
| 3          | 242  Street          |  123  |3
| 4          | 242 Long St          | 456   |4

So,how to write a oracle procedure to select 1 record from first 3 rows for above columns which is duplicate and insert that distinct record at the end of the table by updating ver_id as -1 like below:

| id         | address              |  key  |ver_id**
|  1         | 242  Street          |  123  | 1 
| 2          | 242 Street           | 123   |2
| 3          | 242  Street          |  123  |3
| 4          | 242 Long St          | 456   |4
|  5         | 242  Street          |  123  |-1

I have tried to write the simple procedure to find out the duplicates from table

create or replace PROCEDURE demo (
    key1 IN VARCHAR2
) AS
    CURSOR c_temp IS
    SELECT
        *
    FROM
        temp
    WHERE
        key = key1;
 r_temp c_temp%ROWTYPE;
BEGIN
    OPEN c_temp;
    LOOP
        FETCH c_temp INTO r_temp;
        EXIT WHEN c_temp%notfound;
        dbms_output.put_line('id: '
                             || r_temp.id
                             || ' address: '
                             || r_temp.address);

    END LOOP;

    CLOSE c_temp ;
END;

But above procedure is just fetch the records with duplicate records, but i need help to write the procedure to select distintct records from duplicate and insert into same table with different ver_id as -1

CodePudding user response：

You didn't say how is id column supposed to get its value, so I just created a sequence.

This is sample data:

SQL> select * from temp order by id;

        ID ADDRESS            KEY     VER_ID
---------- ----------- ---------- ----------
         1 242 street         123          1
         2 242 street         123          2
         3 242 street         123          3
         4 242 long st        456          4

SQL> create sequence seq_temp start with 5;

Sequence created.

You don't need PL/SQL procedure for what you're doing; a SQL statement is enough:

data CTE finds duplicates (their rn value is larger than 1)
data2 fetches distinct rows. Why? Because you can't use a sequence along with distinct
the final select composes values that should be inserted

So:

SQL> insert into temp (id, address, key, ver_id)
  2  with data as
  3    (select t.*,
  4       row_number() over (partition by address, key order by id) rn
  5     from temp t
  6    ),
  7  data2 as
  8    (select distinct d.address, d.key
  9     from data d
 10     where d.rn > 1
 11    )
 12  select seq_temp.nextval, address, key, -1
 13  From data2;

1 row created.

Result:

SQL> select * from temp order by id;

        ID ADDRESS            KEY     VER_ID
---------- ----------- ---------- ----------
         1 242 street         123          1
         2 242 street         123          2
         3 242 street         123          3
         4 242 long st        456          4
         5 242 street         123         -1         --> here it is

SQL>