目前由于做一个项目，由客户自己定义表名、表字段、字段是否允许为空等等，所以写了一下代码：
tab_object表主要用于储存表名、表空间等。
-- Create tablecreate 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 tablecreate 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中 不能执行多行语句和不允许有分号.
