Задача. Скрипт создания таблиц.

Получить из словаря данных упрощенный скрипт создания всех таблиц текущей схемы.

Напишем запрос, вглядевшись в поля data_type, data_precision, data_scale, data_length:

select 'CREATE TABLE ' || tc.table_name || ' (' ||

wm_concat(tc.column_name || ' ' || tc.data_type || case

when data_type = 'NUMBER' and data_precision is not null then

'(' || data_precision || ',' || data_scale || ')'

when data_type in ('VARCHAR2', 'CHAR') then

'(' || data_length || ')'

end) || ');' create_table_script

from user_tab_columns tc, user_objects o

where tc.table_name = o.object_name

and o.object_type = 'TABLE'

group by tc.table_name;

Задача. Отчет о созданных объектах.

Написать запрос, который собирает из USER_OBJECTS по объектам, начинающимся с 'KK', строку вида:

'2012-01-12 12:16:05 создали FUNCTION KK_FUNC. 2012-01-15 11:06:15 создали VIEW KK_VIEW1. 2012-01-17 18:00:01 создали TABLE KK_TAB1. ...'

в порядке создания. Запрос должен выдавать 1 строку.

В подзапросе q1 собираем информацию об интересующих нас объектах, далее используем его в ранее описанном запросе по склейке строк в одну.

select res

from (select replace(sys_connect_by_path(string_about_creation, '@'), '@') as res,

level

from (select string_about_creation,

ordr,

lag(ordr) over(order by ordr) as prev_ordr

from (select created,

to_char(created, 'dd.mm.yyyy hh24:mi:ss') ||

' создали ' || object_type || ' ' ||

object_name || '.' || chr(13) || chr(10) as string_about_creation,

row_number() over(order by created) ordr

from user_objects

where object_name like 'KK%') q1)

start with prev_ordr is null

connect by prev_ordr = prior ordr

order by level desc)

where rownum = 1;

Задача. Отчет о числе строк в таблицах.

Создать пустую таблицу KK_CNT_REPORT(table_name varchar2(30), cnt number).

Написать процедуру KK_FILL_CNT_REPORT(in_table_list in varchar), которая на входе получает список таблиц, разделенных запятыми, например, 'KK_TBL1,KK_TBL2,KK_TBL3', и заполняет таблицу KK_CNT_REPORT данными о количестве строк в этих таблицах.

Создадим таблицу:

CREATE TABLE KK_CNT_REPORT(TABLE_NAME VARCHAR2(30), CNT NUMBER);

Напишем требуемую процедуру, используя запрос по "расклейке"

CREATE OR REPLACE PROCEDURE KK_FILL_CNT_REPORT(IN_TABLE_LIST IN VARCHAR2) IS

BEGIN

EXECUTE IMMEDIATE 'TRUNCATE TABLE KK_CNT_REPORT';

FOR Z IN (SELECT REGEXP_SUBSTR(IN_TABLE_LIST, '[^,]+', 1, LEVEL) TABLE_NAME

FROM DUAL

CONNECT BY REGEXP_SUBSTR(IN_TABLE_LIST, '[^,]+', 1, LEVEL) IS NOT NULL) LOOP

EXECUTE IMMEDIATE 'INSERT INTO KK_CNT_REPORT (TABLE_NAME, CNT) SELECT ''' ||

Z.TABLE_NAME || ''', COUNT(1) FROM ' || Z.TABLE_NAME;

END LOOP;

COMMIT;

END KK_FILL_CNT_REPORT;

Запустим процедуру:

BEGIN

KK_FILL_CNT_REPORT('KK_MKB,KK_STUD_2010,KK_STUD_2012,KK_STUDENTS_ALL_YEARS');

END;

Проверим результат:

SELECT * FROM KK_CNT_REPORT;