CURSOR FOR LOOP

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;

Open in new window



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

The actual procedure code is attached . Please suggest the suitable approach to achieve the task.

myProcedure.prd.txt
LVL 9
ronan_40060Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
You code loop also doesn't appear o use v_data.casehflow_date anywhere.


I would just to an insert into select and forget about a cursor:

insert into temp2 (select ...  amount*-1 ... from main_table);
0
ronan_40060Author Commented:
Thanks

Is the cursor For Loop approach is being used correctly ? I was tempted to use explicit cursor
0
slightwv (䄆 Netminder) Commented:
>>being used correctly

Will it 'loop', sure.  However as you have it:  nit is not 'correct'.  You are looping through a cursor setting an object called v_data:

FOR v_data IN   (SELECT TBL_AGG_MANUAL_CASHFLOW_ID ...


Then you never use it.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ronan_40060Author Commented:
Thanks
My objective is   Insert all the data received by a temp table into the main table  
and  Once we have the data in the main table    for those records in the Main Table - Multiple the amount by -1 - Insert the records based on some criteria  to TEMP2 Table

So to achieve above I have to use the Cursor For LOOP ?
Please correct my understanding
0
slightwv (䄆 Netminder) Commented:
>>So to achieve above I have to use the Cursor For LOOP ?

Why?  A single "insert into select" will work:

insert into temp2 table(column1, column2, amount etc...)
select column1, column2, amount*-1, etc... from main_table;

You are using an insert into select inside the loop you have but the loop you have isn't really doing anything.

When you define the loop for v_data, the expectation is you will use those values for something.  You are looping for no reason.  You are inserting the same values every time once for each iteration through the loop.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ronan_40060Author Commented:
excellent
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.