В чем разница между явным курсором и переменной курсора с типом REF CURSOR?

Question

Допустим, определены такие два курсора, явный ec и курсор переменная rc:

declare
    cursor ec is select 'name1' name from dual;
    type refcursor is ref cursor;
    rc refcursor;
begin 
    open ec;
    rc := ec;
end;
/
ORA-06550: line 7, column 11:
PLS-00382: expression is of wrong type

Компилятор считает, что присваивание одного к другому это неверное выражение.

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

Так какая всё таки между ними разница? И как с ними работать?

Answer 1

Как сказано в документации в гл. 6.4 Cursor Variables:

Note: Cursor variables and explicit cursors are not interchangeable — you cannot use one where the other is expected.

Эти типы несовместимы. Нельзя переменную явного курсора (explicit cursor), объявленную с ключевым словом CURSOR, присвоить курсор переменной с типом REF CURSOR (cursor variable).

Явный курсор это особый тип переменной, который создавался только для PL/SQL и может быть использован только в PL/SQL контексте.

Oба типа курсора являются указателями (pointer). Но область памяти, куда указывает явный курсор, цитирую из документации:

A cursor is a pointer to a private SQL area that stores information about processing a specific SELECT or DML statement.

То есть, там, где хранятся все PL/SQL переменные сессии. Поэтому у этого типа переменной есть ещё одно имя - курсор сессии. А область памяти, куда указывает переменная курсора:

Before you can reference a cursor variable, you must make it point to a SQL work area

Другими словами, причина несовместимости этих переменных - их различная "природа".

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

переменная явного курсора указывает только на один определённый SQL запрос, его нельзя изменить
переменная курсора может принять указатель на любой SQL запрос, ей можно присвоить указатель на новый запрос

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

declare
    cursor ec is select 'name1' name from dual;
    cursor oc return ec%rowtype;
    cursor oc is select 'name1' name from dual;
begin 
    open ec;
    oc := ec;
end;
/
ORA-06550: line 7, column 5:
PLS-00321: expression 'OC' is inappropriate as the left hand side of an assignment statement

Поэтому, единственный способ преобразовать явный курсор в переменную курсора, это - открыть новый курсор, где в запросе в качестве таблицы использовать результат выполнения явного курсора. Один конкретный и очень гибкий пример есть в этом ответе. Ещё один самый минимальный воспроизводимый пример:

create or replace package pack as
    cursor cur is
        select 1 id, 'name1' name from dual union all
        select 2 id, 'name2' name from dual;
    type currows is table of cur%rowtype;
    type refcursor is ref cursor;
    procedure proc (rc out refcursor); 
end;
/
create or replace package body pack as
    procedure proc (rc out refcursor) is 
        rs currows;
    begin 
        open cur;
        fetch cur bulk collect into rs;
        close cur;
        open rc for select * from table (rs); 
    end;
end;
/
var rc refcursor 
exec pack.proc (:rc)

    ATTR_1 ATTR_
---------- -----
         1 name1
         2 name2

БЛОГ НА HUSL

В чем разница между явным курсором и переменной курсора с типом REF CURSOR?

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