PL/SQL 中的 DDL 语句?

2021-12-24 00:00:00 oracle plsql ddl

我正在尝试使用下面的代码在 PL/SQL 中创建一个表:

I am trying the code below to create a table in 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;
/

SELECT 语句失败并显示以下错误:

The SELECT statement fails with this error:

PL/SQL: ORA-00942: table or view does not exist

是否可以在一个 PL/SQL 块中一个接一个地CREATE、INSERT 和 SELECT?

Is it possible to CREATE, INSERT and SELECT all in a single PL/SQL Block one after other?

推荐答案

我假设您正在执行以下操作:

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;

在编译时表，TEMP，不存在.它尚未创建.因为它不存在你不能从中选择；因此，您还必须动态执行 SELECT.尽管您可以使用 返回语法.

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 particular situation though you can use the returning into syntax.

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.

我可以推荐René Nyffenegger 的帖子"为什么动态 SQL 不好?" 从性能的角度来看，您应该尽可能避免使用动态 SQL 的原因.另请注意，您对 SQL 更加开放注入并且应该使用绑定变量和DBMS_ASSERT 来帮助防范它.

I can recommend René Nyffenegger's post "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 injection and should use bind variables and DBMS_ASSERT to help guard against it.

相关文章