Профессионалу разработчику

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

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

... Родила царица в ночь
Не то сына, не то дочь;
Не мышонка, не лягушку,
А неведому зверюшку.
А. С. Пушкин, “Сказка о царе Салтане”

Аннотация

Рассматриваются возможности использования ссылок на курсор в предложения SQL в Oracle.

Ссылки на курсор в предложении SQL

Ссылки на курсор могут использоваться не только в программе, но и в предложении SQL. Это обеспечивается указанием во фразе SELECT предложения SELECT выражения CURSOR для формирования столбца результата. Если мы попытаемся сделать это в SQL*Plus, результат окажется довольно неожиданный:

SQL> SELECT
  2    dname
  3  , CURSOR ( SELECT ename FROM emp WHERE emp.deptno = dept.deptno )
  4  FROM dept;

DNAME          CURSOR(SELECTENAMEFR
-------------- --------------------
ACCOUNTING     CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME
----------
CLARK
KING
MILLER

RESEARCH       CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME
----------
SMITH
JONES
SCOTT
ADAMS
FORD

SALES          CURSOR STATEMENT : 2

... ... ... ...

На деле произошло вот что: предложение SELECT … FROM dept вернуло во втором столбце результата каждой своей строки ссылку на курсор, сформированный динамически предложением внутри CURSOR ( … ). Программа SQL*Plus проинтерпретировала эту ссылку, перебрав строки, словно как мы это делали в примерах предыдущей статьи, и выдала строки на экран. В результате мы увидели на экране перечень четырех строк-отделов, и для каждого отдела – перечень сотрудников.

Форма выдачи занятная, но, кажется, практически бесполезная. Так зачем же нужно выражение CURSOR в предложении SELECT ? Чтобы ответить на этот вопрос, снова вернемся к программированию.

Программная обработка

Рассмотрим следующий пример блока PL/SQL, где используется (а) обычный курсор для SELECT и (б) курсорное выражение внутри:

SET SERVEROUTPUT ON

DECLARE 
CURSOR main_cur IS 
SELECT
  dname
, CURSOR ( SELECT ename FROM emp WHERE emp.deptno = dept.deptno )
FROM dept
;
deptname dept.dname%TYPE;
empname emp.ename%TYPE;
inner_cur SYS_REFCURSOR;

BEGIN
OPEN main_cur;
LOOP 
   FETCH main_cur INTO deptname, inner_cur;
   EXIT WHEN main_cur%NOTFOUND;
   dbms_output.put_line ( '-----------------' || deptname );
   LOOP 
      FETCH inner_cur INTO empname;
      EXIT WHEN inner_cur%NOTFOUND;
      dbms_output.put_line( empname );
   END LOOP;
   CLOSE inner_cur;
END LOOP;
CLOSE main_cur;
END;
/

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

Более того, этот один программный курсор с выражениями CURSOR просто и точно указывает на логику обработки предложения SELECT (то есть играет документирующую роль), в то время как определение в программе нескольких курсоров без курсорных выражений ничего не говорит о логике их совместной обработки, так что понять последнюю мы сможем только по процедурным конструкциям, а давно известно, что понять, что делает программа, по процедурным конструкциям гораздо труднее, чем по описаням структур.

Более того, при использовании курсорного выражения мы избавлены от необходимости его открывать, сразу приступая к операциям FETCH, что быстрее в исполнении и экономнее в тексте.

Резюме: использование ссылки на курсор в предложении SELECT упрощает код программной обработки, улучшает читаемость кода и повышает эффективность его исполнения.

Применение в XSQL

В некоторых случаях использование курсорного выражения в SELECT дает выгоду и вне программы.

Средства XSQL, поставляемые в рамках штатной поставки ПО СУБД Oracle, позволяют хранить на сервере web сервлет, порождающий по запросу SELECT к базе данных документ XML. Этот сервлет способен обрабатывать ссылки на курсор, порождаемые курсорным выражением.

Подготовим файл departments.xsql:

<?xml version="1.0"?>
<xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
SELECT
  dname
, CURSOR ( SELECT ename FROM emp WHERE emp.deptno = dept.deptno )
  AS employees
FROM dept
</xsql:query>

Создадим каталог company в месте файловой системы, предназначенном для расположения файлов формата XSQL. Например, при установленном расширении XDK ПО Oracle версии 9.2 этим местом может быть $ORACLE_HOME/xdk/demo/java. Обратимся браузером по адресу http://localhost:7778/xsql/company/departments.xsql (сервер HTTP при этом должен быть запущен). Получим примерно такой результат:

В этом изображении для краткости узлы ROW с атрибутами num = "2" и num = "3" свернуты средствами браузера.

Полученный документ XML может играть для сервера приложений в web как самостоятельную роль (выдавая результат обращений по определенному адресу в web в формате XML), так и роль исходного материала для дальнейшего преобразования XSL (выдавая результат в конечном итоге в формате HTML, в том числе с графическим оформлением). Эти преобразования здесь не рассматриваются.

Замечательно, что обрабатывающий запросы XSQL сервлет, разработанный фирмой Oracle, способен обрабатывать не только одиночные, но и вложенные выражения типа CURSOR. Подготовим еще один файл multicursor.xsql и положим его в тот же каталог company:

<?xml version="1.0"?>
<xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
SELECT
  TO_CHAR ( SYSTIMESTAMP,'SS:FF3' )
  AS seconds
, CURSOR ( SELECT TO_CHAR ( SYSTIMESTAMP,'SS:FF3' ) secondsone 
                , CURSOR ( SELECT TO_CHAR( SYSTIMESTAMP,'SS:FF3' )
                           AS secondstwo
                           FROM DUAL )
                  AS leveltwo
           FROM DUAL ) 
  AS levelone
FROM dept
</xsql:query>

Таблица DEPT здесь взята только ради примера из нескольких строк.

Обратимся браузером по адресу http://localhost:7778/xsql/company/multicursor.xsql и увидим примерно такой результат:

Как и в первом случае, некоторые узлы документа свернуты средствами браузера ради экономии места в статье, занимаемого картинкой.

Обращение к SYSTIMESTAMP позволяет наблюдать процесс вычисления ответа сервлетом Oracle.