PL/SQL中的DDL语句? [英] DDL statements in PL/SQL?

问题描述

我正在尝试下面的代码在 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 and 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注入，并且应使用绑定变量和 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.

这篇关于PL/SQL中的DDL语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！