Ребят, помогите пожалуйста с задачей

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 ( вот сюда по идее нужно загнать код, который бы возвращал что-то при условии того, что что-то найдется…)
semenar
Дата: 06.03.2011 12:39:39
вам в форум - работа. Вроде еще не время сессии.
Добрый Э - Эх
Дата: 06.03.2011 15:09:42
riverhead, почитай тут: тынц
может чего для себя и почерпнёшь...
andreymx
Дата: 06.03.2011 20:07:06
riverhead
Вообщем задача состоит из 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;
Загвоздка в том, что нужно использовать курсор внутри процедуры, а я не знаю, что он должен выдавать в результате своего выполнения и вообще чет запутался.

Буду признатнен за помощь.


begin
for rec in
(
   select <третье поле> from <третья таблица>
    where <третье поле> like <строка поиска>
)
loop
      <вывести значение>
end loop;

for rec in
(
   select <третье поле> from <четвертая таблица>
    where <третье поле> like <строка поиска>
)
loop
      <вывести значение>
end loop;

end;
andreymx
Дата: 06.03.2011 20:09:29
чуть прогнал :)
begin
for rec in
(
   select <третье поле> from <третья таблица>
    where <третье поле> like <строка поиска>
      and rownum=1
)
loop
      <вывести "третья таблица" и "третье поле">
end loop;

for rec in
(
   select <третье поле> from <четвертая таблица>
    where <третье поле> like <строка поиска>
      and rownum=1
)
loop
      <вывести <"четвертая таблица" и "третье поле">
end loop;

end;
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