Oracle SYS_REFCURSOR

SYS_REFCURSOR в Oracle — это предопределенный тип данных, используемый для создания слаботипизированных (weak) курсорных переменных. Он позволяет возвращать динамические наборы результатов из хранимых процедур, функций или пакетов, не привязываясь к конкретной структуре запроса на этапе компиляции. Это ключевой инструмент для работы с результирующими наборами в PL/SQL и внешних приложениях.

Основные особенности:

1. Динамический курсор: Не требует объявления структуры результата заранее. Может быть открыт для любого SQL-запроса во время выполнения.

2. Использование в PL/SQL: Возврат данных клиентским приложениям (например, через JDBC, ODBC, ODP NET). Передача результатов между процедурами/функциями.

3. Сравнение с явными курсорами: Явные курсоры жестко привязаны к определенному запросу. SYS_REFCURSOR гибок: один курсор можно переиспользовать для разных запросов.

Пример использования:

-- Создание процедуры, возвращающей SYS_REFCURSOR
CREATE OR REPLACE PROCEDURE get_employees (p_dept_id NUMBER, p_cursor OUT SYS_REFCURSOR) 
IS
BEGIN
  OPEN p_cursor FOR 
    SELECT employee_id, first_name, last_name 
    FROM employees 
    WHERE department_id = p_dept_id;
END;

-- Вызов из PL/SQL
DECLARE
  cur SYS_REFCURSOR;
  v_emp_id employees.employee_id%TYPE;
  v_first_name employees.first_name%TYPE;
  v_last_name employees.last_name%TYPE;
BEGIN
  get_employees(10, cur); -- Открыть курсор для department_id = 10

  LOOP
    FETCH cur INTO v_emp_id, v_first_name, v_last_name;
    EXIT WHEN cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp_id || ': ' || v_first_name || ' ' || v_last_name);
  END LOOP;

  CLOSE cur;
END;

Отличия от сильных (strong) REF CURSOR:

• Сильный REF CURSOR:

  TYPE dept_cursor IS REF CURSOR RETURN departments%ROWTYPE; -- Привязан к структуре таблицы

  • Проверка типов на этапе компиляции.

  • Ограничивает курсор определенной структурой.

• SYS_REFCURSOR (слабый):

  • Нет привязки к конкретной структуре.

  • Риск ошибок выполнения при несовпадении структуры данных.

Применение в клиентских приложениях:

Java (JDBC):

  CallableStatement stmt = conn.prepareCall("{ call get_employees(?, ?) }");
  stmt.setInt(1, 10);
  stmt.registerOutParameter(2, OracleTypes.CURSOR);
  stmt.execute();
  ResultSet rs = (ResultSet) stmt.getObject(2);
  while (rs.next()) {
    System.out.println(rs.getInt("employee_id"));
  }

Преимущества и недостатки:

Плюсы: Гибкость: возврат любых запросов. Эффективность для больших данных (не требует материализации в памяти).

Минусы: Отсутствие проверки типов на этапе компиляции. Клиент должен знать структуру результата.

Когда использовать:

Возврат результатов из хранимых процедур в приложения. Динамическое формирование запросов (например, с изменяемыми условиями WHERE). Интеграция с Reporting tools или ORM, которые работают с курсорами.

Если требуется строгая типизация, используйте сильные REF CURSOR. Для максимальной гибкости — SYS_REFCURSOR.