Updating a column of cursor then using that value to select

Question

I have a cursor that queries a table like this

CURSOR Cur IS
       SELECT Emp_No,status
        from Employee
        FOR UPDATE OF status;

Now I would want to update my status in Employee table from another table using the Emp_no. Once I have done this I need to use this status for calling custom business logic and not the original status retrieved by the cursor. What is the best way of going about this? Here is what I have written. I declared a variable called v_status by the way

 FOR Rec IN Cur LOOP

           BEGIN

           UPDATE Employee           
SET status = (select a.status from Employee_Status  where a.Emp_No = rec.Emp_No)
           WHERE CURRENT OF Cur ;
           COMMIT;

           END;

           SELECT status INTO v_status 
           FROM  Employee
           where Emp_No = rec.Emp_No;

            IF(v_status = 'Active') THEN
                   -- Custom Business Logic
                  ELSE  
            -- Business logic

            END IF;    
END LOOP;

What would be a better way to achieve this?

Justin Cave · Accepted Answer · 2011-12-13 19:43:15Z

5

1) I'm hoping in your real code that you don't have a COMMIT in the middle of your loop. Since committing releases the locks held by your transaction, the row-level locks taken out with the FOR UPDATE clause are released and other sessions are free to update the same rows. In later versions of Oracle, you'll get an "ORA-01002: fetch out of sequence" if you do this. In earlier versions of Oracle, this error was ignored which lead to occasionally incorrect results.

2) Do you need to update the EMPLOYEE table on a row-by-row basis? I'd tend to move the update outside of the loop in order to maximize SQL since that's the most efficient way to process data. If your business logic is amenable to it, I'd also suggest doing bulk operations to fetch the data into local collections that your business logic can iterate through in order to minimize context shifts between SQL and PL/SQL.

So, based on your comments, in your example, there should be a predicate in the definition of your cursor, right? Something like this?

CURSOR Cur IS
  SELECT Emp_No,status
    from Employee
   WHERE status IS NULL
      FOR UPDATE OF status;

If so, you'd need that same predicate in the single UPDATE statement (potentially instead of the EXISTS clause). But since you know that you only want to process the rows that your UPDATE statement affected, you can just return those rows into a local collection

DECLARE
  CURSOR cur IS 
     SELECT emp_no, status
       FROM employee
      WHERE status IS NULL;
  TYPE l_employee_array IS
    TABLE OF cur%rowtype;
  l_modified_employees l_employee_array;
BEGIN
  UPDATE employee e
     SET status = (select es.status
                     from employee_status es
                    where es.emp_no = e.emp_no)
   WHERE status IS NULL
     AND EXISTS (SELECT 1
                   FROM employee_status es
                  WHERE es.emp_no = e.emp_no)
  RETURNING emp_no, status
       BULK COLLECT INTO l_modified_employees;
  FOR i IN l_modified_employees.FIRST .. l_modified_employees.LAST
  LOOP
    IF( l_modified_employees(i).status = 'Active' ) 
    THEN
      -- Custom logic 1
    ELSE
      -- Custom logic 2
    END IF;
  END LOOP;
END;

edited Dec 13, 2011 at 19:43

answered Dec 13, 2011 at 19:04

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eosphorus Over a year ago

Justin the table employee has processed and non processed records and the only way to know this is by the fact that non processed records have a status of null. So I only need to update and then subsequently process those records and not the rest. If I updated everything then there would be no way to know which records to process since I cannot modify the employee table to add a column to it.

Justin Cave Over a year ago

@Eosphorus - OK. So in your example, there would be another predicate in your cursor definition to check for a NULL status, right? And you only want to process rows where you are modifying the status? If so, I've updated my answer.

A.B.Cade · Accepted Answer · 2011-12-13 18:54:44Z

1

Why not just simply:

FOR Rec IN Cur LOOP

   SELECT a.status INTO v_status from Employee_Status a where a.Emp_No = rec.Emp_No;

           UPDATE Employee           
           SET status = v_status
           WHERE CURRENT OF Cur ;
           COMMIT;

            IF(v_status = 'Active') THEN
                   -- Custom Business Logic
                  ELSE  
            -- Business logic

            END IF;    
END LOOP;

answered Dec 13, 2011 at 18:54

A.B.Cade

16.9k1 gold badge40 silver badges55 bronze badges

Comments

DCookie · Accepted Answer · 2011-12-13 18:57:48Z

1

You could use the RETURNING clause:

UPDATE employee
   SET status = (SELECT a.status ...)
 WHERE CURRENT OF Cur
RETURNING status INTO v_status;

answered Dec 13, 2011 at 18:57

DCookie

43.7k11 gold badges88 silver badges93 bronze badges

Collectives™ on Stack Overflow

Updating a column of cursor then using that value to select

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related