Как проверить, есть ли значение переменной в списке полученном из динамического запроса?

Question

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

Пробую создать табличный тип данных и функцию так:

CREATE OR REPLACE TYPE TP_BI_LIST AS TABLE OF VARCHAR2(4000)
/
CREATE OR REPLACE FUNCTION CheckDataMissing (pOwn VARCHAR2, pTab VARCHAR2, pCol VARCHAR2) RETURN NUMBER IS
    v_str VARCHAR2(2000); 
    v_Results TP_BI_LIST := TP_BI_LIST();
BEGIN
    v_str := 'SELECT ''TEST1'' AS NM_COLUMN FROM DUAL UNION ALL
              SELECT ''TEST2'' AS NM_COLUMN FROM DUAL UNION ALL
              SELECT ''TEST3'' AS NM_COLUMN FROM DUAL';
    EXECUTE IMMEDIATE v_str into v_Results;
   
    DBMS_OUTPUT.PUT_LINE('Total rows: '||v_Results.COUNT);       

    IF pCol IN (v_Results) THEN 
        dbms_output.put_line('YES'); 
    ELSE dbms_output.put_line('NO'); 
    END IF;       
  
EXCEPTION WHEN OTHERS THEN
    RETURN 0;
END CheckDataMissing;
/

Но это не работает, получаю ошибку:

12/8 PLS-00383: type mismatch found at 'PCOL' inside an IN or NOT IN clause

Какой есть путь решения?

Также пробовал решения в этих темах: PL/SQL - Use “List” Variable in Where In Clause, How to pass varchar with single quotes to Stored Proc in Oracle [duplicate] и How to add values to a VARRAY using a loop, но безуспешно.

_{Свободный перевод вопроса PL/SQL Check if a variable is in a list from Execute Immediate от участника Guilherme Matheus}

Answer 1

Чтобы получить коллекцию из запроса надо, либо воспользоваться BULK COLLECT клаузой, либо создать коллекцию функцией COLLECT. Чтобы узнать, есть ли элемент с заданным значением в коллекции, надо воспользоваться условным оператором MEMBER [OF].

Воспроизводимый пример, как надо сделать:

create or replace type listofcols as table of varchar2 (4000)
/
create or replace function isFound (col varchar2) return char is
    guery varchar2 (2000); 
    Results listofcols;
    ret char (1);
begin
    guery := q'[
        select cast (collect (trim (col)) as listofcols) from ( 
            select 'TEST1' col from dual union all
            select 'TEST2'     from dual union all
            select 'TEST3'     from dual)]';
    execute immediate guery into Results;

    if Col member of Results then ret := 'y';
    else ret := 'n'; 
    end if;  
    dbms_output.put_line ('isFound('||col||')='||ret||': total '||Results.count||' rows'); 
    return ret;
end isFound;
/

Результат:

var res char (1)  
exec :res := isFound (upper ('test2'));

isFound(TEST2)=y: total 3 rows

RES
---
y

БЛОГ НА HUSL

Как проверить, есть ли значение переменной в списке полученном из динамического запроса?

Ответы (1 шт):