На днях возникла необходимость посмотреть вхождение ID некой записи в таблицы достаточно большой БД.
Структура данных приблизительно следующая:
Таблица где хранятся наши объекты Dret (id integer, description varchar(25))
Ссылки на запись в других таблицах все имеют вид id_dret integer.
После недолгих раздумий была написана функция возвращающая одно значение по запросу который ей передается в аргументе:
// Выполняем скрипт, результат в массив
CREATE OR REPLACE FUNCTION TOOLS.GET_STR ( P_SEL VARCHAR(4000) )
RETURNS SYSIBMADM.DBMS_OUTPUT.CHARARR
BEGIN
DECLARE SQLSTATE CHAR(5);
DECLARE I INT DEFAULT 1;
DECLARE ARR DBMS_OUTPUT.CHARARR;
DECLARE c1 CURSOR FOR s1;
PREPARE s1 FROM P_SEL;
OPEN c1;
FETCH c1 INTO ARR[I];
WHILE SQLSTATE = '00000' DO
SET I = I + 1;
FETCH c1 INTO ARR[I];
END WHILE;
CLOSE c1;
RETURN ARR;
END;
// Обертка результат в таблицу
CREATE OR REPLACE FUNCTION TOOLS.GET_STR_SQL ( P_SEL VARCHAR(4000) )
RETURNS TABLE ( STR VARCHAR(1000) )
RETURN
SELECT STR
FROM UNNEST(TOOLS.GET_STR(P_SEL)) T(STR);
После чего был написан запрос который возвращает список таблиц в которых есть поле id_dret и в этом поле есть искомое значение:
with tab as (
select trim(sc.TABSCHEMA) TABSCHEMA,trim(sc.TABNAME) TABNAME,trim(sc.COLNAME) COLNAME
from SYSCAT.COLUMNS sc
join SYSCAT.TABLES st
on trim(st.TABSCHEMA)||'.'||trim(st.TABNAME)=trim(sc.TABSCHEMA)||'.'||trim(sc.TABNAME)
and st.TYPE='T'
where upper(trim(sc.COLNAME))='ID_DRET' /*В каком поле*/ and sc.TYPENAME='INTEGER'
)
select tab.TABSCHEMA||'.'||tab.TABNAME from tab
join TABLE(TOOLS.GET_STR_SQL(
'SELECT distinct ''Y'' FROM "'||tab.TABSCHEMA||'"."'||tab.TABNAME||
'" where "'||tab.COLNAME||'"='||4411016 -- что ищем
)) che(res) on che.res='Y'
Не самое простое, не самое лучшее, но для меня самое быстрое решение.
Надеюсь на замечания и комментарии.