oracle 更新一列光标，然后使用该值进行选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8494310/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Updating a column of cursor then using that value to select
提问by Eosphorus
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
现在我想使用 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;
What would be a better way to achieve this?
实现这一目标的更好方法是什么？
回答by Justin Cave
1) I'm hoping in your real code that you don't have a COMMITin the middle of your loop. Since committing releases the locks held by your transaction, the row-level locks taken out with the FOR UPDATEclause 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.
1）我希望在你的真实代码中你COMMIT的循环中间没有 a 。由于提交会释放您的事务持有的锁，因此使用该FOR UPDATE子句取出的行级锁被释放，其他会话可以自由更新相同的行。在 Oracle 的更高版本中，如果执行此操作，您将获得“ORA-01002: fetch out of sequence”。在早期版本的 Oracle 中，此错误被忽略，这会导致偶尔出现不正确的结果。
2) Do you need to update the EMPLOYEEtable 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.
2) 是否需要EMPLOYEE逐行更新表？我倾向于将更新移到循环之外以最大化 SQL，因为这是处理数据的最有效方式。如果您的业务逻辑适合它，我还建议执行批量操作以将数据提取到您的业务逻辑可以迭代的本地集合中，以最大限度地减少 SQL 和 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 EXISTSclause). 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
如果是这样，您将需要在单个 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;
回答by A.B.Cade
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;
回答by DCookie
You could use the RETURNING clause:
您可以使用RETURNING 子句：
UPDATE employee
SET status = (SELECT a.status ...)
WHERE CURRENT OF Cur
RETURNING status INTO v_status;
