更新光标的列，然后使用该值进行选择

Question

我有一个查询这样的表的游标

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

现在我想使用 Emp_no 从另一个表更新 Employee 表中的状态。完成此操作后，我需要使用此状态来调用自定义业务逻辑，而不是游标检索到的原始状态。解决这个问题的最佳方法是什么？这是我写的。我顺便声明了一个名为 v_status 的变量，

 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;

实现此目的的更好方法是什么？

Answer 1

1）我希望在您的实际代码中，您的循环中间没有 COMMIT 。由于提交会释放事务持有的锁，因此使用 FOR UPDATE 子句取出的行级锁将被释放，其他会话可以自由更新相同的行。在 Oracle 的更高版本中，如果执行此操作，您将收到“ORA-01002: fetch out of order”错误。在早期版本的 Oracle 中，此错误被忽略，这会导致偶尔出现不正确的结果。

2) 您是否需要逐行更新EMPLOYEE表？我倾向于将更新移到循环之外，以便最大限度地利用 SQL，因为这是处理数据的最有效方法。如果您的业务逻辑适合它，我还建议执行批量操作以将数据获取到业务逻辑可以迭代的本地集合中，以便最大限度地减少 SQL 和 PL/SQL 之间的上下文转换。

因此，根据您的评论，在您的示例中，光标的定义中应该有一个谓词，对吗？像这样的东西吗？

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

如果是这样，您需要在单个 UPDATE 语句中使用相同的谓词（可能而不是 EXISTS 子句）。但由于您知道您只想处理 UPDATE 语句影响的行，因此您可以将这些行返回到本地集合中

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;

Answer 2

为什么不简单地：

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;

Answer 3

您可以使用 RETURNING 子句：

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