如何创建一个oracle过程，从重复和更新中找出不同的记录

Question

我想知道如何创建一个oracle过程，从整个表中找出不同的记录，并通过更新另一列的值插入到同一个表中假设表名为temp，下面是表的结构

| 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

因此，如何编写一个oracle过程，从上面重复的列的前3行中选择1条记录，并通过更新ver_id为-1将该不同的记录插入表的末尾，如下所示：

| 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 

我试着写一个简单的程序来从表中找出重复项

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;

但上面的过程只是获取具有重复记录的记录，但我需要帮助编写从重复记录中选择distincitct记录并插入到具有不同ver_id的同一表中的过程，如-1

Answer 1

您没有说明id列应该如何获得其值，所以我只是创建了一个序列。

这是样本数据：

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.

您所做的工作不需要PL/SQL过程；SQL语句就足够了：

• dataCTE查找重复项(其rn值大于1(
• data2获取不同的行。为什么？因为不能将序列与distinct一起使用
• 最后的CCD_ 6组成了应该插入的值

所以：

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.

结果：

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>