获取Oracle10g表元数据-原创手记-慕课网

获取Oracle10g表元数据

最近，在写一个代码工具，从数据读取表生成实体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,

'(' || 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.comments is not null)

loop

v_ddl := v_ddl || com.col_com;

end loop;

if v_ddl is not 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

left join 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

order by 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

order by 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

order by 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 is null

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

order by 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 is null

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

Oracle职场休闲Oracle