谢谢大家,我们可以在以下的帮助下动态创建一个表execute immediate
询问。但是,当我们创建一个表时,它已经创建了,但是如果我想动态创建具有动态列数的表,那么就会出现问题。实际上我已经创建了一个表,但是当我没有随表创建任何列时,会出现很多错误。以下是我在Oracle中编写的一个程序的代码。
declare
no_of_cols number:=&no_of_cols;
colname varchar2(20);
coldata varchar2(20);
i number;
begin
execute immediate 'create table smap1(nam varchar2(10))';
age:='age';
datf:='number'
if(no_of_cols>=2) then
for i in 2..no_of_cols loop
colname:=age;
coldata:=datf;
execute immediate 'alter table smapl add '||colname||' '||coldata;
end loop;
end if;
end;
那么如果 no_of_cols 为 5,则此代码将使用具有相同类型的四列执行。然后我修改了代码并运行 plsql 程序。程序如下
declare
no_of_cols number:=&no_of_cols;
colname varchar2(20);
age varchar2(20);
datf varchar2(20);
coldata varchar2(20);
i number;
begin
execute immediate 'create table smap1(nam varchar2(10))';
if(no_of_cols>=2) then
for i in 2..no_of_cols loop
age :=&age;
datf:=&datf;
colname:=age;
coldata:=datf;
execute immediate 'alter table smapl add '||colname||' '||coldata;
end loop;
end if;
end;
以下是创建上述过程时产生的错误
[Error] Execution (13: 19): ORA-06550: line 13, column 19:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
continue avg count current exists max min prior sql stddev
sum variance execute forall merge time timestamp interval
date <a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string literal with character set specification>
<an alternatively
我对上面的plsql做了一些修改,那么plsql代码如下
declare
no_of_cols number:=&no_of_cols;
colname varchar2(20):='&colname';
coldata varchar2(20):='&coldata';
i number;
begin
execute immediate 'create table smap1(nam varchar2(10))';
if(no_of_cols>=2) then
for i in 2..no_of_cols loop
execute immediate 'alter table smapl add '||colname||' '||coldata;
end loop;
end if;
end;
然后执行后我收到以下错误,并且它不会动态读取列名称
[Error] Execution (1: 1): ORA-02263: need to specify the datatype for this column
ORA-06512: at line 10