Два критерия профессионализма программирования Oracle: (1) употребление ссылки на курсор в программе

Владимир Пржиялковский
Преподаватель технологий Oracle
prz@yandex.ru
www.ccas.ru/prz/

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

Аннотация

Рассматриваются ссылки на курсор, представляющие собой инструмент продвинутого программирования в 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;

Во-вторых, для удобства программирования поддерживается "системный" тип 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
- ссылки на курсор не допускают над собой выражений.