CURSOR FOR LOOP

Question

Friends

I am writing a procedure that does the following

1> Insert all the data received by a temp table into the main table 2 Once we have the data in the main table - Multiple the amount by -1 - Insert the records to TEMP2 Table

To achieve the above task

my Procedure looks like

CREATE OR REPLACE PROCEDURE MyProcesure(USER        IN VARCHAR2,
                                        VO_ERROR_CODE  OUT NUMBER,
                                        VO_ERROR_DESC  OUT nocopy VARCHAR2) IS

    v_data      VARCHAR2(2000);     

BEGIN


    VO_ERROR_CODE     := 0;
    VO_ERROR_DESC     := 'Success';

-- Insert all the data received by a temp table  into the main table 

INSERT INTO MAIN_TABLE (
COLUMN1,
COLUMN2,
COLUMN3,
COLUMN4)
SELECT M,COL1,
       M.COL2,
       M.COL3,
       M.COL4
FROM TEMP M 
WHERE M.A = VARIABLE1 ; 

-- Once we have the data in the main table 
 - Multiple the amount by -1 
  - Insert the records to TEMP2 Table
FOR v_data in
( SELECT COL1, COL2, COL3, COl4 FROM MAIN TABLE )   

LOOP

    v_data.COL2 := v_data.COL2 * -1 ; 


INSERT INTO TEMP2 
      (       COL1,
          COL2,
          COL3,
          COL4          
      )
    SELECT SELECT M.COL1, M.COL2, M.COL3, M.COl4 FROM MAIN TABLE; 

     GROUP BY M.COL1;

 END;

I have used the Oracle CURSOR FOR LOOP , please let me know if this is perfect or I can use explicit cursor as well. Thanks

Answer 1

I'm kind of trying to understand what you want, but even the syntax is not valid... in that loop (reading as pseudo-code) what you are doing is re-inserting all the records in the temp2 table for "every" record in the main_table, so basically what you are doing is multiplying the records...

I guess what you want is to multiply COL2 data by -1, so you should remove the loop and just put

INSERT INTO TEMP2 (COL1,COL2,COL3,COL4) SELECT M.COL1, M.COL2*-1, M.COL3, M.COl4 FROM MAIN_TABLE;

besides check what is what you want with that GROUP BY M.COL1 there.

BUT first than everything you should study the pl/sql syntax and its main concepts.