Oracle 获取表带字段备注SQL语句存储过程

来源：互联网发布：淘宝新店如何引流编辑：程序博客网时间：2024/04/29 17:22

为了方便工作，今天特意写了个存储过程自动生成带注释的SQL（Oracle），特别是在大表中，我觉得挺好，不用再不停地快捷键操作了，好了废话少说，开始：

首先，先创建一个表SQLResult，用来存储表名，SQL语句内容，和创建时间，脚本如下：

create table Table_SQLResult
(
tablename varchar2(50), /*表名*/
sqlremark varchar2(4000 char), /*SQL语句内容*/
createtime date /*创建时间*/
)
tablespace 当前数据库用户名
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 16
next 8
minextents 1
maxextents unlimited
);

开始创建存储过程PROC_GetSQLRemark，存储过程根据传入一个表名（区分大小写），获取带注释字段的SQL语句，注释只取前15位（Oracle标识最长可为30，中文算2 个），所以只取前15，可根据使用调整，生成的SQL语句存储在数据库表SQLResult 中，也可在代码中调用（大于4000字节内容的SQL没试过，有兴趣自己可以试一下），对于大于4000字节内容的SQL，会把SQL分开存取进表中，使用时再连接起来，存储过程如下：

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;

0 0