Actually, I want to create an oracle procedure to normalize the table and for that we required a multiple queries/task to perform /execute. Below are the steps for which will help to write an oracle procedure:
step 1) Suppose a table name temp and there are nearly millions of records in it. So get the distinct records from whole table and insert into same table by updating value for ver_id column as -1. Below is the example and table structure to understand
| pid | 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
Expected Resultis below :
select 2 distinct records for duplicate records from above table and insert that distinct record at the end of the table by updating ver_id as -1 like below:
| pid | 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
step 2) Find all parents records of given key in ADDRESS_TEMP table and update them with pid (primary key of temp table) which is newly created in step 1 i.e pid as 5 and 6
ADDRESS_TEMP table (parent of temp table)
pid is the primary key of temp table and foreign key of ADDRESS_TEMP table
|addr_id | ver_id | pid
| 11 | 1 | 1
| 12 | 2 | 2
| 13 | 3 | 3
| 14 | 4 | 4
| 15 | 5 | 5
| 16 | 6 | 6
After Update
|addr_id | ver_id | pid
| 11 | 1 | 1
| 12 | 2 | 2
| 13 | 3 | 3
| 14 | 4 | 4
| 15 | 5 | 5
| 15 | 6 | 6
| 15 | 7 | 7
step 3): Delete all temp table records where key is 123 and 456 (in short delete all duplicate records) whose ver_id is not equal to -1 so the expected result of temp table is like below:
| pid | address | key |ver_id
| 4 | 242 Long St | 456 |4
| 5 | 242 Street | 123 |-1
For the first step ,exeecuted this query as below and its working as expected buti want to perfrom all the above steps in one single procedure .
insert into temp (id, address, key, ver_id)
with data as
(select t.*,
row_number() over (partition by address, key order by id) rn
from temp t
),
data2 as
(select distinct d.address, d.key
from data d where d.rn > 1
)
select seq_temp.nextval, address, key, -1
From data2.`
But above one is just for the step first but we need to create a procedure for all the above 3 steps and also i have tried to add the above insert statement into the procedure and it gets executed but for step2 and 3 it needs to be created
CREATE OR replace PROCEDURE p1 AS
cursor c_temp IS
SELECT * FROM temp ;
r_temp c_temp%ROWTYPE;
BEGIN
OPEN c_temp;
LOOP
FETCH c_temp INTO r_temp;
EXIT WHEN c_temp%NOTFOUND;
insert into temp (id, address, key, ver_id)
with data as
(select t.*,
row_number() over (partition by address, key order by id) rn
from temp t
),
data2 as
(select distinct d.address, d.key
from data d where d.rn > 1
)
select seq_temp.nextval, address, key, -1
From data2;
END LOOP;
CLOSE c_temp;
END;
Your instructions are quite confusing and I'm not sure that I got it completely. Anyway, below is the answer (with comments in the code). Even If it isn't exactly what you wanted - the logic and the structure of the code should be ok. You will have to adjust it to your context anyyway.
Table temp data before and after:
/* TEMP before
P_ID ADDRESS A_KEY VER_ID
1 242 Street 123 1
2 242 Street 123 2
3 242 Street 123 3
4 242 Long St 456 4
TEMP after
P_ID ADDRESS A_KEY VER_ID
4 242 Long St 456 4
5 242 Street 123 -1 */
Table TEMP_ADDRESS before and after:
/* TEMP_ADDRESS before
ADDR_ID VER_ID P_ID
11 1 1
12 2 2
13 3 3
14 4 4
TEMP_ADDRESS after
ADDR_ID VER_ID P_ID
11 1 5
12 2 5
13 3 5
14 4 4 */
Here is the code with comments...
SET SERVEROUTPUT ON
DECLARE
-- Declare cursor to fetch you distinct records from table TEMP
CURSOR c_temp IS SELECT Count(*) "CNT", ADDRESS, A_KEY FROM temp WHERE VER_ID > 0 GROUP BY ADDRESS, A_KEY HAVING Count(*) > 1 ORDER BY A_KEY;
-- ------------- Cursor records below ----------------
-- CNT ADDRESS A_KEY
-- ------ ------------ ----------
-- 3 242 Street 123
-- ----------------------------------------------------
cSet c_temp%ROWTYPE;
sq VarChar2(1) := ''''; -- single quote character (4 single quotes) -- using it to construct Sql commands
mSql VarChar2(512) := '';
mID TEMP.P_ID%TYPE;
mAddr TEMP.ADDRESS%TYPE;
mKey TEMP.A_KEY%TYPE;
BEGIN
Select Nvl(Max(P_ID), 0) Into mID From TEMP; -- Last (max) P_ID from taable TEMP --> 4 (empty table would return 0)
--
OPEN c_temp;
LOOP
FETCH c_temp INTO cSet;
EXIT WHEN c_temp%NOTFOUND;
mID := mID + 1; -- add 1 to mID for insert into TEMP
-- constructing INSERT commands for every cursor record
mSql := 'INSERT INTO TEMP(P_ID, ADDRESS, A_KEY, VER_ID) VALUES(' || mID || ', ' || sq || cSet.ADDRESS || sq || ', ' || cSet.A_KEY || ', -1)';
--
-- --------------- constructed command(s) for cursor record(s) ---------------------------------
-- INSERT INTO TEMP(P_ID, ADDRESS, A_KEY, VER_ID) VALUES(5, '242 Street', 123, -1)
--
Execute Immediate mSql; -- execute created INSERT command(s)
--
-- While you still have m_ID and cursor record's ADDRESS and A_KEY use them to update parent table TEMP_ADDRESS
Begin
UPDATE TEMP_ADDRESS SET P_ID = mID WHERE P_ID IN(Select P_ID FROM TEMP WHERE ADDRESS = cSet.ADDRESS And A_KEY = cSet.A_KEY And VER_ID > 0);
-- after that you can delete records from table temp that are not needed any more
DELETE FROM TEMP WHERE P_ID IN(Select P_ID FROM TEMP WHERE ADDRESS = cSet.ADDRESS And A_KEY = cSet.A_KEY And VER_ID > 0);
Commit;
Exception
WHEN OTHERS THEN
Rollback; -- if anything went wrong - Rollback and send a message
DBMS_OUTPUT.PUT_LINE('ERR - UPDATE or DELETE ' || Chr(10) || SQLERRM);
End;
--
END LOOP;
CLOSE c_temp;
Exception
WHEN OTHERS THEN
Rollback; -- if anything went wrong - Rollback and send a message
DBMS_OUTPUT.PUT_LINE('ERR - INSERT ' || Chr(10) || SQLERRM);
END;
Regards...