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

Рассматриваются ссылки на курсор, представляющие собой инструмент продвинутого программирования в 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 переменную-ссылку на курсор, нужно сначала описать ее тип. Это делается в разделе описания с помощью предложения 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 сокращает текст программы, а иногда позволяет и обойтись без создания служебного пакета (пример чего в этой статье не рассматривается).

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

Выдадим в 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;
/

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

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

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

В этом примере с помощью нашего пакета открываем курсор и передаем ссылку на курсор в программу. Извлечение результатов предложений 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;
/

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

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