使用oracle嵌套游标的奇怪行为 [英] Strange behaviours with oracle nested cursors
问题描述
下面是我编写的使用嵌套游标的存储过程。
Below is stored procedure I have written which used nested cursor.
create or replace
PROCEDURE SP_RUN_EMPLOYEE_UPDATES
(
IN_DATE IN VARCHAr2
)
IS
update_sql varchar2(4000);
employee_id BI_EMPLOYEE_UPDATE.employee_id%TYPE;
effective_date date ;
created_by number;
created_on date;
comments varchar2(4000);
CURSOR
employees
IS
SELECT distinct(employee_id) FROM BI_EMPLOYEE_UPDATE WHERE EFFECTIVE_DATE = to_date(IN_DATE,'dd-mm-yy') AND EXECUTED = 'N' AND ACTIVITY_ID = '0';
CURSOR
e_updates
IS
SELECT * FROM BI_EMPLOYEE_UPDATE WHERE EFFECTIVE_DATE = to_date(IN_DATE,'dd-mm-yy') AND EXECUTED = 'N' AND ACTIVITY_ID = '0' and employee_id = employee_id ;
BEGIN
OPEN employees;
LOOP
effective_date := '';
created_by := '';
created_on := '';
comments := '';
employee_id := '';
FETCH employees into employee_id;
EXIT WHEN employees%NOTFOUND;
update_sql := 'UPDATE BI_EMPLOYEE SET ';
FOR e_update in e_updates
LOOP
select comments, effective_date , changed_by, changed_on into comments, effective_date , created_by, created_on
from bi_employee_update where EMPLOYEE_UPDATE_ID = e_update.EMPLOYEE_UPDATE_ID;
update_sql := update_sql || e_update.column_name || ' = ''' || e_update.new_value || ''' , ' ;
UPDATE BI_EMPLOYEE_UPDATE
SET
EXECUTED = 'Y'
WHERE
EMPLOYEE_UPDATE_ID = e_update.EMPLOYEE_UPDATE_ID ;
END LOOP;
update_sql := update_sql || ' comments = ''' || comments || ''', updated_by = ''' || created_by || ''', updated_on = ''' || created_on || ''', effective_date = ''' || effective_date || '''';
update_sql := update_sql || ' WHERE emp_id = ' || employee_id ;
dbms_output.put_line('KKKK '||update_sql);
execute immediate update_sql ;
END LOOP;
CLOSE employees;
END;
问题出在第二个游标中，我得到所有先前游标的数据。
The problem is in the second cursor where I get the data of all the previous cursors combined.
例如。如果第一次迭代响应返回a，则第二次应该返回b。但是在实际的第一次迭代中返回a，b，第二次返回a，b。
e.g. if first iteration shoud return a, second should return b. But in actual first iteration returns a, b and second also returns a,b.
下面是生成的动态查询完全相同。
Below is the dynamic query generated which is exactly same.
第一次迭代
预期（正确）：
UPDATE BI_EMPLOYEE SET EMPLOYEE_ID = '1111111111111' , PP_NUMBER = '22222222222' ,
CORPORATE_TITLE_ID = '2' , comments = 'c11', updated_by = '361',
updated_on = '12-SEP-12', effective_date = '25-SEP-12' WHERE emp_id = 18010
$ b b
实际（错误）：
ACTUAL (WRONG):
UPDATE BI_EMPLOYEE SET EMPLOYEE_ID = '1111111111111' , PP_NUMBER = '22222222222' ,
CORPORATE_TITLE_ID = '2' , LASTNAME = 'Ll22 edited ' , OFFSHORE_ONSHORE = '1' ,
ONSHORE_REGION = '1' , ONSHORE_DESK_MANAGER = 'henrry ' ,
comments = 'cc 33 33', updated_by = '361', updated_on = '12-SEP-12',
effective_date = '25-SEP-12' WHERE emp_id = 18010
第二次迭代
预期（正确）：
UPDATE BI_EMPLOYEE SET LASTNAME = 'Ll22 edited ' , OFFSHORE_ONSHORE = '1' ,
ONSHORE_REGION = '1' , ONSHORE_DESK_MANAGER = 'henrry ' ,
comments = 'cc 33 33', updated_by = '361', updated_on = '12-SEP-12',
effective_date = '25-SEP-12' WHERE emp_id = 18009
实际（错误）：
UPDATE BI_EMPLOYEE SET EMPLOYEE_ID = '1111111111111' , PP_NUMBER = '22222222222' ,
CORPORATE_TITLE_ID = '2' , LASTNAME = 'Ll22 edited ' ,
OFFSHORE_ONSHORE = '1' , ONSHORE_REGION = '1' ,
ONSHORE_DESK_MANAGER = 'henrry ' , comments = 'cc 33 33',
updated_by = '361', updated_on = '12-SEP-12',
effective_date = '25-SEP-12'
WHERE emp_id = 18009
为什么会发生这种情况？
Why is this happening?
推荐答案
如您在上一个问题的评论中所述，您的第二个游标不限于第一个游标找到的员工，因为他们之间没有链接。您的位置：
As mentioned in a comment on your previous question, your second cursor is not restricted to the employee found by the first cursor because you have no link between them. Where you have:
and employee_id = employee_id
...这两个引用表列，所以它不作为一个过滤器。你赋予你的局部变量相同的名字，这使得事情困惑，但它仍然超出范围 - 这个游标对程序主体中设置的变量值没有可见性。
... both of those refer to the table column so it doesn't act as a filter at all. You've given your local variable the same name, which confuses things enough, but it's out of scope anyway - this cursor has no visibility of the variable value set in the main body of the procedure.
您需要执行以下操作：
CREATE OR REPLACE PROCEDURE sp_run_employee_updates (p_date IN DATE) IS
update_sql varchar2(4000);
first_update boolean;
CURSOR c_employees IS
SELECT DISTINCT employee_id
FROM bi_employee_update
WHERE effective_date = p_date
AND executed = 'N'
AND activity_id = '0';
CURSOR c_updates(cp_employee_id bi_employee_update.employee_id%TYPE) IS
SELECT *
FROM bi_employee_update
WHERE effective_date = p_date
AND executed = 'N'
AND activity_id = '0'
AND employee_id = cp_employee_id
FOR UPDATE;
BEGIN
-- loop around all employees with pending records
FOR r_employee IN c_employees LOOP
-- reset the update_sql variable to its base
update_sql := 'UPDATE BI_EMPLOYEE SET ';
-- reset the flag so we only add the comments etc. on the first record
first_update := true;
-- loop around all pending records for this employee
FOR r_update IN c_updates(r_employee.employee_id) LOOP
-- add the comments etc., only for the first update we see
if first_update then
update_sql := update_sql
|| ' comments = ''' || r_update.comments || ''','
|| ' updated_by = ''' || r_update.changed_by || ''','
|| ' updated_on = ''' || r_update.changed_on || ''','
|| ' effective_date = ''' || r_update.effective_date || '''';
first_update := false;
end if;
-- add the field/value from this record to the variable
update_sql := update_sql || ', '
|| r_update.column_name || ' = ''' || r_update.new_value || '''' ;
-- mark this update as executed
UPDATE bi_employee_update
SET executed = 'Y'
WHERE CURRENT OF c_updates;
END LOOP;
-- apply this update to the bi_employee record
update_sql := update_sql || ' WHERE emp_id = ' || r_employee.employee_id;
DBMS_OUTPUT.PUT_LINE(update_sql);
EXECUTE IMMEDIATE update_sql;
END LOOP;
END sp_run_employee_updates;
真正的重要区别是，第二个游标现在有一个参数，第一个光标作为参数传递。
The important difference, really, is that the second cursor now has a parameter, and the employee ID from the first cursor is passed as that parameter.
此外， IN_DATE 被声明为日期， t需要通过 TO_DATE（）。在其他地方会有隐式的日期转换（生效日期等），因为你将它们视为字符串，但只要他们没有时间组件，这可能不会破坏任何东西，因为它应该是一致的程序。
Also, IN_DATE is declared as a date, so you don't need to pass it through TO_DATE(). There are going to be implicit date conversions in other places (effective dates etc.) because you're treating them as strings, but as long as they don't have time components this probably won't break anything as it should be consistent within the procedure.
这篇关于使用oracle嵌套游标的奇怪行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
