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

Владимир Пржиялковский

предыдущая статья серии

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

Содержание

Аннотация

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

Ссылки по теме


Страница сайта http://test.interface.ru
Оригинал находится по адресу http://test.interface.ru/home.asp?artId=1314