在 pl/sql 中的同一过程中创建表和插入 [英] Creation of table and insertion within the same procedure in 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;

这篇关于在 pl/sql 中的同一过程中创建表和插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！