Задача. Поиск текста во вьюшках.

Как вы уже знаете, в функциях, процедурах, пакетах и триггерах можно найти интересующий текст во вьюшке словаря данных user_source:

select * from user_source where upper(text) like '%SIEBEL%';

Однако тексты вьюшек хранятся отдельно от user_source во вьюшке user_views.

Найти в user_views все вьюшки, содержащие в коде выражение 'SIEBEL'.

Попытка поискать данные в поле text с типом данных long запросом

select * from user_views where text like '%SIEBEL%';

приводит к ошибке, т.к. тип данных long устроен хитро.

Переменную типа long при длине текста до 32000 символов можно сравнивать like-ом внутри PL/SQL блока:

begin

for z in (select view_name, text from user_views where text_length < 32000) loop

if upper(z.text) like '%SIEBEL%' then

dbms_output.put_line(z.view_name);

end if;

end loop;

end;

После этого можно вручную посмотреть вьюшки с text_length >= 32000 и поискать в их тексте желанное выражение:

select view_name, text from user_views where text_length >= 32000;

Динамический sql.

Если структура используемых объектов фиксирована, то мы используем простой SQL.

Если же мы хотим выполнить действия, формируя сам текст команды динамически, то для этого используется динамический SQL, который обычно реализуется с помощью команды execute immediate.

Кроме того, DDL-команды в PL/SQL-блоке можно выполнять только через execute immediate.

Например,

declare

v_table_name varchar2(30);

v_vw_name varchar2(30);

v_table_name2 varchar2(30);

begin

-- Добавим столбец в динамически заданной таблице

v_table_name := 'KK1';

execute immediate 'alter table ' || v_table_name || ' add id_2 integer';

-- Динамически создадим вьюшку

v_table_name := 'KK2';

execute immediate 'create or replace view vw_' || v_table_name ||

' as select * from ' || v_table_name;

-- Вернем результат динамически сформированного запроса в переменную

v_vw_name := 'USER_TABLES';

execute immediate 'select min(table_name) from ' || v_vw_name ||

' where table_name <> ''' || v_table_name || ''''

into v_table_name2;

dbms_output.put_line(v_table_name2);

end;

Вывод DBMS_OUTPUT можно смотреть в PL/SQL Developer на соседней закладке с закладкой ‘SQL’.

Задача. Удаление конкретной таблицы, если она существует.

Во вьюшке user_tables можно увидеть таблицы пользователя, под которым залогинен.

Создать таблицу kk_tst. Написать код который проверяет, существует ли таблица kk_tst.

Если да, то дропает ее, если нет, то ничего не делает.

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

CREATE table kk_tst(id integer);

Этот блок кода решает задачу. Корректно работает при наличии и при отсутствии таблицы KK_TST в текущей схеме:

DECLARE

I INTEGER := 0;

BEGIN

SELECT COUNT(1) INTO I FROM USER_TABLES WHERE TABLE_NAME = 'KK_TST';

IF I = 1 THEN

EXECUTE IMMEDIATE 'DROP TABLE KK_TST'; -- ДИНАМИЧЕСКИЙ SQL

END IF;

END;

Проверим, есть таблица или нет:

SELECT * FROM USER_TABLES WHERE TABLE_NAME = 'KK_TST';

Продемонстрируем еще один вариант решения задачи, через именованные курсоры:

DECLARE

CURSOR KK_CR IS

SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = 'KK_TST';

V_GT KK_CR%ROWTYPE;

BEGIN

DBMS_OUTPUT.ENABLE;

OPEN KK_CR;

FETCH KK_CR

INTO V_GT;

IF KK_CR%FOUND THEN

DBMS_OUTPUT.PUT_LINE('KK_TST НАЙДЕНА');

EXECUTE IMMEDIATE 'DROP TABLE KK_TST';

DBMS_OUTPUT.PUT_LINE('KK_TST ДРОПНУТА');

ELSE

DBMS_OUTPUT.PUT_LINE('KK_TST НЕ НАЙДЕНА');

END IF;

CLOSE KK_CR;

END;