PL/SQL 中的 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.
相关文章