Табличная функция DB2: динамический запрос

в 16:38, , рубрики: Песочница, метки:

На днях возникла необходимость посмотреть вхождение 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'

Не самое простое, не самое лучшее, но для меня самое быстрое решение.
Надеюсь на замечания и комментарии.

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js