如何使用动态列名称和动态数据类型在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

问题描述

感谢大家，我们可以在执行立即执行查询的帮助下动态创建表。但是当我们创建一个表时，它被创建，但是如果我想用动态的列数动态地创建表，那么就提出了问题。其实我创建了一个表，但是当我创建了没有列的列与表然后提出了很多错误。以下是我在一个过程中在Oracle中编写的代码。

 声明
 no_of_cols number：=& ; no_of_cols; 
 colname varchar2（20）; 
 coldata varchar2（20）; 
 i number; 
 begin 
执行立即'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; 
执行立即'alter table smapl add'|| colname ||''|| coldata; 
 end loop; 
 end if; 
结束
  

然后，如果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 
执行立即'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; 
执行立即'alter table smapl add'|| colname ||''|| coldata; 
 end loop; 
 end if; 
结束
  

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

  [错误]执行（13：19）：ORA-06550：第13行，第19列：
 PLS-00103：遇到符号; 当期待以下之一时：
 
（ -  + case mod new not null< an identifier> 
< a双引号delimited-identifier><一个绑定变量> 
 continue avg count当前存在max min先前sql stddev 
总和方差执行forall合并时间时间戳间隔
日期<具有字符集规范的字符串文字> 
< a number><单引号SQL字符串> pipe 
<带有字符集规范的可选引号字符串文字> 
< a替代
  

我已经对上述的plsql做了一些修改，那么plsql代码将如下所示

  declare 
 no_of_cols number：=& no_of_cols; 
 colname varchar2（20）：='& colname'; 
 coldata varchar2（20）：='& coldata' 
i number; 
 begin 
执行立即'create table smap1（nam varchar2（10））'; 
 if（no_of_cols> = 2）then 
 
因为我在2..no _of_cols loop 
执行立即'alter table smapl add'|| colname ||''|| coldata; 
 end loop; 
 end if; 
结束
  

然后执行后，我收到以下错误，并动态读取列名称$ / b
$ b

  [错误]执行（1：1）：ORA-02263：需要指定此列的数据类型
 ORA-06512：at第10行
  

解决方案

您不能在<$ c $中使用分号c> EXECUTE IMMEDIATE 用于单个语句

以下是文档：

除了多-row查询，动态字符串可以包含任何SQL语句（ 没有最终分号 ）或任何PL / SQL块（使用最后一个分号）。

从 EXECUTE IMMEDIATE 中删除​​分号。

 执行立即'create table smap1（nam varchar2（10））;'; - 这是你的代码
执行立即'创建表smap1（nam varchar2（10））'; - 正确的代码，结束时没有分号
  

但还有另一个问题。

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

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

例如，当您运行脚本时，SQL * Plus会认识到是两个未知的文字（& colname 和& coldata ），并将提示您。如果您为其提供值'age'和'number'，则SQL * Plus将重写脚本，如下所示：

 声明
  - 省略添加清晰度
 begin 
执行立即'create table smap1（nam varchar2（10））;'; 
 if（no_of_cols> = 2）then 
 for i in 2..no_of_cols loop 
 colname：= age; 
 coldata：= number; 
执行立即'alter table smapl add'|| colname ||''|| coldata; 
 end loop; 
 end if; 
结束
  

所以如果你想为一个变量指定一个字符串字面值，而你想要从一个替换变量，你需要这样做：

  colname varchar2（30）：='& colname'; - 注意单引号
  

假设您为 colname SQL * Plus将很乐意将其转换为：

  colname varchar2（30）：='age' 
  

所以，在循环中放置替换变量的 不会使SQL * Plus反复提示您的价值 。

Thanks for all,We can create a table dynamically with the help of execute immediate query. 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.

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

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

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

解决方案

You cannot use semicolons in EXECUTE IMMEDIATE for single statements

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).

Remove the semicolon from 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

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.

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

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 colname SQL*Plus will happily convert this to:

colname varchar2(30) := 'age';

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

这篇关于如何使用动态列名称和动态数据类型在oracle中创建动态表，并使用任何视图或任何其他表类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！