ora-04091表格正在变异- [英] ora-04091 table is Mutating-

问题描述

我正在使用一个函数，该函数比较表1和表2中的所有列，并返回"Y"或"N".基于此，我将更新表1.

I'm using a function which compares all the columns in Table 1 and Table 2 and returns 'Y' or 'N'. Based on that, I will update my Table 1.

但是当我运行merge语句时，它会显示错误:

But when I run the merge statement it displays an error:

ora-04091-表1正在变异，触发器/功能可能看不到

ora-04091 - Table1 is mutating, trigger/function may not see it

我该如何解决?

CREATE OR REPLACE function  DataChange  (in_epmname varchar2) return char  is
    v_epmname   table2.empname%type;
    v_DATA_COUNT  varchar2(2);
    v_DATA_CHANGED  char;
begin
    SELECT COUNT (*) into v_DATA_COUNT
    FROM
    (
        SELECT trim(column1||column2||column3)
        FROM table1
        WHERE empname  =  in_epmname
        UNION
        SELECT trim(column1||column2||column3)
        FROM table2
        WHERE empname = in_epmname
    );
    If (v_DATA_COUNT = '1' ) Then
        v_DATA_CHANGED :='N';
    else
        v_DATA_CHANGED :='Y';
    end if;
    return v_DATA_CHANGED;
end DataChange  ;

我正在使用的merge语句是:

The merge statement that I'm using is:

CREATE OR REPLACE PROCEDURE updatetabble1 AS
BEGIN
    MERGE
        INTO Table1 DBC
        USING  ( SELECT
            empname,
            DataChange(empname) as DATA_CHANGED
            FROM employee
        ) TBL_MAIN
        ON ( DBC.empname = TBL_MAIN.empname    )
        WHEN MATCHED THEN
          UPDATE SET
          DBC.DATA_CHANGED = TBL_MAIN.DATA_CHANGED;
    COMMIT;     
END updatetabble1;

推荐答案

乍得，我尝试了此选项，但没有引发任何错误.

Chade i tried this Option it didnt throw me any error.

CREATE OR REPLACE PROCEDURE updatetabble1 AS
    BEGIN
        MERGE
        INTO Table1 DBC
        USING  (            Select ename from
            (
            Select ename
            from
                (
                Select ename, column1||Column2||Column3 from table1
                union
                Select ename, column1||Column2||Column3 from table2 
                ) 
            ) 
        GROUP BY ename HAVING count(*) > 1
        ) TBL_MAIN
                 ON ( DBC.empname = TBL_MAIN.empname)
                WHEN MATCHED THEN
 UPDATE SET DBC.DATA_CHANGED = 'Y';
COMMIT;     
END updatetabble1;

这篇关于ora-04091表格正在变异-的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！