Церковь была отворена, за оградой стояло несколько саней;
по паперти ходили люди.
«Сюда! сюда!» — закричало несколько голосов ...
А. С. Пушкин, «Метель»

Аннотация

Рассматриваются ссылки на курсор, представляющие собой инструмент продвинутого программирования в Oracle. Приводятся примеры работы через ссылки на курсор в программах на PL/SQL и Java и в SQL*Plus.

Ссылки на курсор

В пору моего обучения в техническом ВУЗе люди, как и сечас, были склонны называть себя как угодно, и в народе бытовал критерий настоящего математика. Право им назваться отдавалось тому, кто знал, что такое поле Галуа. Таки не ставши математиком, не берусь судить о корректности этого критерия (сдается, он ребяческий), однако занимаясь Oracle, могу предложить другой критерий профессионализма: для программиста этой СУБД.

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

Ссылка на курсор дает возможность не заводить структуры курсора (CURSOR … IS …) в клиентской программе, а ограничиться в ней выделением памяти только для адреса курсора, в то время как сам курсор будет располагаться целиком в СУБД. Программист способен прожить и без ссылок на курсор, однако те могут дать программам заметные конструктивные выгоды:

• Они позволяют перенести программную логику на сервер. Клиентские приложения оказываются не так жестко привязаны к конкретным запросам; одни и те же запросы могут вызываться с одинаковым эффектом из программ на PL/SQL, C или Java.
• Они позволяют перенести вычислительную нагрузку на сервер.

Поскольку принципиально для программирования ссылки на курсоры не нужны, большинство разработчиков обходится обычным способом общения с СУБД. Выгоды же извлекают программисты, которых и можно в данном контексте признать профессионалами.

Ссылки на курсор реализованы в Oracle программно и в SQL. В программе они реализуются в виде специальной переменной, и именно этот вариант показан в этой статье. Клиентскими средами будут выступать PL/SQL, SQL*Plus и Java.

Описание ссылки на курсор и использование в PL/SQL

На каком бы языке вы не общались с БД посредством ссылки на курсор, без программирования на PL/SQL не обойтись. Формальная сторона работы со ссылкой на курсор в PL/SQL обставлена просто.

Во-первых, чтобы завести в PL/SQL переменную-ссылку на курсор, нужно сначала описать ее тип. Это делается в разделе описания с помощью предложения TYPE:

TYPE имя_типа_ссылки_на_курсор IS REF CURSOR [RETURN тип_записи];

Если конструкция RETURN присутствует, ссылка на курсор называется строгой; если нет – нестрогой. Нестрогая может ссылаться на любой курсор (запрос), а строгая – только на тот, что возвращает результат указанного типа.

Пример описания обоих типов ссылки на курсор:

DECLARE
TYPE any_curtype IS REF CURSOR;
generic_curvar any_curtype;

TYPE departments_curtype IS REF CURSOR RETURN dept%ROWTYPE;
departments_cur departments_curtype;

BEGIN NULL; END;
/

Открытие курсора с помощью переменной-ссылки на курсор:

OPEN ссылка_на_курсор FOR предложение_SELECT;

Команды FETCH и CLOSE используются как обычно, толькот вместо имени курсора указываем имя ссылки на курсор.

Во-вторых, для удобства программирования поддерживается «системный» тип SYS_REFCURSOR нестрогой ссылки на курсор. Так, в блоке выше, в разделе описания можно было бы не приводить предложение TYPE, а сразу сказать:

generic_curvar SYS_REFCURSOR;

Тип SYS_REFCURSOR сокращает текст программы, а иногда позволяет и обойтись без создания служебного пакета (пример чего в этой статье не рассматривается).

Создание пакета в PL/SQL

Приводимые ниже примеры в каждой из трех сред программирования будут использовать для доступа к БД через ссылку на курсор один и тот же пакет. В реальной жизни именно на подобный пакет и ляжет описание требуемой программной логики. Тут же он воимя наглядности устроен максимально просто, (почти) безо всякой программно-прикладной логики, но это обстоятельство и обеспечивает ему универсальность.

Выдадим в SQL*Plus:

CONNECT scott/tiger

CREATE OR REPLACE PACKAGE generic_ref_cursor AS
PROCEDURE get_ref_cursor(sqlselect IN VARCHAR2, rc OUT sys_refcursor);
END;
/

CREATE OR REPLACE PACKAGE BODY generic_ref_cursor AS
PROCEDURE get_ref_cursor(sqlselect IN VARCHAR2, rc OUT sys_refcursor) AS
BEGIN
OPEN rc FOR sqlselect;
END;
END;
/

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

(а) в теле будет обязательно присутствовать предложение OPEN

(б) среди параметров должен присутствовать выходной типа ссылки на курсор (возможно строгой).

Остальное регламентируется исключительно логикой приложения и организации программы.

Пример программирования в PL/SQL

В этом примере с помощью нашего пакета открываем курсор и передаем ссылку на курсор в программу. Извлечение результатов предложений SELECT выполняется отдельными процедурами, причем для результатов с разной структурой мы вынуждены предусмотреть разные процедуры извлечения. Это – плата за минимализм и общность пакета.

Выдадим в SQL*Plus:

SET SERVEROUTPUT ON

DECLARE 
lrc SYS_REFCURSOR;

PROCEDURE fetchandclose ( rc IN sys_refcursor) IS 
somename VARCHAR2 ( 20 ); 
    BEGIN        
    DBMS_OUTPUT.PUT_LINE ( '------------------------------' );        
    LOOP 
    FETCH rc INTO somename; 
    EXIT WHEN rc%NOTFOUND;        
    DBMS_OUTPUT.PUT_LINE ( somename ); 
    END LOOP; 
    CLOSE rc;        
    END;
