Есть ли поддержка не скалярного типа данных для параметра явного курсора?

Question

Есть процедура, в которой условие в WHERE клаузе не известно вплоть до момента выполнения.

DECLARE
    CURSOR my_cursor is 
    SELECT ...
    FROM ...
    WHERE terms in (
        -- элементы, которые должны быть включёны в условие выборки
        (SELECT future_term2 FROM term_table),  
        (SELECT future_term1 FROM term_table),
        (SELECT present_term FROM term_table)
    );
BEGIN
    (the processing)
END;
/

Что SELECT ... FROM term_table возвращает - 4-х значную строку.

Как решение думаю использовать курсор с параметром:

    DECLARE
        target_terms SOME_DATATYPE;

        CURSOR my_cursor (pi_terms IN SOME_DATATYPE) IS 
            SELECT ...
            FROM ...
            WHERE terms in my_cursor.pi_terms;
    BEGIN
        target_terms := CASE term_digit
        WHEN '2' THEN (
            (SELECT future_term2 FROM term_table),
            (SELECT future_term1 FROM term_table),
            (SELECT present_term FROM term_table)
        ) ELSE (
            (SELECT future_term1 FROM term_table),
            (SELECT present_term FROM term_table)
        )
        END;

        FOR my_record IN my_cursor (target_terms) LOOP
            (the processing)
        END LOOP;
    END;
    /

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

Не понятно, поддерживаются ли не скалярный параметр курсора? Если поддерживается, то корректен ли способ, показанный выше, чтобы сформировать значение для target_terms? Если нет, то как тогда?

_{Свободный перевод вопроса Does Oracle support non-scalar cursor parameter? от участника @newman}

Answer 1

Да, поддерживаются. Можно использовать коллекции не только скалярных типов данных, но и композитных. В документации о каком либо лимитировании на тип данных, кроме недопустимости ограничений самих типов данных, не упомянуто:

Restriction on datatype

This datatype cannot have constraints (for example, NOT NULL, or precision and scale for a number, or length for a string).

Пример того, как это будет выглядеть:

create or replace type rect force as object (no number, code varchar2 (4))
/
create or replace type arrt is table of rect 
/
create table t1 as 
    select rownum no, trim (column_value) code
    from xmlTable ('"aaa","bbb","ccc"')
/    
var rc refcursor
declare 
    terms arrt := arrt (rect (1, 'aaa'), rect (3, 'ccc'));
    codes arrt := arrt ();
    cursor cur (terms arrt) is 
        select * from t1
        where (no, code) in (select no, code from table (terms) t); 
begin
    for r in cur (terms) loop 
        codes.extend;
        codes (codes.last) := rect (r.no, r.code);
    end loop; 
    open :rc for 
        select * from table (codes);
end;
/

        NO CODE
---------- ----
         1 aaa 
         3 ccc

Answer 2

Конечно, к курсору можно передать параметры, как и к любой функции - но только IN параметры. Однако PL/SQL, это сильно типизированный язык, поэтому тип данных должен быть указан при компиляции.

На мой взгляд, похоже на то, что нужно построить запрос динамически, а затем использовать:

OPEN cursor FOR querytext;

Следующий пример должен дать представление о том, как это можно сделать:

create or replace package return_id_sal is
    type employee_rt is record (
        employee_id hr.employees.employee_id%TYPE,
        salary hr.employees.salary%TYPE);

    function allrows_by (append_to_from_in varchar2 default null) return sys_refcursor;
end return_id_sal;
/
create or replace package body return_id_sal is
    function allrows_by (append_to_from_in varchar2 default null) return sys_refcursor is
        l_return sys_refcursor;
    begin
        open l_return for
            'SELECT employee_id, salary FROM hr.employees '||append_to_from_in;
        return l_return;
    end allrows_by;
end return_id_sal;
/
declare
    l_cursor sys_refcursor;
    l_row return_id_sal.employee_rt;
begin
    l_cursor := return_id_sal.allrows_by ('WHERE department_id = 10');
    loop fetch l_cursor into l_row;
        exit when l_cursor%NOTFOUND;
    end loop;
end;
/

В этом случае, следует принимать меры предосторожности против SQL инъекций. Пользователь никогда не должен иметь возможности передавать SQL текст непосредственно в функцию.

_{Свободный перевод ответа от участника @Steven Feuerstein}

БЛОГ НА HUSL

Есть ли поддержка не скалярного типа данных для параметра явного курсора?

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