Home > Blockchain >  How to create a single oracle procedure to perform multiple query task
How to create a single oracle procedure to perform multiple query task

Time:11-08

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;

CodePudding user response:

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...

  • Related