在执行即时更新查询时需要帮助 [英] Need help in execute immediate update query

问题描述

我有这个查询，它没有更新到数据库中.给定的"where"子句有效.当我独立运行查询时，它可以正常运行，但是在此过程中不起作用.没有例外或没有错误.你们可以帮我弄清楚问题出在哪里吗?

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?

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.

推荐答案

我认为col_id是主键.所以在更新语句中

I suppose that col_id is the primary key. So in the update statement

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

永远不是真的(1不是> 1)

is never true ( 1 is not > 1 )

因此，如果您的过程中没有任何其他commit语句，则永远不会提交这些更新.

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?

这篇关于在执行即时更新查询时需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！