Как проверить условие IN в динамическом списке в Oracle?

РЕДАКТИРОВАТЬ: изменил заголовок, чтобы он соответствовал приведенному ниже коду.

Я пытаюсь получить список допустимых значений из таблицы Oracle, а затем выполнить SELECT для другого, сравнивая некоторые поля с указанным списком.

Я пытался сделать это с помощью курсоров (как показано ниже), но это не удалось.

DECLARE
    TYPE gcur IS REF CURSOR;
    TYPE list_record IS TABLE OF my_table.my_field%TYPE;
    c_GENERIC gcur;
    c_LIST list_record;
BEGIN
    OPEN c_GENERIC FOR
    SELECT my_field FROM my_table
    WHERE some_field = some_value;

    FETCH c_GENERIC BULK COLLECT INTO c_LIST;

    -- try to check against list
    SELECT * FROM some_other_table
    WHERE some_critical_field IN c_LIST;

END

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

Как вы выполняете это в Oracle?


list oracle plsql cursor
person Richard Neil Ilagan    schedule 11.08.2010    source источник
comment
Досадно, что ваш код работал бы идеально, если бы вашей последней операцией было UPDATE или DELETE — это только SELECT, которые не принимают массовое связывание в качестве входных данных.   -  person JulesLt    schedule 12.08.2010
comment
Кстати, у вас есть утечка ресурсов - не закрыли курсор ссылки. Почему не старый добрый локальный курсор?   -  person Alexander Malakhov    schedule 13.08.2010

Ответы (2)


arrow_upward
4
arrow_downward

Мы можем использовать коллекции для хранения значений в соответствии с вашими целями, но они должны быть объявлены как типы SQL:

create type list_record is table of varchar2(128)
/

Это связано с тем, что мы не можем использовать типы PL/SQL в операторах SQL. Увы, это означает, что мы не можем использовать %TYPE или %ROWTYPE, потому что это ключевые слова PL/SQL.

Тогда ваша процедура будет выглядеть так:

DECLARE
    c_LIST list_record;
BEGIN

    SELECT my_field 
    BULK COLLECT INTO c_LIST 
    FROM my_table
    WHERE some_field = some_value;

    -- try to check against list
    SELECT * FROM some_other_table
    WHERE some_critical_field IN ( select * from table (c_LIST);

END;

«Я вижу, что вам все равно пришлось выполнить оператор SELECT, чтобы заполнить список для предложения IN».

Если значения находятся в таблице, другого способа получить их в переменную нет :)

«Я думаю, что это дает значительный прирост производительности по сравнению с прямым полусоединением»

Не обязательно. Если вы используете значения только один раз, то подзапрос, безусловно, лучший подход. Но поскольку вы хотите использовать одни и те же значения в нескольких дискретных запросах, заполнение коллекции является более эффективным подходом.

В 11g Enterprise Edition у нас есть возможность использовать кэширование набора результатов. Это гораздо лучшее решение, но оно подходит не для всех таблиц.

person APC    schedule 11.08.2010
comment
Спасибо. Я понимаю, о чем вы говорите, но я вижу, что вам все же пришлось выполнить оператор SELECT, чтобы заполнить список для предложения IN. Я думаю, что это дает значительный прирост производительности по сравнению с прямым полусоединением (как предлагает Адам Муш ниже)? :) - person Richard Neil Ilagan; 12.08.2010

arrow_upward
1
arrow_downward

Зачем тянуть список вместо использования полусоединения?

SELECT * 
  FROM some_other_table 
 WHERE some_critical_field IN (SELECT my_field 
                                FROM my_table
                               WHERE some_field = some_value);
person Adam Musch    schedule 11.08.2010
comment
Потому что основной оператор SELECT может выполняться неоднократно. Я думал, что любые вложенные SELECT будут неэффективны, если количество итераций будет действительно большим, поэтому вместо этого я пытаюсь кэшировать свой список. - person Richard Neil Ilagan; 12.08.2010
comment
Не хочу показаться ехидным, но похоже, что вы думаете, что у вас есть проблема с оптимизацией производительности, а не то, что вы знаете, что она у вас есть. Я бы рекомендовал сравнить то, что я предоставил, с решением APC, чтобы увидеть, есть ли какая-либо существенная разница. - person Adam Musch; 12.08.2010
comment
На самом деле, я знаю, что у меня проблема с оптимизацией. Я застрял на рефакторинге чужого кода прямо сейчас на рассвете, потому что SP, состоящий из блоков, таких как код, который я псевдонапечатал выше, не справляется с нашими большими наборами данных. :) По правде говоря, мне действительно интересно сравнить оба ваших решения, чтобы удовлетворить свое любопытство, но я просто предпочел бы попробовать что-то новое, чтобы наконец покончить с этим. :D - person Richard Neil Ilagan; 12.08.2010
comment
Если вложенный подзапрос не ссылается ни на что в основном запросе, Oracle должен определить, что подзапрос может быть выполнен один раз, а затем повторно использовать результирующий набор. Это должно быть идентично наличию C_LIST в качестве типа коллекции переменных/объектов. Мы все знаем, что оптимизатор часто может вместо этого решить сделать самое худшее из возможного. Подсказки могут помочь или иным образом заставить другой план выполнения. - person JulesLt; 12.08.2010