oracle 自动建表

       目前由于做一个项目，由客户自己定义表名、表字段、字段是否允许为空等等，所以写了一下代码：

tab_object表主要用于储存表名、表空间等。

-- Create table
create table TAB_OBJECT
(
  ID            VARCHAR2(40) not null,
  TAB_NAME      VARCHAR2(100) not null,
  TAB_CNAME     VARCHAR2(100),
  TAB_SPACE     VARCHAR2(100) not null,
  CREATED       DATE not null,
  CREATED_USER  VARCHAR2(50) not null,
  LAST_DDL_TIME DATE,
  LAST_DDL_USER VARCHAR2(50),
  STATUS        NUMBER(1)
)
tablespace JZSOFT
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns
comment on column TAB_OBJECT.TAB_NAME
  is '表名';
comment on column TAB_OBJECT.TAB_CNAME
  is '表中文名';
comment on column TAB_OBJECT.TAB_SPACE
  is '表空间';
comment on column TAB_OBJECT.CREATED
  is '创建日期';
comment on column TAB_OBJECT.CREATED_USER
  is '创建人';
comment on column TAB_OBJECT.LAST_DDL_TIME
  is '最后修改日期';
comment on column TAB_OBJECT.LAST_DDL_USER
  is '最后修改人';
comment on column TAB_OBJECT.STATUS
  is '状态';
-- Create/Recreate primary, unique and foreign key constraints
alter table TAB_OBJECT
  add constraint PK_TAB_OBJECT primary key (ID)
  using index
  tablespace JZSOFT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes
create unique index INX_TABNAME on TAB_OBJECT (TAB_NAME)
  tablespace JZSOFT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

 

表tab_column 主要存储表的字段、数据类型、类型长度、是否为空、是否主键等信息。这里并没有对外键进行管理，
-- Create table
create table TAB_COLUMN
(
  TAB_NAME       VARCHAR2(100) not null,
  ID             VARCHAR2(40) not null,
  COLUMN_NAME    VARCHAR2(100) not null,
  COLUMN_CNAME   VARCHAR2(100),
  DATA_TYPE      VARCHAR2(100) not null,
  DATA_LENGTH    NUMBER(10),
  CREATED        DATE,
  ISNULL         NUMBER(1),
  COLUMN_DEFAULT VARCHAR2(500),
  ISPK           NUMBER(1)
)
tablespace JZSOFT
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table TAB_COLUMN
  is '表字段+描述';
-- Add comments to the columns
comment on column TAB_COLUMN.TAB_NAME
  is '表名';
comment on column TAB_COLUMN.COLUMN_NAME
  is '字段名';
comment on column TAB_COLUMN.COLUMN_CNAME
  is '字段中文名';
comment on column TAB_COLUMN.DATA_TYPE
  is '数据类型';
comment on column TAB_COLUMN.DATA_LENGTH
  is '字段长度';
comment on column TAB_COLUMN.CREATED
  is '创建时间';
comment on column TAB_COLUMN.ISNULL
  is '是否为空';
comment on column TAB_COLUMN.COLUMN_DEFAULT
  is '默认值';
comment on column TAB_COLUMN.ISPK
  is '是否为主键';
-- Create/Recreate primary, unique and foreign key constraints
alter table TAB_COLUMN
  add constraint PK_TAB_COLUMN primary key (ID)
  using index
  tablespace JZSOFT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table TAB_COLUMN
  add constraint FK_TABNAME foreign key (TAB_NAME)
  references TAB_OBJECT (ID) on delete cascade;

 

-- Create table

表：CREATETABLE_LOG为日志表、存储建表时的错误信息，信息记录太少，需要加强日志记录。

create table CREATETABLE_LOG
(
  TABLE_NAME VARCHAR2(100),
  C_SQLCODE  VARCHAR2(500),
  BEIZHU     VARCHAR2(1000)
)
tablespace JZSOFT
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

