很久以前写的一段代码了。
还记得那个时候是我刚从学校出来的那段时光,整天呆在公司,还没有具体事物交给我做。当时,由于比较闲,一个同事就跑过来,问我。说可不可以把oracle当前
用户下所有表的建表语句都导出来。
当时由于比较闲,就花了一个下午的时间,研究这个问题。我发现很多数据库GUI的程序,都不能做到这一点。后来发现可以在cmd里用数据库的一些命令,把所有的建
表语句都打印出来,然后再利用spool off截屏之后把文本放到一个文本文件里。这样做显然是可以的,可是我那个同事却说,一定要一个表导出一个文件。
听完这句话,我当时就想到了两个解决办法。
1,就是把所有表的建表语句导出到一个文本文件里,然后再用正则匹配,对整个文件进行切割,按表名切割成多个.sql的文件。
2, 从源头上解决整个问题,那就是自己写plsql代码块,然后自己去拼接整个建表语句,然后将拼接的字符写到硬盘上。
当时的我对于正则匹配还不是很了解,所以就很倾向于第二种方式。
下面就是我的所有代码:
--获取表的注释
--tabname表名
create or replace function get_tab_comment(tabname varchar2)
return varchar2
as
Result varchar2(4000);
v_query_sql varchar2(400);
v_acc_nbr varchar2(400);
type ref_cursor is ref cursor;
v_cursor ref_cursor;
begin
Result:='';
v_query_sql:= 'select COMMENTS from user_tab_comments where TABLE_NAME ='''||tabname||'''';
open v_cursor for v_query_sql;
loop
fetch v_cursor into v_acc_nbr;
exit when v_cursor%notfound;
if(v_acc_nbr is null) then
Result:=Result||'';
else Result:=Result||'comment on table '||tabname||CHR(10)||' is '''||v_acc_nbr||''''||';';
end if;
end loop;
close v_cursor;
return Result;
end get_tab_comment;
/
--获取列的注释
--tabname表名
create or replace function get_col_comments(tabname varchar2)
return varchar2
as
Result varchar2(4000);
v_query_sql varchar2(400);
type ref_cursor is ref cursor;
v_cursor ref_cursor;
v_col_name varchar2(4000);
v_comments varchar2(4000);
begin
Result:='';
v_query_sql:= 'select column_name,comments from user_col_comments where TABLE_NAME ='''||tabname||'''';
open v_cursor for v_query_sql;
loop
fetch v_cursor into v_col_name,v_comments;
exit when v_cursor%notfound;
if(v_comments is null) then
Result:=Result||'';
else Result:=Result||'comment on '||tabname||'.'||lower(v_col_name)||CHR(10)||' is '''||v_comments||''''||';'||CHR(10);
end if;
end loop;
close v_cursor;
return Result;
end get_col_comments;
/
--将查出的所有创建表语句的结果集放在一个视图里
CREATE OR REPLACE VIEW STUDENT AS
SELECT t.TABLE_NAME as tabname,DBMS_METADATA.GET_DDL('TABLE',table_name) as temp FROM USER_TABLES t;
--创建一个目录盛放写出的文件
create or replace directory TESTFILE as 'D:\zzk\upload\service';
/
declare
CURSOR C_EVENT is select t.tabname,t.temp from student t;
temp Student.TEMP%TYPE;
filehandle utl_file.file_type;
mystring VARCHAR2(4000):='';
begin
for temp in C_EVENT
Loop
filehandle := utl_file.fopen('TESTFILE',lower(temp.TABNAME)||'.sql','w');
if(get_tab_comment(temp.TABNAME) is null) then
utl_file.put_line(filehandle, temp.TEMP||get_col_comments(temp.TABNAME));
else utl_file.put_line(filehandle, temp.TEMP||get_tab_comment(temp.TABNAME)||CHR(10)||get_col_comments(temp.TABNAME));
end if;
utl_file.fclose(filehandle);
End Loop;
end;
/
--删除为执行任务而创建的资源
drop directory testfile;
drop view student;
drop function get_tab_comment;
drop function get_col_comments;
/
--select get_tab_comment('HOUSEHOLD') from dual;
由于本机没有oracle,所以这里就不往上贴测试图片了。