如何在没有任何视图或任何其他表类型的情况下使用动态列名和动态数据类型在 oracle 中创建动态表

how to create dynamic table in oracle with dynamic column name and dynamic data type with out any views or any other table type

提问by Smart003

Thanks for all,We can create a table dynamically with the help of execute immediatequery. But when we create a table it is been created but if i wanted to create table dynamically with dynamic no of columns then question was raised. Actually I had created a table but when I created no of columns along with the table then a lots of errors raised. The following is the code which I had written in the Oracle in a procedure.

谢谢大家，我们可以在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;

then this code executing with four columns with same type if no_of_cols is 5.Then i had modified the code and run the plsql program.the program is as follows

如果 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;

The following are the errors which are generated when the above procedure is created

以下是创建上述过程时产生的错误

  [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

i had done some modifications for the above plsql,then the plsql code will as follows

我对上面的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;

then after executing i am getting the following error and it doenot read column name dynamically

然后在执行后我收到以下错误并且它不会动态读取列名

[Error] Execution (1: 1): ORA-02263: need to specify the datatype for this column
 ORA-06512: at line 10

回答by sampathsris

You cannot use semicolons in EXECUTE IMMEDIATEfor single statements

不能在EXECUTE IMMEDIATE单个语句中使用分号

Here's a quote from the documentation:

这是文档中的引用：

Except for multi-row queries, the dynamic string can contain any SQL statement (without the final semicolon) or any PL/SQL block (with the final semicolon).

除了多行查询，动态字符串可以包含任何 SQL 语句（没有最后的分号）或任何 PL/SQL 块（有最后的分号）。

Remove the semicolon from EXECUTE IMMEDIATE.

去掉分号EXECUTE IMMEDIATE。

execute immediate 'create table smap1(nam varchar2(10));'; -- this is your code
execute immediate 'create table smap1(nam varchar2(10))';  -- correct code, no semicolon at end

But there's another problem.

但还有一个问题。

You need to understand how substitution variables (&variable) works

您需要了解替换变量 ( &variable) 的工作原理

SQL*Plus will prompt for substituion variables only once: just before the script compiles, before running it. And then the variables are replaced in the script verbatim, after which it will get compiled and executed.

SQL*Plus 只会提示输入替换变量一次：就在脚本编译之前，在运行它之前。然后在脚本中逐字替换变量，之后它将被编译和执行。

For example, when you run your script, SQL*Plus recognizes that there are two unknown literals (&colnameand &coldata), and will prompt for you. If you supply the values 'age', and 'number' for them, SQL*Plus will rewrite the script like this:

例如，当您运行脚本时，SQL*Plus 会识别出有两个未知文字（&colname和&coldata），并会提示您。如果您为它们提供值 'age' 和 'number'，SQL*Plus 将像这样重写脚本：

declare
    -- omitted to add clarity
begin
    execute immediate 'create table smap1(nam varchar2(10));';
    if(no_of_cols>=2) then
        for i in 2..no_of_cols loop
            colname:=age;
            coldata:=number;
            execute immediate 'alter table smapl add '||colname||' '||coldata;  
        end loop;
    end if;
end;

So if you want to assign a string literal to a variable, and you want to get that string from a substitution variable, you need to do this:

因此，如果要将字符串文字分配给变量，并且想从替换变量中获取该字符串，则需要执行以下操作：

colname varchar2(30) := '&colname'; -- notice the single quotes

Assuming you provided 'age' for colnameSQL*Plus will happily convert this to:

假设您为colnameSQL*Plus提供了 'age'将很乐意将其转换为：

colname varchar2(30) := 'age';

So, placing a substitution variable inside a loop will not make SQL*Plus repeatedly prompt you for it's value.

因此，在循环中放置替换变量不会使 SQL*Plus 反复提示您输入它的值。

回答by Wernfried Domscheit

For better understanding and analyze you should do it like this:

为了更好地理解和分析，您应该这样做：

sqlcmd varhchar(30000);
begin
    sqlcmd := 'create table smap1(nam varchar2(10))';
    DBMS_OUTPUT.PUT_LINE(sqlcmd);
    execute immediate sqlcmd;
    if(no_of_cols>=2) then
        for i in 2..no_of_cols loop
            age :=&age;
            datf:=&datf;
            colname:=age;
            coldata:=datf;
            sqlcmd := 'alter table smapl add '||colname||' '||coldata;  
            DBMS_OUTPUT.PUT_LINE(sqlcmd);
            execute immediate sqlcmd;
        end loop;
    end if;