获取Oracle10g表元数据 2
来源:互联网 发布:淘宝一键复制有违规吗 编辑:程序博客网 时间:2024/04/30 15:08
最近,在写一个代码工具,从数据读取表生成实体Bean、SQLMap,需要获取到数据库的元信息,为了适应不同的数据库,需要针对每一种数据库实现一种获取元信息的方式,这里是Oracle10g元信息的获取方式:
spool get_tab_ddl.log
prompt
prompt Creating function GET_TAB_DDL
prompt =============================
prompt
create or replace function get_tab_ddl(p_tab_name varchar2) return clob is
--返回表结构
v_result clob;
--表所在表空间
v_tablespace_name varchar2(200);
--表模式
v_logging varchar2(100);
--是否分区表
v_partitioned varchar2(100);
--前导空格
v_block varchar2(100) := ' ';
--区域ddl
v_ddl varchar2(32767);
--主键索引
v_pk_index varchar2(200);
begin
--表信息
select t.tablespace_name,
decode(t.logging, 'YES', 'LOGGING', 'NO LOGGING') logging,
t.partitioned
into v_tablespace_name,
v_logging,
v_partitioned
from user_tables t
where t.table_name = upper(p_tab_name);
v_result := '-- Create table' || chr(13) || 'create table ' ||
upper(p_tab_name) ||'(' || chr(13);
--列信息
for col in (select'"' || c.COLUMN_NAME || '"' || ' ' || c.DATA_TYPE ||
decode(c.DATA_TYPE,
'VARCHAR2',
'(' || c.DATA_LENGTH ||') ',
'CHAR',
'(' || c.DATA_LENGTH ||') ',
decode(c.DATA_PRECISION,
null,
' ',
decode(c.DATA_SCALE,
0,
'(' || c.DATA_PRECISION ||') ',
'(' || c.DATA_PRECISION ||',' ||
c.DATA_SCALE ||') '))) ||
decode(c.NULLABLE, 'Y', 'NULL ', 'NOT NULL ') tab_col,
c.data_default
from user_tab_columns c
where c.table_name =upper(p_tab_name)
order by c.column_id)
loop
if col.data_default is null
then
v_result := v_result || v_block || col.tab_col || ',' || chr(13);
else
v_result := v_result || v_block || col.tab_col || 'DEFAULT ' ||
rtrim(col.data_default, chr(10)) ||',' || chr(13);
end if;
end loop;
v_result := rtrim(rtrim(v_result, chr(13)),',') || chr(13) || ') ' ||
v_logging || chr(13) || 'tablespace ' || v_tablespace_name || ';' ||
chr(13);
-- Add comments to the table
select decode(m.comments,
null,
v_result,
v_result || '-- Add comments to the table' || chr(13) ||
'comment on table ' || m.table_name ||' is ''' || m.comments ||
''';' || chr(13))
into v_result
from user_tab_comments m
where m.table_name = upper(p_tab_name);
-- Add comments to the columns
v_ddl := '';
for com in (select'comment on column ' || c.table_name || '.' ||
c.column_name || ' is ''' || c.comments || ''';' ||
chr(13) col_com
from user_col_comments c
where c.table_name =upper(p_tab_name)
and c.commentsis not null)
loop
v_ddl := v_ddl || com.col_com;
end loop;
if v_ddl isnot null
then
v_result := v_result || '-- Add comments to the columns ' || chr(13) ||
v_ddl;
end if;
-- Create/Recreate primary, unique and foreign key constraints
v_ddl := '';
v_pk_index := '';
for con in (select c.constraint_name,
c.constraint_type,
c.search_condition,
c.r_constraint_name,
c.index_name,
decode(c.delete_rule, 'CASCADE', ' on delete cascade', '') delete_rule,
i.tablespace_name
from user_constraints c
leftjoin user_indexes i on (c.index_name = i.index_name)
where c.table_name =upper(p_tab_name)
order by c.constraint_type)
loop
--pk
if con.constraint_type = 'P'
then
v_pk_index := con.index_name;
v_ddl := v_ddl || 'alter table ' ||upper(p_tab_name) ||
' add constraint ' || con.constraint_name ||
' primary key(';
for pk in (select c.column_name
from user_cons_columns c
where c.constraint_name = con.constraint_name
orderby c.position)
loop
v_ddl := v_ddl || pk.column_name || ',';
end loop;
v_ddl := rtrim(v_ddl, ',') || ')' || chr(13) || 'using index' ||
chr(13) || 'tablespace ' || con.tablespace_name ||';' ||
chr(13);
end if;
--fk
if con.constraint_type = 'R'
then
v_ddl := v_ddl || 'alter table ' ||upper(p_tab_name) ||
' add constraint ' || con.constraint_name ||
' foreign key(';
for spk in (select c.column_name
from user_cons_columns c
where c.constraint_name = con.constraint_name
orderby c.position)
loop
v_ddl := v_ddl || spk.column_name || ',';
end loop;
v_ddl := rtrim(v_ddl, ',') || ')';
select distinct (v_ddl || ' references ' || c.table_name ||'(')
into v_ddl
from user_cons_columns c
where c.constraint_name = con.r_constraint_name;
for tfk in (select c.column_name
from user_cons_columns c
where c.constraint_name = con.r_constraint_name
orderby c.position)
loop
v_ddl := v_ddl || tfk.column_name || ',';
end loop;
v_ddl := rtrim(v_ddl, ',') || ')' || con.delete_rule || ';' ||
chr(13);
end if;
--check
if (con.constraint_type = 'C' and
instr(con.search_condition, 'NOT NULL') = 0)
then
v_ddl := v_ddl || 'alter table ' ||upper(p_tab_name) ||
' add check (' ||rtrim(con.search_condition, chr(10)) || ');' ||
chr(13);
end if;
end loop;
if v_ddl isnull
then
v_result := v_result || chr(13);
else
v_result := v_result ||
'-- Create/Recreate primary, unique and foreign key constraints ' ||
chr(13) || v_ddl;
end if;
-- Create/Recreate indexes
v_ddl := '';
for idx in (select t.index_name,
t.table_name,
decode(t.uniqueness,
'NONUNIQUE',
' ',
' ' || t.uniqueness ||' ') uniqueness,
t.tablespace_name
from user_indexes t
where t.table_name =upper(p_tab_name)
and t.index_type <>'LOB'
and t.index_name <> v_pk_index
and instr(t.index_name,'SYS_C00') <> 1)
loop
v_ddl := v_ddl || 'create' || idx.uniqueness ||'index ' ||
idx.index_name || ' on ' || idx.table_name ||'(';
for i_col in (select c.column_name || ' ' || c.descend column_name
from user_ind_columns c
where c.index_name = idx.index_name
orderby c.column_position)
loop
v_ddl := v_ddl || i_col.column_name || ',';
end loop;
v_ddl := rtrim(v_ddl, ',') || ')' || ' tablespace ' ||
idx.tablespace_name || ';' || chr(13);
end loop;
if v_ddl isnull
then
v_result := v_result || chr(13);
else
v_result := v_result || '-- Create/Recreate indexes' || chr(13) ||
v_ddl;
end if;
return(v_result);
end get_tab_ddl;
/
spool off
prompt
prompt Creating function GET_TAB_DDL
prompt =============================
prompt
create or replace function get_tab_ddl(p_tab_name varchar2) return clob is
--返回表结构
v_result clob;
--表所在表空间
v_tablespace_name varchar2(200);
--表模式
v_logging varchar2(100);
--是否分区表
v_partitioned varchar2(100);
--前导空格
v_block varchar2(100) := ' ';
--区域ddl
v_ddl varchar2(32767);
--主键索引
v_pk_index varchar2(200);
begin
--表信息
select t.tablespace_name,
decode(t.logging, 'YES', 'LOGGING', 'NO LOGGING') logging,
t.partitioned
into v_tablespace_name,
v_logging,
v_partitioned
from user_tables t
where t.table_name = upper(p_tab_name);
v_result := '-- Create table' || chr(13) || 'create table ' ||
upper(p_tab_name) ||'(' || chr(13);
--列信息
for col in (select'"' || c.COLUMN_NAME || '"' || ' ' || c.DATA_TYPE ||
decode(c.DATA_TYPE,
'VARCHAR2',
'(' || c.DATA_LENGTH ||') ',
'CHAR',
'(' || c.DATA_LENGTH ||') ',
decode(c.DATA_PRECISION,
null,
' ',
decode(c.DATA_SCALE,
0,
'(' || c.DATA_PRECISION ||') ',
'(' || c.DATA_PRECISION ||',' ||
c.DATA_SCALE ||') '))) ||
decode(c.NULLABLE, 'Y', 'NULL ', 'NOT NULL ') tab_col,
c.data_default
from user_tab_columns c
where c.table_name =upper(p_tab_name)
order by c.column_id)
loop
if col.data_default is null
then
v_result := v_result || v_block || col.tab_col || ',' || chr(13);
else
v_result := v_result || v_block || col.tab_col || 'DEFAULT ' ||
rtrim(col.data_default, chr(10)) ||',' || chr(13);
end if;
end loop;
v_result := rtrim(rtrim(v_result, chr(13)),',') || chr(13) || ') ' ||
v_logging || chr(13) || 'tablespace ' || v_tablespace_name || ';' ||
chr(13);
-- Add comments to the table
select decode(m.comments,
null,
v_result,
v_result || '-- Add comments to the table' || chr(13) ||
'comment on table ' || m.table_name ||' is ''' || m.comments ||
''';' || chr(13))
into v_result
from user_tab_comments m
where m.table_name = upper(p_tab_name);
-- Add comments to the columns
v_ddl := '';
for com in (select'comment on column ' || c.table_name || '.' ||
c.column_name || ' is ''' || c.comments || ''';' ||
chr(13) col_com
from user_col_comments c
where c.table_name =upper(p_tab_name)
and c.commentsis not null)
loop
v_ddl := v_ddl || com.col_com;
end loop;
if v_ddl isnot null
then
v_result := v_result || '-- Add comments to the columns ' || chr(13) ||
v_ddl;
end if;
-- Create/Recreate primary, unique and foreign key constraints
v_ddl := '';
v_pk_index := '';
for con in (select c.constraint_name,
c.constraint_type,
c.search_condition,
c.r_constraint_name,
c.index_name,
decode(c.delete_rule, 'CASCADE', ' on delete cascade', '') delete_rule,
i.tablespace_name
from user_constraints c
leftjoin user_indexes i on (c.index_name = i.index_name)
where c.table_name =upper(p_tab_name)
order by c.constraint_type)
loop
--pk
if con.constraint_type = 'P'
then
v_pk_index := con.index_name;
v_ddl := v_ddl || 'alter table ' ||upper(p_tab_name) ||
' add constraint ' || con.constraint_name ||
' primary key(';
for pk in (select c.column_name
from user_cons_columns c
where c.constraint_name = con.constraint_name
orderby c.position)
loop
v_ddl := v_ddl || pk.column_name || ',';
end loop;
v_ddl := rtrim(v_ddl, ',') || ')' || chr(13) || 'using index' ||
chr(13) || 'tablespace ' || con.tablespace_name ||';' ||
chr(13);
end if;
--fk
if con.constraint_type = 'R'
then
v_ddl := v_ddl || 'alter table ' ||upper(p_tab_name) ||
' add constraint ' || con.constraint_name ||
' foreign key(';
for spk in (select c.column_name
from user_cons_columns c
where c.constraint_name = con.constraint_name
orderby c.position)
loop
v_ddl := v_ddl || spk.column_name || ',';
end loop;
v_ddl := rtrim(v_ddl, ',') || ')';
select distinct (v_ddl || ' references ' || c.table_name ||'(')
into v_ddl
from user_cons_columns c
where c.constraint_name = con.r_constraint_name;
for tfk in (select c.column_name
from user_cons_columns c
where c.constraint_name = con.r_constraint_name
orderby c.position)
loop
v_ddl := v_ddl || tfk.column_name || ',';
end loop;
v_ddl := rtrim(v_ddl, ',') || ')' || con.delete_rule || ';' ||
chr(13);
end if;
--check
if (con.constraint_type = 'C' and
instr(con.search_condition, 'NOT NULL') = 0)
then
v_ddl := v_ddl || 'alter table ' ||upper(p_tab_name) ||
' add check (' ||rtrim(con.search_condition, chr(10)) || ');' ||
chr(13);
end if;
end loop;
if v_ddl isnull
then
v_result := v_result || chr(13);
else
v_result := v_result ||
'-- Create/Recreate primary, unique and foreign key constraints ' ||
chr(13) || v_ddl;
end if;
-- Create/Recreate indexes
v_ddl := '';
for idx in (select t.index_name,
t.table_name,
decode(t.uniqueness,
'NONUNIQUE',
' ',
' ' || t.uniqueness ||' ') uniqueness,
t.tablespace_name
from user_indexes t
where t.table_name =upper(p_tab_name)
and t.index_type <>'LOB'
and t.index_name <> v_pk_index
and instr(t.index_name,'SYS_C00') <> 1)
loop
v_ddl := v_ddl || 'create' || idx.uniqueness ||'index ' ||
idx.index_name || ' on ' || idx.table_name ||'(';
for i_col in (select c.column_name || ' ' || c.descend column_name
from user_ind_columns c
where c.index_name = idx.index_name
orderby c.column_position)
loop
v_ddl := v_ddl || i_col.column_name || ',';
end loop;
v_ddl := rtrim(v_ddl, ',') || ')' || ' tablespace ' ||
idx.tablespace_name || ';' || chr(13);
end loop;
if v_ddl isnull
then
v_result := v_result || chr(13);
else
v_result := v_result || '-- Create/Recreate indexes' || chr(13) ||
v_ddl;
end if;
return(v_result);
end get_tab_ddl;
/
spool off
select s.column_name,
s.data_type column_type,
decode(s.data_type,
'VARCHAR2',
s.data_length,
'CHAR',
s.data_length,
s.data_precision) column_length,
c.comments column_comments,
decode(s.nullable, 'Y', 'N', 'N', 'Y') isnotnull,
decode(l.column_name, null, 'N', 'Y') ispk
from user_tab_columns s
left join user_col_comments c
on (s.column_name = c.column_name and s.table_name = c.table_name)
left join user_constraints t
on (s.table_name = t.table_name and t.constraint_type = 'P')
left join user_cons_columns l
on (s.table_name = l.table_name and
t.constraint_name = l.constraint_name and
s.column_name = l.column_name)
where s.table_name = 表名称
order by s.column_id
s.data_type column_type,
decode(s.data_type,
'VARCHAR2',
s.data_length,
'CHAR',
s.data_length,
s.data_precision) column_length,
c.comments column_comments,
decode(s.nullable, 'Y', 'N', 'N', 'Y') isnotnull,
decode(l.column_name, null, 'N', 'Y') ispk
from user_tab_columns s
left join user_col_comments c
on (s.column_name = c.column_name and s.table_name = c.table_name)
left join user_constraints t
on (s.table_name = t.table_name and t.constraint_type = 'P')
left join user_cons_columns l
on (s.table_name = l.table_name and
t.constraint_name = l.constraint_name and
s.column_name = l.column_name)
where s.table_name = 表名称
order by s.column_id
- 获取Oracle10g表元数据 2
- 通过JDBC获取数据库表元数据
- Trafodion 元数据获取数据库所有表
- 获取数据库元数据
- 获取数据库元数据
- OSMF获取元数据
- ADO.NET 获取元数据
- id3元数据的获取
- java获取db元数据
- 元数据获取,Mark一下
- JDBC 获取元数据DatabaseMetaData
- Java获取DB元数据
- java--获取数据库元数据
- 获取SQL Server数据库的元数据
- 获取SQL Server元数据方法
- OAI-PMH:元数据获取标准
- 获取某个对象的元数据
- [MySQL] 获取元数据的方法
- 算法与数据结构-精确覆盖
- 关于集合的一些操作
- 1.0-学习Opencv与MFC混合编程之---CVMFC程序的继续开发步骤CVMFC程序的继续开发步骤
- 2011.12 程序员
- 杂记--注册表
- 获取Oracle10g表元数据 2
- 关于手机刷机后,eclipse中Logcat无法输出log信息的原因
- Heartbeat3.x应用全攻略之:安装、配置、维护
- python zipfile 创建处理压缩包
- 按标签显示新闻
- 服务器日志
- 程序员是这样干的嘛
- Android 近百个项目的源代码,覆盖Android开发的每个领域
- 浏览器兼容性解说1