Ищете тип данных, который можно заполнить с помощью BULK COLLECT INTO, а затем выбрать из него с помощью SELECT.

У меня большая проблема с пакетом PL-SQL, с которым я сейчас работаю.

Все, что я хочу сделать, это создать небольшой фрагмент кода, который будет делать это:

В разделе IS функции:

l_tabellen_excl DBMS_utility.name_array;

Далее в коде:

SELECT DISTINCT TABLE_NAME
BULK COLLECT INTO l_tabellen_excl
FROM
ALL_TAB_COLUMNS
WHERE
TABLE_NAME IN ('TAB_1', 'TAB_2');

Чтобы, наконец, использовать эту переменную в операторе SELECT:

AND col.table_name NOT IN (SELECT * FROM TABLE (l_tabellen_excl))

Я получаю ORA-22905: cannot access rows from a non-nested table item здесь.

Я знаю, что мог бы просто написать AND col.table_name NOT IN ('TAB_1','TAB_2'), но я не хочу жестко кодировать его в самых глубоких ямах кода... что затрудняет его поиск и менее настраиваемый.

Я пробовал ТОННУ вещей:

type array_t is table of varchar2(10);

Не работает. Я получаю сообщение об ошибке, говорящее о том, что я не могу использовать локально объявленную коллекцию в операторе SELECT.

Я попытался привести переменную i_tabellen_excl к локально объявленному типу - как обходной путь. Но я получаю ORA-00902 - неверный тип данных.

Я попытался объявить VARCHAR2, содержащий строку со списком таблиц, разделенных запятыми, кажется, что он работает, но все же он далек от чистого, хорошо написанного, хорошо разработанного кода.

Я пробовал другие варианты, о которых не стоит упоминать, например, пытался написать функцию и т. Д.

Я потерялся в этом вопросе, ЛЮБЫЕ идеи было бы здорово проверить.


oracle plsql nested-table table-functions
person dziki    schedule 12.10.2015    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Если вы хотите использовать функцию TABLE, вы должны создать TYPE как тип OBJECT, который сохранит его в базе данных и создаст функция, использующая его. Позже вы сможете использовать табличную функцию в своем коде PL/SQL.

В противном случае, как вы это делаете, используя Вложенную таблицу, вам придется снова выполнить ЦИКЛ, чтобы сослаться на объект коллекции.

SET serveroutput ON
DECLARE
type str_typ
IS
  TABLE OF VARCHAR2(200);
  str_sub str_typ := str_typ ();
BEGIN
  SELECT DISTINCT TABLE_NAME BULK COLLECT INTO str_sub FROM ALL_TAB_COLUMNS;
  FOR i IN 1..str_sub.count
  LOOP
    dbms_output.put_line(str_sub(i));
  END LOOP;
END;
/

Таким образом, вы не можете использовать его как объект базы данных, для этого вы должны создать тип как тип объекта и создать табличную функцию.

Например,

Создайте тип

SQL> CREATE TYPE col_type AS OBJECT (
  2    col_name VARCHAR2(50)
  3  );
  4  /

Type created.

SQL> CREATE TYPE col_sub_type IS TABLE OF col_type;
  2  /

Type created.

Построить табличную функцию

SQL> CREATE OR REPLACE FUNCTION get_col_tab RETURN col_sub_type AS
  2    l_type  col_sub_type := col_sub_type();
  3  BEGIN
  4    FOR i IN (SELECT DISTINCT TABLE_NAME t_name FROM ALL_TAB_COLUMNS) LOOP
  5      l_type.extend;
  6      l_type(l_type.last) := col_type(i.t_name);
  7    END LOOP;
  8
  9    RETURN l_type;
 10  END;
 11  /

Function created.

Протестируйте

SELECT *
FROM   TABLE(get_col_tab());
person Lalit Kumar B    schedule 12.10.2015
comment
Я поместил его в пакет Oracle, но все равно - чертовски много ошибок компиляции. И еще - могу ли я получить доступ к этому str_sub в цикле? Все, что мне нужно, это объявить переменную, содержащую выбранные имена таблиц, и использовать их в операторе выбора, чтобы исключить их из вывода :( - person dziki; 12.10.2015
comment
Я вижу отредактированную версию. Теперь может быть гораздо полезнее! Спасибо! - person dziki; 12.10.2015
comment
@dziki Для первого подхода, используя вложенную таблицу, вам нужно сформировать динамический sql и использовать EXECUTE IMMEDIATE. Потому что вам нужно перебирать имена таблиц и использовать их динамически. - person Lalit Kumar B; 12.10.2015
comment
еще один вопрос. Где в теле пакета я должен его поместить? Что ни пробую - не работает. - person dziki; 12.10.2015
comment
Что ты пытаешься? Первый способ с использованием вложенной таблицы или табличной функции? Для первого способа вам просто нужно сделать это в теле пакета. Просто объявите вложенную таблицу и инициализируйте ее. Вы можете увидеть рабочий пример. Обычный и простой PL/SQL. Для второго подхода используется табличная функция. вам нужно сделать это снаружи один раз. Типы и функции будут созданы как объекты базы данных и будут храниться в базе данных. Вам не нужно создавать какой-либо тип/коллекцию в пакете, все, что вам нужно сделать, это напрямую обратиться к табличной функции, как и к любой другой статической таблице. - person Lalit Kumar B; 12.10.2015
comment
Второй подход работает нормально, но он менее настраиваемый. Я просто не понимаю подход NESTED TABLE. Я хочу использовать вывод в операторе SQL... Чего я не могу здесь сделать, верно? - person dziki; 12.10.2015
comment
Возможен даже подход NESTED table, однако для этого вам нужно использовать динамический sql. Чтобы в цикле можно было передать имя таблицы и использовать его в фильтре. Вам комфортно с dynamic sql и execute immediate? - person Lalit Kumar B; 12.10.2015
comment
Может быть, если вы сообщите мне свое точное требование, я смогу помочь вам лучше, то есть почему вы хотите поместить информацию о метаданных в коллекцию? Чего вы пытаетесь добиться от кода? - person Lalit Kumar B; 12.10.2015
comment
Ну, дело не в том, что второй подход не настраивается. Вы можете взглянуть на тип RECORD в PL/SQL. - person Lalit Kumar B; 12.10.2015
comment
Я хочу объявить: строку, массив или таблицу, которая будет содержать имена таблиц для исключения. Во-вторых, я хочу использовать этот массив/строку/что угодно в операторе SELECT, например: допустим, переменная называется tables. Под этой переменной выбранного типа скрыты имена таблиц, которые я хочу исключить. Затем в операторе SQL Select я хочу использовать что-то вроде этого: AND TABLE_NAME NOT LIKE tables. Этот оператор AND скрыт вокруг строки 240 пакета Oracle, я хочу иметь переменную tables в начале файла... - person dziki; 12.10.2015
comment
Тогда почему бы не создать параметризирующий курсор и передать имена таблиц для исключения в качестве меняющегося списка IN. Не лучшее решение, но у вас есть 3 варианта. См. мой блог о различных списках IN lalitkumarb.com/2015/01/02/ и см. эту статью для параметризованного курсора java2s.com/Tutorial/Oracle/0500__Cursor/. Лично я бы использовал второй подход к табличной функции и сделал бы ее конвейерной функцией. - person Lalit Kumar B; 12.10.2015