PROCEDURE fetch2andclose ( rc IN sys_refcursor) IS 
somename VARCHAR2 ( 20 ); 
somenumber NUMBER; 
    BEGIN        
    DBMS_OUTPUT.PUT_LINE ( '------------------------------' );        
    LOOP 
    FETCH rc INTO somename, somenumber; 
    EXIT WHEN rc%NOTFOUND;        
    DBMS_OUTPUT.PUT_LINE ( RPAD ( somename, 10, ' ' ) || somenumber ); 
    END LOOP; 
    CLOSE rc; 
    END;
 
BEGIN
-- Примеры:generic_ref_cursor.get_ref_cursor ( 'SELECT ename FROM emp', lrc ); 
fetchandclose ( lrc );

generic_ref_cursor.get_ref_cursor ( 'SELECT dname FROM dept', lrc );
fetchandclose ( lrc );

generic_ref_cursor.get_ref_cursor ( 'SELECT job, sal FROM emp', lrc );
fetch2andclose ( lrc );
END;
/

(Чтобы не усложнять пример, результат на экране почти не оформляется).

Обратим внимание: программам, обрабатывающим по общей схеме однотипные курсоры, мы передаем не тексты запроса, а ссылки на уже открытый и динамически (а не статически, как в случае его определения в отдельном пакете) сформированный курсор.

Пример программирования в SQL*Plus

SQL*Plus позволяет заводить собственные переменные, в том числе и типа нестрогой ссылки на курсор. Открывается курсор, как и в примере выше, нашим пакетом, а вот извлечение возможно обычной командой PRINT. Эта команда умеет распознавать структуру фактического курсора, что очень удобно для работы.

Выдадим в SQL*Plus:

VARIABLE refcur REFCURSOR

BEGIN
generic_ref_cursor.get_ref_cursor        
( 'SELECT ename, sal FROM emp', :refcur );
END;
/

PRINT refcur

BEGIN
generic_ref_cursor.get_ref_cursor        
( 'SELECT * FROM emp', :refcur );
END;
/

PRINT refcur

В отличие от предыдущего примера команда PRINT закрывает курсор, так что вторичная выдача

PRINT refcur

приведет к ошибке.

Пример программирования в Java

В клиентской программе на Java обращаться к БД через ссылку на курсор можно с помощью собственных расширений, сделанных фирмой Oracle в реализации ею драйвера JDBC. В программе ниже предполагается имя СУБД MYDB. Обратите внимание, что текст с запросом SQL передается нашему пакету объектом класса CallableStatement, а извлечение в программу ссылки на курсор делается после приведения этого объекта к сугубо Oracle’овскому классу OracleCallableStatement.

Получение в программу ссылки на курсор соответствует формированию объекта класса ResultSet, обработка которого делается стандартно.

Выдача второго запроса в программе ниже демонстрирует возможность использования одного и того же пакета для получения результата разной структуры. Очевидно, по своей гибкости эта техника находится посередине между тем, что имеется в PL/SQL и в SQL*Plus.

Подготовим файл GenericRefCursor.java:

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;public class GenericRefCursor 

{public static void main ( String[] args ) 
{ 

  try 
  {       
   DriverManager.registerDriver 
    ( new oracle.jdbc.driver.OracleDriver ( ) );       
    
   Connection cn;
   cn = DriverManager.getConnection                      
        ( "jdbc:oracle:oci:@mydb", "scott", "tiger" );       
        
   CallableStatement cst;       
   OracleCallableStatement ocst;       
   ResultSet rs;       
        
   cst = cn.prepareCall                            
     ( "BEGIN generic_ref_cursor.get_ref_cursor ( ?, ? ); END;" );       
   cst.setString ( 1, "SELECT sal FROM emp" );       
   cst.registerOutParameter ( 2, OracleTypes.CURSOR );       
       
   cst.execute ( );       
       
   ocst = ( OracleCallableStatement ) cst;       
       
   rs = ocst.getCursor ( 2 );       
        
   while ( rs.next       
   ( ) ) { System.out.println ( rs.getInt ( 1 ) ); }       
       
   /* Новый запрос ... */       
   cst.setString ( 1, "SELECT dname, loc FROM dept" );       
   cst.execute       ( );       
   rs = ocst.getCursor ( 2 );       
   while ( rs.next ( ) ) 
   { System.out.println ( rs.getString ( 1 ) + rs.getString ( 2 ) ); }       
       
  /* ... и так далее, запрос за запросом */       
  cst.close ( ); 
 } 
 catch ( Exception e ){System.out.println ( e );  }
}
   
}

В ОС оттранслируем класс GenericRefCursor и выполним программу:

>javac GenericRefCursor.java
>java GenericRefCursor

Ограничения использования ссылки на курсор

Мысли, возникающие по поводу возможного использования ссылок на курсор в программе, несколько осаждаются существующими ограничениями, часть которых, если вдуматься, имеют свою логику. Как упоминалось, ссылки на курсор не представлены типом SQL (до некоторой степени это естественно), и не могут храниться в качестве переменных пакета PL/SQL. Более полно:

• ссылки на курсор не могут объявляться как переменные пакета PL/SQL и их нельзя передавать через переменные пакета
• ссылкам на курсор нельзя присваивать значение NULL (в версии 10 уже можно) и их нельзя сравнивать друг с другом (но их можно присваивать друг другу)
• ссылки на курсор нельзя хранить в столбцах таблиц и в элементах коллекции
• ссылки на курсор нельзя передавать от сервера к серверу с помощью RPC
• ссылки на курсор нельзя использовать с пакетом DBMS_SQL
• ссылки на курсор не допускают над собой выражений.