在 pl/sql 中的同一过程中创建表和插入
我正在尝试创建一个表，然后在 pl/sql 的同一过程中在其中插入一些值.我尝试运行以下查询但没有成功:
I'm trying to create a table and then insert some values in it within the same procedure in pl/sql. I tried to run the following query without success:
create or replace Procedure insertval8(id_no in number,e_name in char)
is
begin
execute immediate 'create table edu2(id number(20), name char(12))';
insert into edu2 values(&id_no,&e_name);
end;
显示
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1 PL/SQL: SQL Statement ignored
5/13 PL/SQL: ORA-00942: table or view does not exists
错误一直存在，直到我删除插入代码.
The error persists until I remove the insert code.
推荐答案
该过程无法编译，因为在编译时该表不存在.
The procedure cannot be compiled because the table is not present at compile time.
将插入也包裹在立即执行中，或者使用全局临时表(通常是临时数据的首选解决方案).
Wrap the insert in execute immediate also, or use a global temporary table (generaly the preferred solution for temporary data).
create or replace procedure insertval8 (id in number,
name in char )
is
begin
execute immediate 'create table edu2(id number(20), name char(12))';
execute immediate 'insert into edu2(id, name) values (:1, :2)'
using id, name;
end;
相关文章