目前由于做一个项目,由客户自己定义表名、表字段、字段是否允许为空等等,所以写了一下代码:
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中 不能执行多行语句和不允许有分号.