oracle 需要帮助执行立即更新查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5023642/
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
Need help in execute immediate update query
提问by x.509
I have this query and it's not updating into the database. The given "where" clause is valid. When I run the query independently, it works fine but in this Procedure it's not working. There is no exception or no error. Could you guys help me in figuring out where the problem is?
我有这个查询，它没有更新到数据库中。给定的“where”子句是有效的。当我独立运行查询时，它工作正常，但在此过程中它不起作用。没有异常或没有错误。你们能帮我找出问题所在吗？
EXECUTE IMMEDIATE 'UPDATE ' || dest || ' SET COUNTRY_CODE = :v1 WHERE col_id = :v2'
USING l_vc_CountryCode, l_vc_ColId;
if SQL%ROWCOUNT > 1 THEN
inserts := inserts + 1;
counter := counter + 1;
IF counter > 500 THEN
counter := 0;
COMMIT;
END IF;
END IF;
I didn't write the commit code before. Just to clarity.
我之前没有写提交代码。只是为了清晰。
采纳答案by Marcin Wroblewski
I suppose that col_id is the primary key. So in the update statement
我想 col_id 是主键。所以在更新语句中
EXECUTE IMMEDIATE 'UPDATE ' || dest || ' SET COUNTRY_CODE = :v1 WHERE col_id = :v2'
USING l_vc_CountryCode, l_vc_ColId;
you are always updating at most one row and thus the condition
你总是最多更新一行，因此条件
SQL%ROWCOUNT > 1
is never true ( 1 is not > 1 )
永远不会是真的（ 1 不是 > 1 ）
So if you don't have any other commit statement in your procedure, you will never commit those updates.
因此，如果您的过程中没有任何其他提交语句，您将永远不会提交这些更新。
By the way: what is the purpose of this
顺便说一句：这样做的目的是什么
if SQL%ROWCOUNT > 1 THEN
inserts := inserts + 1;
counter := counter + 1;
IF counter > 500 THEN
counter := 0;
COMMIT;
END IF;
END IF;
why don't you just commit at the end of your work?
你为什么不在工作结束时做出承诺？
回答by tbone
you may want to reconsider your design if your using dynamic sql to change the "dest" table in thousands of updates.
如果您使用动态 sql 在数千次更新中更改“dest”表，您可能需要重新考虑您的设计。
Much better to know your dest and use bind variables for the where conditions. then you can commit every x rows using mod or similar:
更好地了解您的目标并为 where 条件使用绑定变量。然后您可以使用 mod 或类似方法提交每 x 行：
if (mod(v_ctr, 1000) = 0) then
commit;
end if;
But for your example, Marcin is correct, if you are updating only 1 row at a time, then
但是对于您的示例，Marcin 是正确的，如果您一次只更新 1 行，那么
if SQL%ROWCOUNT > 1
will never be true;
永远不会是真的；
EDIT: A simple example knowing your "dest" table:
编辑：一个简单的例子，知道你的“dest”表：
declare
cursor sel_cur is
select col1, col2, from sourceTable where col3 = 'X';
v_ctr pls_integer := 0;
begin
for rec in sel_cur
loop
v_ctr := v_ctr + 1;
-- implicit bind variables used
update destTable
set col1 = rec.col1,
col2 = rec.col2
where col3 = 'Z';
if (mod(v_ctr, 1000) = 0) then
commit;
end if;
end loop;
exception
when others then rollback;
raise;
end;
If using dynamic SQL, a simple example using explicit bind variables (USING clause) from Oracle docs:
如果使用动态 SQL，使用 Oracle 文档中的显式绑定变量（USING 子句）的简单示例：
CREATE OR REPLACE PROCEDURE raise_emp_salary (column_value NUMBER,
emp_column VARCHAR2, amount NUMBER) IS
v_column VARCHAR2(30);
sql_stmt VARCHAR2(200);
BEGIN
-- determine if a valid column name has been given as input
SELECT COLUMN_NAME INTO v_column FROM USER_TAB_COLS
WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = emp_column;
sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE '
|| v_column || ' = :2';
EXECUTE IMMEDIATE sql_stmt USING amount, column_value;
IF SQL%ROWCOUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE('Salaries have been updated for: ' || emp_column
|| ' = ' || column_value);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('Invalid Column: ' || emp_column);
END raise_emp_salary;
/
For more reading, see here.
如需更多阅读，请参见此处。
Hope this helps, happy coding
希望这有帮助，快乐编码
回答by Gary Myers
The following code works okay (ie updates the row). I suspect your error is elsewhere.
下面的代码工作正常（即更新行）。我怀疑你的错误在别处。
For example, if you don't initialise COUNTER, the increment will still leave it as null and it will never commit.
例如，如果您不初始化 COUNTER，增量仍会将其保留为 null，并且永远不会提交。
Or, l_vc_ColId may be the wrong datatype and suffering from an invalid conversion.
或者，l_vc_ColId 可能是错误的数据类型并遭受无效转换。
declare
v_emp_id number := 7839;
v_name varchar2(4) := 'DING';
v_tab varchar2(3) := 'EMP';
begin
execute immediate 'update '||v_tab||
' set ename = :v_name Where empno = :v_emp_id'
using v_name, v_emp_id;
dbms_output.put_line('C:'||sql%rowcount);
end;
回答by Alain Pannetier
Execute immediate needs explicit commit. I guess you checked that ?
立即执行需要显式提交。我猜你检查过了？
