oracle PL/SQL 中的 DDL 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11272904/
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
DDL statements in PL/SQL?
提问by Aspirant
I am trying the code below to create a table in PL/SQL:
我正在尝试使用下面的代码在PL/SQL 中创建一个表：
DECLARE
V_NAME VARCHAR2(20);
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE TEMP(NAME VARCHAR(20))';
EXECUTE IMMEDIATE 'INSERT INTO TEMP VALUES(''XYZ'')';
SELECT NAME INTO V_NAME FROM TEMP;
END;
/
The SELECTstatement fails with this error:
该SELECT语句失败并出现以下错误：
PL/SQL: ORA-00942: table or view does not exist
Is it possible to CREATE, INSERT and SELECTall in a single PL/SQL Block one after other?
是否有可能CREATE, INSERT and SELECT一个接一个地在一个 PL/SQL 块中？
回答by Ben
I assume you're doing something like the following:
我假设您正在执行以下操作：
declare
v_temp varchar2(20);
begin
execute immediate 'create table temp(name varchar(20))';
execute immediate 'insert into temp values(''XYZ'')';
select name into v_name from temp;
end;
At compile time the table, TEMP, does not exist. It hasn't been created yet. As it doesn't exist you can't select from it; you therefore also have to do the SELECT dynamically. There isn't actually any need to do a SELECT in this particularsituation though you can use the returning intosyntax.
在编译时表TEMP，不存在。它尚未创建。因为它不存在你不能从中选择；因此，您还必须动态执行 SELECT。尽管您可以使用语法，但在这种特定情况下实际上不需要执行 SELECT returning into。
declare
v_temp varchar2(20)
begin
execute immediate 'create table temp(name varchar2(20))';
execute immediate 'insert into temp
values(''XYZ'')
returning name into :1'
returning into v_temp;
end;
However, needing to dynamically create tables is normally an indication of a badly designed schema. It shouldn't really be necessary.
但是，需要动态创建表通常表明架构设计不当。这真的不应该是必要的。
I can recommend René Nyffenegger'spost "Why is dynamic SQL bad?"for reasons why you should avoid dynamic SQL, if at all possible, from a performance standpoint. Please also be aware that you are much more open to SQL injectionand should use bind variables and DBMS_ASSERTto help guard against it.
我可以推荐René Nyffenegger 的文章“为什么动态 SQL 不好？” 从性能的角度来看，您应该尽可能避免使用动态 SQL 的原因。另请注意，您对SQL 注入更加开放，应该使用绑定变量并DBMS_ASSERT帮助防范它。
回答by Gourabp
If you run the program multiple time you will get an error even after modifying the program to run the select statement as dynamic SQL or using a returning into clause. Because when you run the program first time it will create the table without any issue but when you run it next time as the table already created first time and you don't have a drop statement it will cause an error: "Table already exists in the Database". So my suggestion is before creating a table in a pl/sql program always check if there is any table with the same name already exists in the database or not. You can do this check using a Data dictionary views /system tables which store the metadata depending on your database type.
如果多次运行该程序，即使在修改程序以将 select 语句作为动态 SQL 运行或使用返回 into 子句后，您仍将收到错误消息。因为当您第一次运行该程序时，它会创建没有任何问题的表，但是当您下次运行它时，因为该表已经第一次创建并且您没有 drop 语句，它将导致错误：“表已存在于数据库”。所以我的建议是在 pl/sql 程序中创建表之前，总是检查数据库中是否已经存在任何同名的表。您可以使用数据字典视图/系统表来执行此检查，这些表根据您的数据库类型存储元数据。
For Example in Oracle you can use following views to decide if a tables needs to be created or not:
例如在 Oracle 中，您可以使用以下视图来决定是否需要创建表：
DBA_TABLES , ALL_TABLES, USER_TABLES
DBA_TABLES、ALL_TABLES、USER_TABLES
