riverhead
Дата: 06.03.2011 11:52:10
Вообщем задача состоит из 2 частей:
1 часть:
есть 5 таблиц:
- первые 2 таблицы имеют по 4 поля и тип у всех полей "number";
- третья таблица состоит из 3 полей (у нее два поля типа "number", а третье поле типа "char");
- четвертая таблица состоит из 3 полей (у нее два поля типа "number", а третье поле типа "varchar2");
- пятая таблица состоит из 3 полей (у нее два поля типа "number", а третье поле типа "blob");
Первичных ключей у всех пяти таблиц нет. Они просто состоят из полей заданных типов с ограничением “not null”
Вот в первой части задачи нужно было заполнить рандомно 10 строк первых 4 таблицы, а в пятой таблице вручную заполнить одну строку – там просто тип "blob" и его не получится рандомно проставить.
Вообщем эту часть я сделал - получил 5 заполненных таблиц:
2 часть:
Нужно написать pl/sql процедуру, на вход которой подаем слово (набор символов типа “varchar2”) - эта процедура должна вывести "имя_таблицы.имя_столбца" в которых встречается это слово.
Она должна искать это слово только в столбцах типа char или varchar2, т.е в одном из полей таблиц TABLE3 и TABLE4;
Загвоздка в том, что нужно использовать курсор внутри процедуры, а я не знаю, что он должен выдавать в результате своего выполнения и вообще чет запутался.
Буду признатнен за помощь.
riverhead
Дата: 06.03.2011 11:53:27
Пока вот только такой набросок:
CREATE OR REPLACE PROCEDURE (SEARCH_STRING IN VARCHAR2)
AS
CREATE CURSOR
SELECT (ну вот здесь наверное он должен все-таки возвращать имя таблицы и имя строки)
FROM USER_TAB_COLUMNS
WHERE T.DATA_TYPE IN ('CHAR','VARCHAR2')
AND …
А и еще думал вот так сделать:
Такой вот код внутри процедуры:
SELECT T.TABLE_NAME||'.'||T.COLUMN_NAME
FROM USER_TAB_COLUMNS T
WHERE T.DATA_TYPE IN ('CHAR','VARCHAR2')
AND TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES)
AND EXISTS ( вот сюда по идее нужно загнать код, который бы возвращал что-то при условии того, что что-то найдется…)
riverhead
Дата: 06.03.2011 21:21:02
andreymx - не такая подгонка не пойдет
Добрый Э - Эх, что в этом коде подправить, чтоб он выводил "имя_таблицы.имя_столбца" вместо "owner.table_name"?
with
v_tab_col as
(
select '"'||c.table_name||'"' as table_name,
'"'||c.column_name||'"' as column_name,
'"'||c.owner||'"' as owner,
row_number() over(partition by c.owner, c.table_name order by c.column_id) as rn
from dba_tab_columns c, dba_objects o
where data_type in ('CHAR','VARCHAR2')
and c.table_name = o.object_name
and o.object_type = 'TABLE'
and o.owner = c.owner
and o.owner = upper(:owner)
),
sql_text as
(
select 'select '''||owner||'.'||table_name||
''' as VALUE /*, 1 as flag*/ from dual where exists ( select null from '||
owner||'.'||table_name||' where '|| rtrim(replace(max(sys_connect_by_path('upper('||
column_name||') like upper(''%'||:WHAT_SEARCH||'%'') OR ',','))
keep(dense_rank last order by level),','),'OR ')||')' as sql_text
from v_tab_col
start with rn = 1
connect by prior table_name = table_name
and prior owner = owner
and prior rn = rn - 1
group by owner, table_name
)
Select *
from (
select extractvalue
(dbms_xmlgen.getxmltype
(sql_text)
, '/ROWSET/ROW/VALUE'
) as value
from sql_text
)
where value is not null