在应该跨分区运行的此Oracle SQL Update查询中，我在做什么错?

本文介绍了在应该跨分区运行的此Oracle SQL Update查询中，我在做什么错?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

这是我要运行的查询(由同事提供)

Here's the query I'm trying to run(supplied by a colleague)

FOR partition IN 1..32 LOOP lQuery := 'UPDATE CORE.tbl PARTITION(tbl' || LPAD(partition, 2, '0') '|| ') SET p1 = NULL ' || 'WHERE p1 IS NOT NULL'; EXECUTE IMMEDIATE lQuery; END LOOP;

其中，CORE是数据库，而tbl是表的名称.但是，当我尝试运行脚本时，Sql开发人员会抛出很多错误.

where CORE is the db and tbl is the name of the table. However, Sql developer throws a bunch of errors when I try to run the script.

Error starting at line 1 in command: FOR partition IN 1..32 LOOP Error report: Unknown Command Error starting at line 2 in command: lQuery := 'UPDATE CORE.user_login PARTITION(user_login' || LPAD(partition, 2, '0') '|| ') SET password_md5 = NULL ' Error report: Unknown Command Error starting at line 3 in command: || 'WHERE password_md5 IS NOT NULL' Error report: Unknown Command Error starting at line 5 in command: EXECUTE IMMEDIATE lQuery Error report: ORA-06550: line 1, column 17: PLS-00103: Encountered the symbol "LQUERY" when expecting one of the following: := . ( @ % ; The symbol ":=" was substituted for "LQUERY" to continue. 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action: Error starting at line 6 in command: END LOOP Error report: Unknown Command

除SQL中的简单脚本外，我从未使用过其他任何东西，而且似乎无法弄清查询中断的原因.解决问题的任何帮助将不胜感激

I have never worked with anything more than simple scripts in SQL and can't seem to figure out why the query is breaking. Any help on solving the issue would be appreciated

推荐答案

我认为根本的问题是，您需要将该代码包装在BEGIN..END中，以使其成为单个有效命令.

I think the fundamental problem is that you need to wrap that code in BEGIN..END for it to be a single valid command.

正如另一个答案中指出的那样，您在代码中也存在语法错误.

As pointed out in another answer, you also have a syntax error within the code.

DECLARE lQuery VARCHAR2(1000); BEGIN FOR partition IN 1..32 LOOP lQuery := 'UPDATE CORE.tbl PARTITION(tbl' || LPAD(partition, 2, '0') || ') SET p1 = NULL ' || 'WHERE p1 IS NOT NULL'; EXECUTE IMMEDIATE lQuery; END LOOP; END;