oracle 如果 else 里面不能有 CREATE TABLE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6272997/
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
Can not have CREATE TABLE inside if else
提问by rsd
When I run this query
当我运行此查询时
DECLARE
num NUMBER;
BEGIN
SELECT COUNT(*) INTO num FROM user_all_tables WHERE TABLE_NAME=upper('DatabaseScriptLog')
;
IF num < 1 THEN
CREATE TABLE DatabaseScriptLog
(ScriptIdentifier VARCHAR(100) NOT NULL,
ScriptType VARCHAR(50),
StartDate TIMESTAMP,
EndDate TIMESTAMP,
PRIMARY KEY (ScriptIdentifier)
);
END IF;
END;
When execute the above, I got the following:
执行上述操作时，我得到以下信息：
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma raise return select update while with << close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error.
PLS-00103：在预期以下情况之一时遇到符号“CREATE”：
begin case 为 goto 声明退出 if loop mod null pragma raise return select update while with << close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe 06550. 00000 - "line %s, column %s:\n %s" *原因：通常是 PL/SQL 编译错误。
回答by Thilo
You cannot run DDL statements like that. You need to use dynamic SQL (EXECUTE IMMEDIATE).
您不能像那样运行 DDL 语句。您需要使用动态 SQL (EXECUTE IMMEDIATE)。
IF num < 1 THEN
EXECUTE IMMEDIATE 'CREATE TABLE DatabaseScriptLog (ScriptIdentifier VARCHAR(100) NOT NULL, ScriptType VARCHAR(50), StartDate TIMESTAMP, EndDate TIMESTAMP, PRIMARY KEY (ScriptIdentifier))'
END IF;
回答by Adam Dymitruk
You cannot do this like you could in SQLServer. You need to execute the create code through a stored procedure that is already in the proper schema. You pass the create code as a parameter and the stored procedure that has the correct privileges does it for you.
您不能像在 SQLServer 中那样执行此操作。您需要通过已在正确架构中的存储过程来执行创建代码。您将创建代码作为参数传递，具有正确权限的存储过程会为您执行此操作。
I use a version script that updates the schema to the latest by running schema altering operations separated by if-then clauses to check what version the db is at. After altering it increments the version so that the next if statements test passes and so on. If you are up to date and run the script the ifs skip all altering code. If your db is at version 46 and you run the script which has all changes up to 50, you execute only the blocks that represent versions 47-50.
我使用一个版本脚本，通过运行由 if-then 子句分隔的模式更改操作来将模式更新为最新版本，以检查数据库的版本。更改后，它会增加版本，以便下一个 if 语句测试通过等等。如果您是最新的并运行脚本，则 ifs 将跳过所有更改代码。如果您的数据库版本为 46，并且您运行的脚本的所有更改最多为 50，则您只执行代表版本 47-50 的块。
You could execute immediate but would need elevated privileges which I would not recommend.
您可以立即执行，但需要提升权限，我不建议这样做。
Hope this helps.
希望这可以帮助。
