create table Table_SQLResult(tablename varchar2(50), /* 表名*/sqlremark varchar2(4000 char), /*SQL语句内容*/createtime date /*创建时间*/)tablespace 当前数据库用户名pctfree 10initrans 1maxtrans 255storage(initial 16next 8minextents 1maxextents unlimited);
create or replace procedure PROC_GetSQLRemark
(
tableName_in varchar2 /*传入表名区分大小*/
/*SQLstr_out out varchar2 --返回带中文注释SQL语句*/
) is
v_sqlresult varchar2(32767); /*SQL内容*/
v_tableNameCount number; /*用来判断是否已经存在相同表*/
v_createTime date; /*默认当前系统时间*/
cursor v_cur is
select distinct column_id, column_name||' as "'|| substr(replace(replace(replace(replace(comments,',',''),'.',''),'、',''),';',''),0,15)||'",' as rowname from ( /*注意Oracle标识符长度不要超过30,中文算2个*/
select b.column_name column_name /*字段名*/
/*,b.data_type data_type --字段类型
,b.data_length --字段长度 */
,NVL(a.comments,b.column_name) comments /*字段注释*/
,b.column_id /*列字段序号ID*/
from user_col_comments a left join
(select distinct table_name,column_name,column_id from all_tab_columns where table_name = tableName_in) b
on a.column_name = b.column_name and a.table_name = b.table_name
where a.table_name = b.table_name and a.table_name = tableName_in /*注意表名区分大小写*/
) order by column_id;
begin
select count(*) into v_tableNameCount from Table_SQLResult where tablename = tableName_in;
if v_tableNameCount > 0 then
return;
end if;
v_createTime := sysdate;
v_sqlresult := 'select ';
for re in v_cur loop
begin
v_createTime := v_createTime + 1/(24*60*60); /* 加1秒钟,以区别SQL语句字节大于4000的SQL先后顺序*/
if(length(re.rowname) <> 0) then
v_sqlresult := v_sqlresult||re.rowname;
if(lengthb(v_sqlresult) > 4000-176) then
insert into Table_SQLResult(tablename,sqlremark,createtime) values(tableName_in,v_sqlresult,v_createTime);
v_sqlresult := '';
end if;
end if;
end;
end loop;
if(lengthb(v_sqlresult) < 4000) then
v_sqlresult := substr(v_sqlresult,0,length(v_sqlresult) - 1)||' from '||tableName_in;
insert into Table_SQLResult(tablename,sqlremark,createtime) values(tableName_in,v_sqlresult,v_createTime);
end if;
commit;
/* sqlresult := substr(sqlresult,0,length(sqlresult) - 1)||' from '||tableName_in;
SQLstr_out := sqlresult;
insert into Table_SQLResult(tablename,sqlremark,createtime) values(tableName_in,v_sqlresult,v_createTime);
commit;*/
end PROC_GetSQLRemark;