Аннотация

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

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

Ссылки на курсор могут использоваться не только в программе, но и в предложении 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 («XML Development Kit», XDK), позволяют хранить на сервере 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 в месте файловой системы, предусмотренным в Oracle для расположения файлов формата 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, в том числе с графическим оформлением). Эти преобразования здесь не рассматриваются, как не относящиеся к возможностям собственно Oracle.

Замечательно, что обрабатывающий запросы 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.

Нетрудно сообразить, что в случае «запроса» на XSQL, равно как и в случае запроса с курсорным выражением в SQL*Plus, мы имеем дело с иллюзией отсутствия программной обработки, которая на деле присутствует в тексте сервлета и в SQL*Plus, во встроенном виде. Но ведь также мы имеем дело с иллюзией таблицы, работая с view, что никоим образом не снижает практической ценности этого понятия.