Новые возможности в динамическом SQL в Oracle 12c (возврат "неявного" курсора)

В некоторых СУБД есть возможность вернуть из хранимой функции непосредственно набор данных. Например, в СУБД MS SQL Server возможен такой код хранимой процедуры на T-SQL:

CREATE PROCEDURE my_proc1
AS
BEGIN
 SELECT * FROM accounts_amounts WHERE Amount >= 20000
END

Далее, клиентские библиотеки, на стороне приложения, могут такой "неявный" курсор прочитать.

Как вы хорошо знаете, такой возможности в СУБД Oracle Database нет: хранимая процедура не может вернуть просто запрос. Необходим возврат ссылки на курсор (ref cursor): через OUT-переменную, либо как результат возвращаемый хранимой функцией.

Например:

CREATE OR REPLACE PACKAGE my_types IS
  TYPE
   Taccounts_amounts_cur is ref cursor;
END;
/

CREATE PROCEDURE my_proc1(v_xRes in out my_types.Taccounts_amounts_cur) IS
BEGIN
 OPEN v_xRes FOR 
   SELECT * FROM accounts_amounts WHERE Amount >= 20000;
END;
/

В общем-то, отсутствие в СУБД Oracle Database возможности вернуть из хранимой процедуры просто курсор, никак не мешала разработчикам приложений.
Тем не менее, это значительно усложняло процедуру миграции приложений на СУБД Oracle Database, с тех СУБД, где такая возможность присутствовала.

В СУБД Oracle Database 12c такая возможность появилась!
Это, конечно, потребовало адаптации клиентских библиотек доступа в Oracle Client. Начиная с Oracle Database Client 12c приложения, которые используют любой интерфейс доступа к СУБД (Oracle Call Interface, ODP.Net, JDBC), могут получать из СУБД такие "неявные" курсоры. Собственно на стороне СУБД, чтобы вернуть такой курсор, необходимо использовать новую процедуру RETURN_RESULT из пакета DBMS_SQL.

Эта процедура перегружена, и присутствует в двух вариантах:

• для возврата слаботипизированной ссылки на курсор -
  PROCEDURE RETURN_RESULT(rc IN OUT SYS_REFCURSOR, to_client IN BOOLEAN DEFAULT TRUE);
• для возврата курсора отрытого с помощью динамического SQL в пакете DBMS_SQL -
  PROCEDURE RETURN_RESULT(rc IN OUT INTEGER, to_client IN BOOLEAN DEFAULT TRUE);

Параметр to_client определяет возможность возвращения курсора непосредственно сразу клиентcкому приложению, либо в другую хранимую процедуру PL/SQL. Хранимая процедура может вернуть таким образом не один курсор, а несколько - может вызвать процедуру RETURN_RESULT несколько раз. Определим хранимую процедуру, которая возвращает курсор подобным образом в клиентское приложение:

SQL> create or replace procedure test_dyn3 is
  v_xCursor    integer;
  v_xRowCount  integer;
begin
  v_xCursor := dbms_sql.open_cursor;

  dbms_sql.parse(c                  => v_xCursor,
                 statement          => 'select * from dual',
                 language_flag      => DBMS_SQL.NATIVE);

  v_xRowCount := dbms_sql.execute(c => v_xCursor);

  dbms_sql.return_result(v_xCursor,true);
end;
/

Procedure created.

Попробуем вызвать нашу процедуру в среде утилиты SQL*Plus:

SQL> exec test_dyn3;

PL/SQL procedure successfully completed.

ResultSet #1

D
-
X

SQL>

Как Вы можете убедиться: утилита SQL*Plus версии 12с "понимает" такой вид курсора, и просто выводит его содержимое на свою консоль.
Эта возможность появилась в версии 12с этой утилиты. Если курсор возвращается таким образом из динамического SQL, то чтобы его получить внутри вызываемого блока PL/SQL, предназначена еще одна новая процедура пакета DBMS_SQL - GET_NEXT_RESULT. Эта процедура также перегружена и имеет два варианта:

• для получения слаботипизированной ссылки на курсор -
  PROCEDURE GET_NEXT_RESULT (c IN INTEGER, rc OUT SYS_REFCURSOR);
• для получения курсора отрытого с помощью динамического SQL в пакете DBMS_SQL -
  PROCEDURE GET_NEXT_RESULT (c IN INTEGER, rc OUT INTEGER);;

Для того чтобы, иметь возможность получения таких "неявных" курсоров, вызов процедуры OPEN_CURSOR должен быть выполнен с новым параметром treat_as_client_for_results равным значению TRUE, по умолчанию значение этого параметра равно FALSE. Немного перепишем нашу процедуру test_dyn3 указав в функции возврата курсора значение параметра to_client равным FALSE:

SQL> create or replace procedure test_dyn3 is
  v_xCursor    integer;
  v_xRowCount  integer;
begin
  v_xCursor := dbms_sql.open_cursor;

  dbms_sql.parse(c                  => v_xCursor,
                 statement          => 'select * from dual',
                 language_flag      => DBMS_SQL.NATIVE);

  v_xRowCount := dbms_sql.execute(c => v_xCursor);

  dbms_sql.return_result(v_xCursor,false);
end;
/

Procedure created.

Теперь вызовем нашу процедуру через динамический SQL, и попробуем получить и вывести на экран содержимое курсора:

SQL> DECLARE
  v_xCursor          integer;
  v_xRowCount        integer;

  v_xReturnCursor    integer;
  v_xResult          dual.dummy%type;
  v_xReturnRefCursor sys_refcursor;
BEGIN
  v_xCursor := dbms_sql.open_cursor(treat_as_client_for_results => true);

  dbms_sql.parse(v_xCursor, 'BEGIN test_dyn3(); END;', dbms_sql.native);

  v_xRowCount := DBMS_SQL.EXECUTE(v_xCursor);
 
  dbms_sql.get_next_result(v_xCursor, v_xReturnCursor);

--Для удобства превращаем полученный курсор в ссылку на курсор (sys_refcursor)
  v_xReturnRefCursor := dbms_sql.to_refcursor(v_xReturnCursor);

  FETCH v_xReturnRefCursor 
    INTO v_xResult;
 
  dbms_output.put_line('Result: ' || v_xResult);
 
  dbms_sql.close_cursor(v_xCursor);
END;
/
Result: X

PL/SQL procedure successfully completed.

SQL>

Мы рассмотрели еще одну новую возможность Oracle Database 12c в области динамического SQL - возможность непосредственного возврата курсоров из хранимых процедур.
На мой взгляд, эта новая возможность в основном востребована при миграции приложений с других СУБД, которые уже поддерживают эту технологию, на СУБД Oracle Database. Поскольку в этом случае значительно снижается объем работ по адаптации кода приложения под СУБД Oracle.