动态表创建中的问题 [英] Issue in dynamic table creation

问题描述

我正在尝试根据以下信息动态创建表..它不能正常工作，如您在下面的输出中看到的那样，我无法消除多余的，"，甚至无法关闭括号..请指教.

i am trying to create the tables dynamically based on the information below..it is not working properly as you can see in the output below i am not able to get rid of extra ',' at the end and not even close the parenthesis.. please advise.

drop table t1;
/
create table t1 (table_name varchar2(10),COLUMN_NAME varchar2(10),DATATYPE varchar2(10),COLUMN_WIDTH NUMBER,is_null varchar2(1));
/
insert into t1 values ('TEST','FNAME','VARCHAR2',10,'Y');
insert into t1 values ('TEST','LNAME','VARCHAR2',10,'N');
commit;

代码

create or replace PROCEDURE P1(
    P_TABLE_NM IN VARCHAR2 )
IS
  LSQL VARCHAR2(1000);
  LSQL2 VARCHAR2(100);
  CURSOR C1
  IS
    SELECT * FROM T1 WHERE TABLE_NAME = P_TABLE_NM ;
    RC1 C1%ROWTYPE;
BEGIN
  lsql := 'create table '||P_TABLE_NM||'(';
  OPEN C1;
  LOOP
   FETCH C1 INTO RC1;
   EXIT WHEN C1%NOTFOUND;
    LSQL := lsql||RC1.COLUMN_NAME||' '||RC1.DATATYPE||'('||RC1.COLUMN_WIDTH||') ';
     BEGIN
       IF (RC1.IS_NULL='Y') THEN
          BEGIN
            lsql := lsql || 'NOT NULL';
          END;
        END IF;
         lsql := lsql || ',' || CHR(10);      
     END;
     END LOOP;
      DBMS_OUTPUT.PUT_LINE(LSQL);
  CLOSE C1;
END;

输出

create table TEST(FNAME VARCHAR2(10) NOT NULL,LNAME VARCHAR2(10) ,

推荐答案

最简单的解决方法是在末尾去除逗号:

The simplest fix would be to strip the trailing comma at the end:

dbms_output.put_line(rtrim(lsql,',') || ' )');

似乎该过程可以简化为:

It seems like the procedure could be simplified to:

create or replace procedure p1(p_table_nm in varchar2)
is
    l_sql long := 'create table ' || p_table_nm || chr(10);
    l_colsep varchar2(2) := '( ';  -- changes to comma after first item
begin
    for rc1 in (
        select * from t1
        where  table_name = p_table_nm
    )
    loop
        l_sql := l_sql || l_colsep || rc1.column_name || ' ' || rc1.datatype ||
            case
                when rc1.column_width is not null then '(' || rc1.column_width || ')'
            end;

        if rc1.is_null = 'N' then  -- Changed from 'Y' - check requirement
            l_sql := l_sql || ' NOT NULL';
        end if;

        l_sql := l_sql || chr(10);
        l_colsep := ', ';
    end loop;

    dbms_output.put_line(rtrim(l_sql,chr(10)) || ' )');
end;

这给出了这样的输出:

create table TEST
( FNAME VARCHAR2(10) 
, LNAME VARCHAR2(10) NOT NULL
, STARTDATE DATE NOT NULL )

您是否打算处理默认值，生成的标识符，虚拟列或任何指定的列顺序?

Are you planning to handle default values, generated identifiers, virtual columns, or any specified column ordering?

您还确定rc1.is_null = 'Y'表示该列是强制性的吗?看起来相反.

Also are you sure that rc1.is_null = 'Y' means the column is mandatory? It looks like the opposite.

这篇关于动态表创建中的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！