NiXXX
Member | Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Код: /* Генерация html-страницы с описанием таблиц и полей для текущей схемы */
declare
l_tab_comment varchar2(1000);
l_counter number := 1;
l_curr_schema varchar(100);
cursor c_tlist is (
select distinct (o.OBJECT_NAME)
from user_objects o
where o.OBJECT_NAME not like '%$%'
and o.OBJECT_TYPE = 'TABLE')
order by 1 asc;
begin
dbms_output.enable(buffer_size => NULL);
begin
dbms_output.put_line('<!DOCTYPE HTML>' || chr(13) || chr(10) ||
'<HTML>' || chr(13) || chr(10) ||
'<HEAD>' || chr(13) || chr(10) ||
'<meta http-equiv="content-type" content="text/html; charset=utf-8">' || chr(13) || chr(10) ||
'</HEAD>');
end;
begin
select sys_context('userenv', 'CURRENT_SCHEMA')
into l_curr_schema
from dual;
dbms_output.put_line('<h3>Список таблиц схемы ' || l_curr_schema || ': </h3>' || chr(13) || chr(10) || '<ul>');
-- 1. Формирование списка таблиц
for z in c_tlist loop
dbms_output.put_line('<li><a href="#a' || l_counter || '">' || z.object_name || '</a></li>');
l_counter := l_counter + 1;
end loop;
dbms_output.put_line('</ul>' || chr(13) || chr(10) || '<hr color="SILVER" size="2">' || chr(13) || chr(10) || '<br>');
end;
l_counter := 0;
-- 2. Формирование заголовков таблиц
for y in c_tlist loop
begin
l_counter := l_counter + 1;
select u.comments
into l_tab_comment
from user_tab_comments u
where u.TABLE_NAME = y.object_name;
if l_tab_comment is null then
select 'описание отсутствует'
into l_tab_comment
from dual;
end if;
exception
when no_data_found then
select 'описание отсутствует'
into l_tab_comment
from dual;
when others then
null;
end;
dbms_output.put_line('<TABLE COLS=5 BORDER=1>' || chr(13) || chr(10) ||
'<TR ALIGN=LEFT BGCOLOR=LIGHTGREY>' || chr(13) || chr(10) ||
'<TH colspan="5"><H3 id="a' || l_counter || '"><I>' || y.object_name || ' - ' || l_tab_comment || '</I>' || chr(38) || 'nbsp;' || chr(38) || 'nbsp;' || chr(38) || 'nbsp;' || '<a href="#" onClick="scroll(0,0)" title="К списку таблиц">' || chr(38) || '#8679;</a></H3></TH></TR>' || chr(13) || chr(10) ||
'<TR ALIGN=CENTER BGCOLOR=SILVER><TD><b>Поле</b></TD>' || chr(13) || chr(10) ||
'<TD><b>Тип</b></TD>' || chr(13) || chr(10) ||
'<TD><b>Обязательность заполнения</b></TD>' || chr(13) || chr(10) ||
'<TD><b>Значение по умолчанию</b></TD>' || chr(13) || chr(10) ||
'<TD><b>Комментарий</b></TD>' || chr(13) || chr(10) ||
'</TR>');
-- 3. Заполнение таблиц
for x in (select t.COLUMN_NAME,
t.DATA_TYPE || case
when to_char(t.DATA_PRECISION) is null then
''
else
'(' || to_char(t.DATA_PRECISION) || ')'
end D_T_P,
t.NULLABLE,
t.DATA_DEFAULT,
c.comments
from user_tab_columns t
join user_objects o on o.OBJECT_NAME = t.TABLE_NAME and o.OBJECT_TYPE = 'TABLE' and t.TABLE_NAME = y.object_name
left join user_col_comments c on c.table_name = t.TABLE_NAME and c.column_name = t.COLUMN_NAME
order by t.COLUMN_ID asc) loop
dbms_output.put_line('<TR ALIGN=Left>' ||
'<TD>' || x.COLUMN_NAME || '</TD>' || chr(13) || chr(10) ||
'<TD>' || x.D_T_P || '</TD>' || chr(13) || chr(10) ||
'<TD align="center">' || x.NULLABLE || '</TD>' || chr(13) || chr(10) ||
'<TD align="center">' || x.DATA_DEFAULT || '</TD>' || chr(13) || chr(10) ||
'<TD>' || x.comments || '</TD>' || chr(13) || chr(10) ||
'</TR>');
end loop;
dbms_output.put_line('</TABLE>');
dbms_output.put_line('<br>');
end loop;
dbms_output.put_line('</BODY>' || chr(13) || chr(10) || '</HTML>');
end; |
|
Всего записей: 365 | Зарегистр. 06-05-2003 | Отправлено: 16:10 15-08-2018 | Исправлено: NiXXX, 08:39 07-09-2018 |
|
