Вы находитесь на страницах старой версии сайта.
Переходите на новую версию Interface.Ru

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

 

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

© Владимир Пржиялковский
координатор Евро-Азиатской Группы Пользователей Oracle,
преподаватель УКЦ Interface Ltd.

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

Содержание

Аннотация

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

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

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

SQL> SELECT
2dname
3, CURSOR ( SELECT ename FROM emp WHERE emp.deptno = dept.deptno )
4FROM dept;
 
DNAMECURSOR(SELECTENAMEFR
------------------------------------------------
ACCOUNTINGCURSOR STATEMENT : 2
 
CURSOR STATEMENT : 2
 
ENAME
----------
CLARK
KING
MILLER
 
RESEARCHCURSOR STATEMENT : 2
 
CURSOR STATEMENT : 2
 
ENAME
----------
SMITH
JONES
SCOTT
ADAMS
FORD
 
SALESCURSOR 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, что никоим образом не снижает практической ценности этого понятия.

Дополнительная информация


За дополнительной информацией обращайтесь в компанию Interface Ltd.

Обсудить на форуме Oracle

Рекомендовать страницу

INTERFACE Ltd.
Телефон/Факс: +7 (495) 925-0049
Отправить E-Mail
http://www.interface.ru
Rambler's Top100
Ваши замечания и предложения отправляйте редактору
По техническим вопросам обращайтесь к вебмастеру
Дата публикации: 14.10.05