实现储存过程：
create or replace procedure sp_createtable( l_tabname varchar2 )
is
 ls_create varchar2(4000);
 ls_tablespace varchar2( 4000 );
 --ls_alter varchar2(4000);
 ls_type varchar2( 1000 );
 ls_default varchar2( 4000 );
 ls_isnull varchar2( 30 );
 ls_ispk varchar2( 500 );
 ls_comment_column varchar2(4000);
 ls_comment_tab varchar2(500);
 ls_sqlcode varchar2(500);
 ls_errm varchar2(500);
  TYPE red_cfgdb IS RECORD (
  comment_column varchar2(4000),inx_column varchar2(500));

  li_i integer;

   TYPE type_conarray IS TABLE OF red_cfgdb INDEX BY PLS_INTEGER;
   gv_conarray type_conarray;
begin
  ls_create := null ;
  ls_tablespace := null;
  li_i:=1;
  --ls_alter := null;
for a in ( select x.id,x.tab_name,x.tab_cname,x.tab_space,y.column_name,y.column_cname,y.data_type,y.data_length,y.isnull,y.column_default,y.ispk
  from tab_object x,tab_column y where x.id=y.tab_name and upper(x.id) = upper(l_tabname) ) loop
    ls_type :=  case when upper(a.data_type) = upper('date') then a.data_type
                     when upper(a.data_type) <> upper('date') and a.data_length is null then a.data_type
                     when upper(a.data_type) <> upper('date') and a.data_length is not null then a.data_type||'('||a.data_length||')'
                     end ;
    ls_isnull := case when a.isnull = 1 then ' not null ' else null  end;
    ls_ispk := case when a.ispk = 1 And ls_ispk is null then '  constraint PK_'||l_tabname||' primary key ('||a.column_name
                    when a.ispk = 1 and ls_ispk is not null then ls_ispk||', '||a.column_name
                    else ls_ispk
                    end ;
    ls_tablespace := case when ls_tablespace is  null then 'tablespace '|| a.tab_space || ' pctfree 10  pctused 40  initrans 1
                  maxtrans 255 storage  (  initial 64K  minextents 1   maxextents unlimited )'  end ;

    ls_default := case when a.column_default is not null then   ' default '||a.column_default end ;
    if ls_create is null then
      ls_create := ' create table '||a.tab_name||' ( '
                ||chr(10)||a.column_name||chr( 32 ) ||ls_type|| chr(32)||ls_default||chr( 32 )||ls_isnull ;
    else
      ls_create := ls_create||chr(10)||' , '||a.column_name||chr( 32 ) ||ls_type|| chr(32)||ls_default||chr( 32 )||ls_isnull ;
    end if;
    ls_comment_tab := case when a.tab_cname is not null and ls_comment_tab is null then ' comment on table '||l_tabname ||' is ' ||chr(39)||a.tab_cname||chr(39) else ls_comment_tab end ;

   gv_conarray(li_i).comment_column :=  ' comment on column '||l_tabname||'.'||a.column_name||' is  '||chr(39)||a.column_cname ||chr(39)  ;
    li_i:=li_i+1;
end loop;
ls_create :=    ls_create||chr( 32 )
            ||chr(10)||case when ls_ispk is not null then ' , '||ls_ispk||')' end
            ||chr(10)||' ) '
            ||chr(10)||ls_tablespace ;

 

execute immediate   ls_create   ;
   insert into createtable_log(table_name,c_sqlcode,beizhu) values( l_tabname,'','建表成功!' );
execute immediate ls_comment_tab ;

li_i := gv_conarray.first;
             WHILE li_i is NOT NULL LOOP
                    execute immediate gv_conarray(li_i).comment_column;
                   li_i := gv_conarray.next(li_i);
             END LOOP;
exception when others then
  ls_sqlcode := sqlcode;
  ls_errm:=sqlerrm;
  insert into createtable_log(table_name,c_sqlcode,beizhu) values( l_tabname,ls_sqlcode,ls_errm );
   dbms_output.put_line( ls_sqlcode ) ;
  commit;
end sp_createtable;

 

 

ps:在开始调试存储过程的时候，在 execute immediate  时 经常报“ORA-00911 无效字符”错误，无论怎么搞都找不到原因，我把语句copy到plsql中执行通过。   后来找了很久才发现  在execute immediate中 不能执行多行语句和不允许有分号.