获取表结构定义的SQL语句

create or replace function f_get_ddl(iv_user_name in varchar2,
iv_tab_name in varchar2)

return sys.ku$_ddls authid current_user is
-- ************************************************************
-- author : 23670
-- create_date : 2015/6/9 15:42:23
-- description : get table's definition
-- parameter_info:
-- vv_user_name: in
-- vv_table_name; in
-- modified_list:
-- 2015/6/9 15:42:23 create
-- ***********************************************************
-- define local variables.
vv_task_name varchar2(40) := 'f_get_ddl';
vd_start date := sysdate;
vv_task_msg varchar2(200);
vv_task_pos varchar2(200);

vi_h number; -- handle returned by 'open'
vi_th number; -- handle returned by 'add_transform'
vt_tab_ddl sys.ku$_ddls; -- table of ddl records
vi_h2 number; -- handle returned by 'open'
vi_th2 number;
vt_idx_ddl sys.ku$_ddls;
vv_tmp_ddl clob; -- creation ddl for an object
vi_tmp number;
vv_pk_name varchar2(40) := null;
vs_pi sys.ku$_parsed_items; -- parse items are returned in this object
vv_idxname varchar2(30); -- the parsed index name
vv_user_name varchar2(40) := upper(iv_user_name);
vv_tab_name varchar2(40) := upper(iv_tab_name);
vv_tmp_comm varchar2(200);

begin

vv_task_pos := '获取主键对应的索引名称';
begin
select c.index_name
into vv_pk_name
from all_constraints c
where c.owner = upper(vv_user_name)
and c.table_name = upper(vv_tab_name)
and c.constraint_type = upper('p');
exception
when no_data_found then
null;
end;
vv_task_pos := '获取表的DDL';
-- table ddl
-- specify the object type.
vi_h := dbms_metadata.open(upper('table'));
-- use filters to specify the particular object desired.
dbms_metadata.set_filter(vi_h, upper('schema'), vv_user_name);
dbms_metadata.set_filter(vi_h, upper('name'), vv_tab_name);
-- request that the metadata be transformed into creation ddl.
vi_th := dbms_metadata.add_transform(vi_h, upper('ddl'));
-- specify that segment attributes are not to be returned.
-- note that this call uses the transform handle, not the open handle.
dbms_metadata.set_transform_param(vi_th,
upper('segment_attributes'),
false);
dbms_metadata.set_transform_param(vi_th, upper('sqlterminator'), true);

-- fetch the object.
vt_tab_ddl := dbms_metadata.fetch_ddl(vi_h);
/*doc := dbms_metadata.fetch_clob(h);*/
-- release resources.
dbms_metadata.close(vi_h);
for x in 1 .. vt_tab_ddl.count loop
vt_tab_ddl(x).ddltext := replace(vt_tab_ddl(x).ddltext, '"', '');
vt_tab_ddl(x).ddltext := replace(vt_tab_ddl(x).ddltext,
upper(vv_user_name) || '.',
'');
end loop;

vv_task_pos := '获取索引的DDL';
-- index ddl
-- specify the object type.
vi_h2 := dbms_metadata.open(upper('index'));
-- the base object is the table retrieved in the outer loop.
dbms_metadata.set_filter(vi_h2,
upper('base_object_schema'),
vv_user_name);
-- dbms_metadata.set_filter(vi_h2, upper('name'), vv_tab_name);
dbms_metadata.set_filter(vi_h2, upper('base_object_name'), vv_tab_name);
-- exclude system-generated indexes.
dbms_metadata.set_filter(vi_h2, upper('system_generated'), false);
-- request that the index name be returned as a parse item.
dbms_metadata.set_parse_item(vi_h2, upper('name'));
-- request that the metadata be transformed into creation ddl.
vi_th2 := dbms_metadata.add_transform(vi_h2, upper('ddl'));
-- specify that segment attributes are not to be returned.
dbms_metadata.set_transform_param(vi_th2,
upper('segment_attributes'),
false);
dbms_metadata.set_transform_param(vi_th, upper('sqlterminator'), true);
dbms_metadata.set_transform_param(vi_th, upper('STORAGE'), false);

loop
vt_idx_ddl := dbms_metadata.fetch_ddl(vi_h2);
exit when vt_idx_ddl is null;
for i in vt_idx_ddl.first .. vt_idx_st loop
vv_tmp_ddl := vt_idx_ddl(i).ddltext;
vs_pi := vt_idx_ddl(i).parseditems;
-- loop through the returned parse items.
if vs_pi is not null and vs_pi.count > 0 then
for j in vs_pi.first .. vs_st loop
if vs_pi(j).item = upper('name') then
vv_idxname := vs_pi(j).value;
end if;
end loop;
end if;
-- 判断索引名和主键对应的索引名称是否相同
if upper(vv_idxname) != upper(vv_pk_name) or vv_pk_name is null then
vv_tmp_ddl := replace(vv_tmp_ddl, '"', '');
vv_tmp_ddl := replace(vv_tmp_ddl, upper(vv_user_name) || '.', '');
vi_tmp := vt_tab_st;
vt_tab_ddl(vi_tmp).ddltext := vt_tab_ddl(vi_tmp)
.ddltext || chr(10) || vv_tmp_ddl;
end if;
end loop;
end loop;
dbms_metadata.close(vi_h2);
-- add table comment ddl
vv_task_pos := '获取表的注释 ';
vv_tmp_comm := null;
select (case
when MENTS is not null then
'comment on table ' || t.TABLE_NAME || ' is ''' ||
replace(MENTS, chr(39), chr(34)) || ''';'
else
null
end)
into vv_tmp_comm
from ALL_TAB_COMMENTS t
where t.owner = vv_user_name
and t.table_name = vv_tab_name;
if vv_tmp_comm is not null then
vi_tmp := vt_tab_st;
vt_tab_ddl(vi_tmp).ddltext := vt_tab_ddl(vi_tmp)
.ddltext || chr(10) || vv_tmp_comm;

end if;
-- add columns comment ddl
vv_task_pos := '获取列的注释';

for x in (select

(case
when ments is not null then
'comment on column ' || t.table_name || '.' ||
t.column_name || ' is ''' ||
replace(ments, chr(39), chr(34)) || ''';'
else
null
end) comm
from all_col_comments t
where t.owner = vv_user_name
and t.table_name = vv_tab_name) loop
if m is not null then
vt_tab_ddl(vi_tmp).ddltext := vt_tab_ddl(vi_tmp)
.ddltext || chr(10) || m;
end if;
end loop;
-- end
vv_task_pos := '正常结束';
vv_task_msg := vv_task_pos;
-- p_db_job_runlog(vv_task_name, vd_start, vv_task_pos, vv_task_msg);
return vt_tab_ddl;

exception
when others then
-- 发生异常
vv_task_msg := substr(sqlerrm, 1, 200);
-- p_db_job_runlog(vv_task_name, vd_start, vv_task_pos, vv_task_msg);
if vt_tab_ddl is null then
dbms_output.put_line('集合为空');
end if;
return vt_tab_ddl;
end;
/