oracle存儲過程實(shí)現(xiàn)導(dǎo)出表結(jié)構(gòu)

　　要將數(shù)據(jù)庫中的表結(jié)構(gòu)全部倒出來，有分區(qū)表和非分區(qū)表，涉及到的字段有number、data、timestamp、varchar2、char。所以只針對了這幾個字段的表的導(dǎo)出，如果表有其類型字段，則需要添加代碼。分區(qū)表都是以時間做分區(qū)的，所以導(dǎo)出來的分區(qū)表結(jié)構(gòu)都是以時間分區(qū)的。只是根據(jù)了自己實(shí)際情況寫的，根據(jù)不同的情況要改寫!

　　存儲過程帶一個參數(shù)，默認(rèn)為Y，導(dǎo)出分區(qū)表的分區(qū)，如果指定其他值，如

　　execu table_frame('N')，則只導(dǎo)出表結(jié)構(gòu)。

　　使用方法：

　　1、要導(dǎo)出哪個用戶的所有表結(jié)構(gòu)，就在該用戶下執(zhí)行最下面的存儲過程。

　　2、如下建立一個directory，同樣要在數(shù)據(jù)庫服務(wù)器D盤下建立一個名為‘結(jié)構(gòu)’的文件夾。

　　create or replace directory DIR_DUMP as 'd:/結(jié)構(gòu)';

　　3、執(zhí)行存儲過程，生成的表結(jié)構(gòu)代碼就在路徑d:/結(jié)構(gòu)下的txt文件中。

　　create or replace procedure table_frame(v_partition_status varchar2 default 'Y')

　　is

　　type column_type is table of user_tab_lumn_name%type;

　　v_column column_type;

　　type data_type is table of user_tab_columns.data_type%type;

　　v_type data_type;

　　type length_type is table of user_tab_columns.data_length%type;

　　v_length length_type;

　　type datapre_type is table of user_tab_columns.DATA_PRECISION%type;

　　v_ldatapre datapre_type;

　　type datasca_type is table of user_tab_columns.DATA_SCALE%type;

　　v_dayasca datasca_type;

　　v_str clob; 　file_name UTL_FILE.file_type;

　　v_tables varchar2(50);

　　partition_status varchar2(3);

　　partition_keywords varchar2(30);

　　TYPE part_cursor is ref CURSOR;

　　part_name part_cursor;

　　partition_name user_tab_partitions.partition_name%type;

　　high_value user_tab_partitions.high_value%type;

　　begin

　　file_name := UTL_FILE.FOPEN('DIR_DUMP','table.txt','w');

　　--判斷是否需要分區(qū)

　　partition_status := v_partition_status;

　　--按表循環(huán)

　　for j in (select table_name from user_tables group by table_name ) loop

　　v_tables :=upper(j.table_name);

　　v_str := 'create table '||v_tables||'(';

　　UTL_FILE.PUT_LINE(file_name,v_str);

　　--提取表的字段信息

　　select column_name,data_type,data_length,DATA_PRECISION,DATA_SCALE

　　bulk collect into v_column,v_type,v_length,v_ldatapre,v_dayasca

　　from user_tab_columns where table_name=v_tables;

　　--按字段循環(huán)

　　for i in 1..v_unt loop

　　if v_type(i)= 'DATE' or v_type(i) like 'TIMESTAMP%' then

　　v_str :=v_column(i)||' '||v_type(i)||',';

　　elsif v_type(i)= 'NUMBER' and v_ldatapre(i) is not null then

　　v_str :=v_column(i)||' '||v_type(i)||'('||v_ldatapre(i)||','||v_dayasca(i)||'),';

　　elsif v_type(i)= 'NUMBER' and v_ldatapre(i) is null then

　　v_str :=v_column(i)||' '||v_type(i)||',';

　　else

　　v_str :=v_column(i)||' '||v_type(i)||'('||v_length(i)||'),';

　　end if;

　　if i=v_unt then

　　v_str :=substr(v_str,1,length(v_str)-1);

　　end if;

　　UTL_FILE.PUT_LINE(file_name,v_str);

　　end loop; --判斷是否添加分區(qū)

　　if partition_status = 'Y' then

　　SELECT nvl(max(column_name),'0') into partition_keywords FROM USER_PART_KEY_COLUMNS

　　where object_type = 'TABLE' and name=v_tables;

　　if partition_keywords != '0' then

　　UTL_FILE.PUT_LINE(file_name,')partition by range ('||partition_keywords||')(');

　　open part_name for select partition_name,high_value from user_tab_partitions

　　where table_name = v_tables;

　　v_str := null;

　　loop

　　fetch part_name into partition_name,high_value;

　　if part_name%notfound then

　　--去掉最后逗號

　　v_str :=substr(v_str,1,length(v_str)-1);

　　UTL_FILE.PUT_LINE(file_name,v_str);

　　exit;

　　end if;

　　UTL_FILE.PUT_LINE(file_name,v_str);

　　v_str :='partition '||partition_name||' values less than ('||high_value||'),';

　　end loop;

　　end if;

　　end if;

　　UTL_FILE.PUT_LINE(file_name,');');

　　UTL_FILE.PUT_LINE(file_name,'-------------------------------------------------------------');

　　end loop;

　　UTL_FILE.fclose_all;

　　end;